MSSQL Server Reporting Services: Reporting Services Basics: Create a Reusable Template Report - Page 2

Create a Template Report in Reporting Services

Objective and Business Scenario

In the following sections, we will perform the steps required to create a template report to be reused by a hypothetical group of report authors. In effect, we will be treating the author audience just as we have treated information consumers for whom we have developed solutions in past articles: as a group of Reporting Services users from a development perspective, with a common business need that we will help to fill as Reporting Services practitioners. Our aim is to enhance the overall development life cycle, which not only enhances the development experience of the authors directly, but ultimately ensures a better information product for any information consumer that uses the report.

For purposes of our practice procedure, we will assume that we have been called to "kick start" a reporting project for a client (the AdventureWorks2000 organization), with whom we have previously consulted in a conversion from Business Objects to Reporting Services. We implemented Business Objects at the client a few years back, returning recently to convert corporate business intelligence systems to Reporting Services. We therefore know several members of the existing group of report authors, who are familiar with the concept of templates. New members of the team, which the organization was able to add, based upon the seven figures it saved in application licensing fees by converting to Reporting Services, have varied exposure to enterprise reporting, and are new to Reporting Services.

Having trained the authors in general classes we tailored to the client environment as part of our conversion engagement, we have offered to provide some "reality-based" training that goes beyond canned classes, and with which we consistently experience success at most of our reporting implementations. We will conduct hands-on reporting workshops at several of the client's locations, where we actually write reports in Reporting Services to replace select high profile Business Objects reports, matching the look and feel - not to mention the functionality - of the original reports with the new application.

This approach offers many tangible benefits (I use it in real life within most engagements, in some form or other). The opportunity to create templates of highly visible, frequently used reports numbers among the most popular of these value-adds. By creating representative samples of popular reports in a collaborative environment, we can accomplish numerous objectives in a richly rewarding session that leaves the authors, and the organization, with working models - and in this case templates - upon which they can rely to not only reinforce the training that they have received to date, but to allow them to generate and maintain working reports from the outset. This is highly valuable as a training event, and returns many times the value of classroom training that is based upon simplistic "training" databases that are, to be kind, not likely to mirror the data of the organization whose representatives it purports to teach.

The template we create in our practice exercise together represents a simple example from the report set we have described. The objective for creating the template is, again, to increase the productivity of the report authors, among other, far-reaching benefits upon which we have touched. The client has requested, in this instance, a template that can be used as a basis for the creation of subsequent reports. The template will include the AdventureWorks2000 logo, along with a company title within a header that will automatically appear atop each page of the report. In addition, a footer will appear on each page. Within the footer, we will include:

• Identification of the system user printing the report;
• The report file title;
• Page number;
• Total number of pages in the report.

As part of our typical business requirements gathering process, we listen attentively to the requirements, and consider the objects required, and the techniques from which we can choose to employ them. Once we grasp the stated need, and confirm our understanding with the intended audience, we begin the process of creating the template to enhance the report generation process for the authors with whom we will be interacting in our workshop.

Considerations and Comments

The template report that we will create involves the sample MSSQL Server 2000 database, AdventureWorks2000, which accompanies the installation of Reporting Services. At the time of writing, the Service Pack 1 update is assumed for Reporting Services and the related Books Online and Samples.

For purposes of this exercise, we will create a Reporting Services project within the Visual Studio.Net 2003 Report Designer environment, within which we will work primarily with a blank report. Creating a blank report is quite straightforward, making the assumptions that are standard within this series: that you have the authority, access and privileges, within both MSSQL Server and Reporting Services, needed to accomplish the steps involved.