About the Series ...

This is the tenth article of the series MSSQL Server 2000 Reporting Services. The series is designed to introduce MSSQL Server 2000 Reporting Services ("Reporting Services"), with the objective of presenting an overview of its features, together with many tips and techniques for real-world use. This column also serves as a vehicle for sharing my conviction in Reporting Services' role as a new paradigm in enterprise reporting. As I advise clients on a more and more frequent basis these days, this is the future in a big way. I hope you will consider my input valuable, and that you will investigate closely the savings and advanced functionality that will soon be available to anyone with an MSSQL Server 2000 (and beyond) license.

Important: For information concerning the applications to which you will require access to benefit the most from our series, please see our initial Database Journal article, A New Paradigm for Enterprise Reporting.

It is assumed that you have access and the other rights / privileges required to complete the articles within the series. For details on the specifics of the adjustments necessary to quickly allow full freedom to complete the exercises in this and subsequent articles, as well as important assumptions regarding rights and privileges in general, please see earlier articles in our series, as well as the Reporting Services Books Online.

Overview

In this article, we will venture away from the functionally specific focuses of recent sessions, and concentrate on a basic consideration that can save us a great deal of time in our work within reporting services. As most of us who have worked with enterprise reporting packages have come to realize, report templates can offer us many advantages in creating reports to meet the needs of information consumers.

When I say "template," I mean a "pattern," or "boilerplate," which we can create and store, and which we can later call upon to accomplish many of the repetitive steps we might encounter in building a report. A template can serve as a "starter report," saving us myriad similar steps in authoring. This increases report author productivity because much or all of the design work is already done when they begin creating a new report. A template, as we shall see, is simply an existing report file whose attributes can be applied to a new report. We can use templates to give any number of reports a consistent look, while sparing ourselves the need to format each one individually. Finally, in addition to more productivity and support for standardized reporting, templates often offer an added bonus to our report authoring team: they can enable many users to create sophisticated reports that might otherwise be beyond their existing skill levels.

While templates can be evolved to any level of completion before saving them, a template best embodies, once again, a pattern upon which we base the creation of new reports. Because the templates attributes become the basis for the report we are creating, we should try to find that ideal point in the creation of the template where it contains many or all of the characteristics that our reports will hold in common, while leaving out those features that we will have to remove from individual reports after we jumpstart their creation. Templates are, in effect, reports without any actual data, or with minimal "dummy data," perhaps, that can be used as placeholders with which we can easily substitute the real data that needs to appear in the new report. Templates are obviously the most useful when report authors frequently need the same type of report, and we can create these "models" to match the various "standard" reports that arise within our organizations as soon as we recognize the recurring nature of these reports.

Templates in Reporting Services can contain many kinds of report objects, examples of which include:

• formatting
• text
• pictures
• Report items such as:
  • Textboxes
  • images
  • lines
  • rectangles
• placeholders of various sorts
• calculations
• summaries
• "global" report variables, such as the date or time that the report was created

Regardless of the variety of report types with which we work in the business environment as report authors, it is common to encounter scenarios where our reports represent hours of development investment to produce formatting and other features that we wish, going forward, to apply to subsequent reports without reinventing the original. Template reports in Reporting Services are simply existing .rdl files that we can use, as we have said, to "kick start" new reports - and often shortcut creation cycles dramatically. It is certainly not hard to imagine how creating these surrogate "boilerplates" can help us to grind out large volumes of reports quickly, while retaining the appearance and characteristics to which the organization has become accustomed, or wishes to present as a corporate standard to the targeted audiences.

The process is straightforward, and perhaps intuitive to many of us (depending upon previous reporting applications with which we have had experience), but the step-by-step procedure is not detailed in a straightforward manner in the online help that accompanies Reporting Services. In this article, we will explore the creation of a basic template which we will then enable for use at any point going forward in the report authoring process. In this session, we will:

