About the Series ...

This article is a member of the series MSSQL Server Reporting Services. The series is designed to introduce MSSQL Server Reporting Services ("Reporting Services"), with the objective of presenting an overview of its features, together with tips and techniques for real-world use. For more information on the series, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting.

As I have stated since the charter article of the series, published about the time Reporting Services was first publicly released, my conviction is that Reporting Services will commoditize business intelligence, particularly in its role as a presentation component within an integrated Microsoft BI solution. Having been impressed from my first exposure to this exciting application, when it was in early beta, my certainty in its destiny grows stronger by the day, as I convert formerly dominant enterprise business intelligence systems, such as Cognos, Business Objects, MicroStrategy, Crystal, and others, to the Reporting Services architecture. I receive constant requests to conduct strategy sessions about these conversions with large organizations in a diverse range of industries – the interest grows daily as awareness of the solution becomes pervasive. Indeed, the five- to six-plus figures that many can shave from their annual IT budgets represent a compelling sweetener to examining this incredible toolset.

Note: To follow along with the steps we undertake within the articles of this series, the following components, samples and tools are recommended, and should be installed / accessible, according to the respective documentation that accompanies MSSQL Server 2005:

Server Requirements

• Microsoft SQL Server 2005 Reporting Services
• Microsoft SQL Server 2005 Database Services
• The AdventureWorks sample databases
• Microsoft SQL Server 2005 Analysis Services
• The AdventureWorks OLAP cube

Client Requirements

• Microsoft Internet Explorer 6.0 with scripting enabled
• Business Intelligence Development Studio (optional)

Sample Files

We will be using one of the AdventureWorks sample reports in the practice section, to save time and focus for the subject matter of the article. The AdventureWorks sample reports are a set of prefabricated report definition files that use the AdventureWorks databases (both relational and Analysis Services) as data sources. The sample reports are highly useful to many new report authors and other practitioners, for whom they serve as a tool to assist in learning the capabilities of Reporting Services, as well as templates for designing new reports. For this reason, we typically make a copy of any report(s) we modify within our lessons.

The samples are not automatically installed. Before we can install the Reporting Services samples, we must have already copied the sample installation program to the PC with which we are working, in accordance with the instructions found in the SQL Server 2005 Books Online and elsewhere. We then run the sample installation program to extract and copy the reports (and other) samples to the computer. The sample installation program also installs the AdventureWorks databases.

The samples come packaged within a Report Server project file, which we will open and use in many lessons, rather than creating a new project file. Please make sure that the samples and the project file are installed before beginning the practice section of this article, so as to provide an environment in which to complete the exercises effectively.

About the Mastering OLAP Reporting Articles ...

One of the first things that become clear to "early adopters" of Reporting Services is that the "knowledgebase" for OLAP reporting with this tool is, to say the least, sparse. As I stated in my article, Mastering OLAP Reporting: Cascading Prompts, the purpose of the Mastering OLAP Reporting subset of my Reporting Services series is to focus on techniques for using Reporting Services for OLAP reporting. In many cases, which I try to outline in my articles at appropriate junctures, the functionality of the reporting solutions of well-established, but expensive, solutions, such as Cognos PowerPlay, can be met in most respects by Reporting Services – at a tiny fraction of the cost.

The vacuum of documentation in this arena, to date, represents a serious "undersell" of Reporting Services, from an OLAP reporting perspective. I hope to contribute to making this arena more accessible to everyone, and to share my implementation and conversion experiences as the series evolves. In the meantime, rest assured that the OLAP potential in Reporting Services will be yet another reason that the application commoditizes business intelligence.

For more information about the Mastering OLAP Reporting articles, see the section entitled "About the Mastering OLAP Reporting Articles" in my article Ad Hoc TopCount and BottomCount Parameters.

Overview

Authoring reports in Reporting Services is largely dependent upon the process of associating controls within the report body with fields that are created within one or more Datasets within the report file. While this is standard operational procedure, and is easily accomplished in ways that vary slightly among the various controls / data regions, one does not develop reports for long without coming across an apparent obstacle to complete freedom with making these associations: when we attempt to use a Dataset field within a report Page Header or Footer, we meet with an error message that informs us that it is not possible to do so.

As with most perceived shortcomings in a business intelligence application as flexible as Reporting Services, there are ways to overcome this inconvenience. As we shall discover in this article, we can address meeting the need to use Dataset fields in a Page Header or Footer through what might be described as the use of an "alias" concept. In this session, we will:

• Create a clone of an existing sample OLAP report, with which to perform our practice exercise;
• Make structural changes to the clone report, based upon a sample SQL Server Analysis Services cube, to meet the business requirements of a hypothetical group of information consumers for displaying a Dataset field within a report Page Header;
• Preview the report to ascertain the effectiveness of our solution;
• Discuss the results obtained within the development techniques that we exploit.