• Create a basic report in Report Designer to use as the basis for a template report;
• Create header and footer sections in the template report;
• Add basic formatting, system variables and images, and other rudimentary objects to the template;
• Preview the report to ascertain that it meets the needs of its intended audience;
• Install the template appropriately to ensure its easy selection as an option in the creation of any report in Report Designer;
• Verify the proper operation of the template in the initial steps of creating a new report.

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.

Hands-On Procedure

Preparation

Create a Reporting Services Project

To begin, we will launch Reporting Services' Report Designer, found in Microsoft Visual Studio .NET 2003.

The equivalent on my PC appears as shown in Illustration 1.

The New Project dialog appears. Business Intelligence Projects appears in the Project Types tree, indicating an installation of Reporting Services.

RS010

The New Project dialog appears, with our additions, as shown in Illustration 3.

Our new project appears in the Solution Explorer (upper right corner of the Visual Studio .NET interface), as we see in Illustration 4.

Having created a Report Project, we are ready to proceed with creating the new template report.

Create a Template Report

In this section, we will launch Reporting Services' Report Designer, and then create a new report, which we will designate as a template for reuse as often as we find it needful. We will start with the creation of a new report, upon which we will base our template.

Create a Blank Report

As most of us are aware, creating a new report file is easy.

Template_DatabaseJournal

The Add New Item dialog appears, as shown in Illustration 6.