Display a Dataset Field within the Page Header of an OLAP Report

Objective and Business Scenario

The forehanded use of expressions, as we have seen in myriad scenarios within articles of my MSSQL Server Reporting Services series, can enable a report author or developer to accomplish many things that do not seem possible "out of the box." We have seen, time and again, that we can conditionally format via expressions within Reporting Services to achieve virtually any behavior we wish within our reports. Another instance of the power of expressions is their capacity to allow us to substitute the attributes of a report object for those of an object we create for that purpose. While the concepts can certainly be extrapolated to other requirements, an excellent example of the successful use of this approach lies in supporting the need to insert a Dataset field into the Page Header or Footer of a report.

In the following sections, we will perform the steps required to make it possible to display a Dataset field within the Page Header of an OLAP report. (The procedure we will expose works equally well for a Page Footer). To provide a report upon which we can practice the steps of our hands-on exercise, we will begin with the Sales Reason Comparisons sample report, based upon the Adventure Works cube contained within the Analysis Services database, Adventure Works DW, which is available with the installation of the MSSQL Server 2005 samples. The Sales Reason Comparisons report is intended to present comparison summary data from the Adventure Works cube. For the purposes of our article, we will say that we are working with developers and report authors within the Office of the Vice President - Marketing of our client, the Adventure Works organization.

To illustrate the somewhat basic business needs, let's say that the developers / authors have expressed the need for modifications to the existing Sales Reason Comparisons report. We had prepared this report for them in an earlier engagement, where we converted many existing reports from another enterprise reporting application, as a part of unifying many disparate – and expensive – applications within the Microsoft integrated BI solution. Because the conversion saved the organization six figures in licensing costs annually, they were able to retain the employees already in place and avoid an alternative proposal to offshore the business intelligence operation (using the previously existing reporting application) in an attempt to meet budgetary challenges.

The Sales Reason Comparisons report, as it was originally created, appears as depicted in Illustration 1.

Because the internal report authors and developers adapt quickly to the Reporting Services environment, they typically call upon us only when they encounter a hurdle that presents a handicap in their meeting the needs of their internal customers. The current scenario consists of such an apparent obstacle: One of the authors, after successfully making minor modifications to a copy of the existing Sales Reason Comparisons report, has attempted to present a Dataset field, as we shall see, within a newly added report Page Header, to make the report more useful, from the perspective of the end audience. They wish to simply show the field, upon which they perform grouping (and page breaks) within the report atop the report, along with page numbers, so that readers can easily determine the group reported upon by any given page.

The changes requested by the end consumers are largely arrangement–related. First, they wish a new report to be created for a special purpose – a report that, in most respects is identical to the existing Sales Reason Comparisons report. Moreover, they wish for the new report to contain Country, not Territory, grouping, and they want the geographical grouping to be done in the rows (between the Sales Reason and the three measure columns, Internet Orders, Internet Sales Amount, and Internet Total Product Cost), not the columns. Further, they wish for the existing multi-value report parameter, from which one or more Product Categories can be selected as filters at run time, to default to "All" selections, versus the current default of "Bikes and Components."

In addition, the developers / authors tell us that they would like a new group, Sales Reason Type, to be added on the outer left of the report. Further, the group has requested that the report be designed to page break on the Sales Reason Type group. Finally, in addition to its placement within the body of the report, the Sales Reason Type should appear within a Page Header (none currently exists), along with page information, in a format similar to the following:

Page: # of [Total Pages]

Sales Reason Type:  [Name]

Upon initially making the structural changes to the report, and before attempting to insert the Dataset field to the Page Header, the authors performed a couple of test runs with the report, and all appeared to meet expectations. One of the authors next dragged the desired field into the Page Header, assuming that the "acceptance" of the item (when she dropped it onto the Header section of the canvas in Layout view) without any indication of problems meant that all was well. She next attempted to preview the report, and met with the message shown in Illustration 2 almost immediately.

As part of our typical business requirements gathering process, we listen attentively to the details, formulating, in the background, an idea of the steps we need to take in modifying a copy of the report to produce the desired results. Once we grasp the stated need, and confirm our understanding with the intended audience, we begin the process of modifying the Sales Reason Comparisons report to satisfy the information consumers. Because the authors are not certain that they have completed all the steps required in modifying the report, we will make these modifications to a copy we independently create from the original.

Practice

Our first objective is to create a copy of the Sales Reason Comparisons sample report, with which we can implement the requested enhancements we have discussed with the author / developer group. We will perform this, and the other steps of our practice session, from inside the BI Development Studio, which makes its home within Visual Studio .NET 2005.

Preparation: Create a Clone Report within the Reporting Services Development Environment

For purposes of our practice session, we will create a copy of the Sales Reason Comparisons report, one of several samples that are available with (and installable separately from) the Microsoft SQL Server 2005 integrated business intelligence solution. Creating a "clone" of the report means we can make changes to our report while retaining the original sample in a pristine state – perhaps for other purposes, such as using it to accompany relevant sections of the Books Online, and other documentation, in learning more about Reporting Services in general.

Making preparatory modifications, and then making the enhancements to the report to add the functionality to support the subject of our lesson, can be done easily within the Studio environment. Working with a copy of the report will allow us the luxury of freely exploring our options, and leave us a working example of the specific approach we took, to which we can refer in our individual business environments.

Open the Sample Report Server Project and Ascertain Connectivity of the Shared Data Source

To begin, we will launch the SQL Server Business Intelligence Development Studio.

The equivalent on my PC appears as depicted in Illustration 3.

We briefly see a splash page that lists the components installed on the PC, and then Visual Studio .NET 2005 opens at the Start page.

The Open Project dialog appears.

The reports are installed, by default (and, therefore, subject to be installed in a different location on our individual machines), in the following location

C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Report Samples\AdventureWorks Sample Reports

The AdventureWorks Sample Reports solution opens, and we see the various objects within appear in Solution Explorer, as shown in Illustration 6.

Let's first ensure we have a working shared data source. Many of us will be running "side-by-side" installations of MSSQL Server 2000 and MSSQL Server 2005. This means that our installation of the latter will need to be referenced as a server / instance combination, versus a server name alone.

The Shared Data Source dialog opens, and appears with default settings as depicted in Illustration 7.

The Connection Properties dialog opens, and appears with default settings shown in Illustration 8.

We note that the default Server name is "local." While this might prove an adequate setting for a PC with only MSSQL Server 2005 installed (default instance), in the case of many of our installations, the requirement here is for the server / instance combination that correctly identifies the correct MSSQL Server 2005 instance. (Clicking the Test Connection button at this point will provide confirmation whether we need to make this change).

A message box appears, indicating that the Test connection succeeded, assuming that our changes (or lack of same, as appropriate) are appropriate. The message box appears as shown in Illustration 10.

The Shared Data Source dialog appears, with our modified settings, similar to that depicted in Illustration 11.

We are now ready to "clone" a sample report and proceed with the practice exercise.

Create a Copy of the Sales Reason Comparisons Report

As we have noted, we will begin with a copy of the Reporting Services 2005 Sales Reason Comparisons report, which we will use for our practice exercise in meeting the business requirements of the Adventure Works developers / authors.

The Add Existing Item – AdventureWorks Sample Reports dialog appears.

An example of the Add Existing Item – AdventureWorks Sample Reports dialog, having been pointed to the sample Reports folder (which contains the Sales Reason Comparisons report file we seek), appears as partially shown in Illustration 13.

A copy of the Sales Reason Comparisons report appears within the dialog.

RS025_Dataset Field in Header.rdl

NOTE: Be sure to include the .rdl extension in the file name.

The renamed copy of the Sales Reason Comparisons sample report appears as depicted in Illustration 16.

RS025_Dataset Field in Header.rdl appears in the Reports folder, within the AdventureWorks Sample Reports project tree in the Solution Explorer, as shown in Illustration 17.

We now have a clone report file within our Reporting Services 2005 Project, with which we can proceed in the next section to make alterations per the specification we have received, including the display of a Dataset field within a Page Header we add to the report.

Preparation: Enhance the Report per the Business Requirements

As we noted in the Objective and Business Scenario section above, the authors / developers with which we are working have outlined a few enhancements that they wished to make to the report clone, to outfit it to meet a specific business need that is different than the need addressed by the original report. Let's make these changes, before addressing an approach to adding the previously problematic Dataset field to a Page Header, which we will also add to the report.

RS025_Dataset Field in Header.rdl opens in Layout view, and appears as depicted in Illustration 20.

Let's preview the report, so as to get a feel for its general operation prior to performing our enhancements.

Execution begins (the report initially executes with the default parameter setting).

The report executes, and appears as shown in Illustration 21.

We are now positioned to make modifications to the report to support the expressed business requirements. To do so, we will first go to the Data tab, and the MDX Query Designer, where we will make additions to the query to bring in newly required data elements.

The MDX Query Designer appears, with the existing query in place, as depicted in Illustration 22.

NOTE: A red line appears to indicate the drop point for the data element.

Having made the Dataset adjustments, we will now modify the query parameter default within the Filter pane.

The multi-value picklist appears, as depicted in Illustration 26.

The Filter Expression column of the Filter pane appears, reflecting our change, as shown in Illustration 28.