The design environment opens. We see the Data, Layout and Preview tabs appear in the Report Designer. (As I have mentioned in other articles within this series, I have docked many of my toolbars in places I find convenient. Your environment will probably differ somewhat, and so may not appear, at times, identical to the illustrations that appear in my articles.

The report has opened in Data View, as shown in Illustration 7.

Create a Page Header

Let's create a header, complete with a company logo and title, for our new template report.

We see the new Page Header section atop the Layout area, as shown in Illustration 9.

The Page Header section is, unsurprisingly, where we design our page heading for the report. As we find in most enterprise reporting packages, as well as in Microsoft Office and other applications, we can set various properties in the Page Header section that dictate the behavior of the header, such as whether it appears on all pages of the report (the default), the first page only, and so forth. As we have already intimated, we can insert various objects into the header, although data regions (lists, matrices, tables, and charts), and some other objects that are typically associated with the report Body section, cannot be placed here.

(Do not be concerned with dragging the image into any specific position, sizing it, etc., here. We will typically perform positioning and the like from the Properties window, where we can do it more precisely.)

Illustration 10 depicts graphically the steps above.

As soon as we release the mouse after clicking in the Page Header section, the Image Wizard appears, beginning with the Select the Image Source dialog. We can select from several options here, but for purposes of our practice exercise, we will select the default of Embedded.

As many of us have already discovered, an image item in Reporting Services is an object that contains a reference to an image file that is stored on the report server, embedded within the report, or stored in a database. We will be working with an embedded image in this exercise, to keep things simple, although we will encounter and discuss the other image options in other articles throughout the MSSQL Server 2000 Reporting Services series.

When we embed the image, the Report Designer encodes the image and stores it as text within the report definition (the .rdl file). The primary advantage for using an embedded image is that the image is always available to the report - that is, there is no possibility of it being moved from an external storage location (such as the other types of location options that appear on the Select Image Source dialog entail), and not being accessible by the report at runtime. An unsurprising disadvantage in using this option is the fact that it can bloat the report definition; embedding several images can dramatically increase size, which might not be desirable in some instances.

The Choose the Embedded Image dialog appears.

The Import Image dialog appears, affording us the opportunity to locate and select the image file we wish to embed in the template report header.

Microsoft SQL Server\MSSQL\Reporting Services\ReportManager\en\help

hlp_rswa_logo.gif

The Import Image dialog appears as depicted in Illustration 12.

The Choose the Embedded Image dialog reappears, and displays our selected image file as the default selection. The dialog appears as depicted in Illustration 13.

The Completing the Image Wizard dialog appears, and displays the details surrounding our image selection. At this stage, we can review our settings and back up to make changes, should that be necessary. The dialog appears as shown in Illustration 14.

The Image Wizard closes, and our image appears in the Report Header section.

NOTE: If you have the Properties window docked to one side of the design environment, open it in the manner which is most suitable to you.

The Image Properties window appears, with our settings, as depicted in Illustration 16.

The Textbox Properties window appears, with our settings, as depicted in Illustration 17.

The Page Header Properties window appears, with our settings, as depicted in Illustration 18.

Create a Page Footer

We now have a header, and can move to the creation of a Page Footer. Within the footer, we will insert the additional variables that the client has requested.

We see the new Page Footer section in the Layout area, as shown in Illustration 19.

We will work within the Page Footer section to manage the objects and formats that appear in the footer sections of the various pages of the report. As we noted when discussing the Page Header section, we can insert various objects into the footer, but these do not include data regions (lists, matrices, tables, and charts), and some other objects that are typically associated with the report Body section.

The Textbox Properties window appears, with our settings, as depicted in Illustration 20.

Now we will add a global variable to the footer, using the Value setting within the Textbox Properties window where we have just completed the above settings.

The Edit Expression dialog appears.

"System User: " &

The UserID expression component appears in the Expression area of the dialog. The Edit Expression dialog should appear as depicted in Illustration 22.

Now, let's add some more of the requested information to our footer through the use of Global variables and other expression components within the Value property of a couple of additional Textboxes.

Now we will add another global variable to the footer, using the Value setting within the Textbox Properties window whose settings we have modified.

The Edit Expression dialog appears.

="Title: " & 

The ReportName expression component appears in the Expression area of the dialog, just as UserID did earlier. The Edit Expression dialog should appear as depicted in Illustration 23.

We will add the final variable component to the footer, the page number information, next.

The Edit Expression dialog appears.

="Page " & 

The PageNumber expression component appears in the Expression area of the dialog. We now need to provide for the "total number of pages" portion.

& " of " & 

The TotalPages component is appended, and the Edit Expression dialog appears as shown in Illustration 24.

Let's check our work so far, and verify its effectiveness.

The preview of our template report appears as depicted in Illustration 25.

All appears in order, and so we will save the report, and then locate it for use as a template, in the next section.

Save the Report Definition as a Template

We have created an "empty" report, with header and footer sections, that we wish to reuse in creating new reports going forward. Let's save the report in its original location, for safekeeping, and then save it again as reusable template, with the following steps.

The Save File As dialog appears.

C:\Program Files\Microsoft SQL Server\80\Tools\



	Report Designer\ProjectItems\ReportProject

The Save File As dialog appears similar to the one shown in Illustration 26.

Our template report is now positioned for reuse in prospective report creation.

Verify Operation of the Template Report

Let's do a quick test to make sure our template is available as expected. We will begin the report creation process, where we will be presented with our template as an option, in the following steps:

The New Item dialog appears. In its default, large icons view, we may have trouble discerning our template, especially if we already have other template reports in place.

To proceed in creating a report that adopts the characteristics of our template, we have only to select it here (simply clicking it will do). We would then rename it, in the Name box at the bottom of the dialog, and proceed to create the report in the normal manner. New reports that we create in this fashion will bear all the attributes we have created in the template, saving time and helping authors to maintain a consistent look, and feel.

Through the foregoing steps, we have met the requirements of the client in providing one of what will probably be many template reports to make internal report authors more productive. Templates provide many other advantages in the report creation process, as well. The sophistication of the objects that we can add to our templates can, obviously, allow us to accomplish far more than those we have examined in this introductory session; we will revisit further possibilities in future articles within the series.

Conclusion ...

In this article, we discussed advantages that accrue to users of template reports in Reporting Services, and then activated what we learned in an exercise where we undertook the creation of a basic template to meet the business needs of a hypothetical client. We created a blank report in Report Designer to use as the basis for our template, adding a header with an image file and company name. We then installed a footer, wherein we placed global variables to meet the stated business requirements.

We previewed the template report in Report Designer to ascertain that it met the expressed needs of the client. Next, we installed the template report in the appropriate location to ensure its easy selection, as a "built-in" option, in the creation of any report within Report Designer. Finally, we verified its operation in the initials steps of creating a report, where we noted its potential selection as the basis for a new report.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Reporting Services Forum.