Mastering OLAP Reporting: Display a Dataset Field in a Report Page Header

Monday Jan 16th 2006 by William Pearson
Share:

Architect Bill Pearson leads hands-on practice in displaying a Dataset field in the Page Header of an OLAP report. In this article, we examine one of the simpler approaches for surmounting an apparent limitation within Reporting Services.

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.

Note: Current Service Pack updates are assumed for the operating system, along with the applications and components listed above and the related Books Online and Samples. Images are from a Windows 2003 Server environment, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2005 and its component applications.

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.


Illustration 1: Original Sales Reason Comparisons Report

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.


Illustration 2: Message Received Upon Attempt to Insert Dataset Field into Report Page Header (Compressed View)

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.

NOTE: For more exposure to the MSSQL Server Business Intelligence Development Studio itself, and the myriad design, development and other evolutions we can perform within this powerful interface, see articles in this and my other Database Journal series, Introduction to MSSQL Server Analysis Services. In this article, we will be commenting only on the features relevant to our immediate practice exercise, to allow us to get to the focus of the article more efficiently.

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.

1.  Click Start.

2.  Navigate to, and click, the SQL Server Business Intelligence Development Studio, as appropriate.

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


Illustration 3: Launching SQL Server Business Intelligence Development Studio

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.

3.  Close the Start page, if desired.

4.  Select File --> Open from the main menu.

5.  Click Project / Solution ... from the cascading menu, as shown in Illustration 4.


Illustration 4: Selecting a Project ...

The Open Project dialog appears.

6.  Browse to the AdventureWorks sample reports.

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

7.  Select the AdventureWorks Sample Reports.sln file within the sample reports folder, as depicted (circled) in Illustration 5.


Illustration 5: The Open Project Dialog, with Our Selection Circled ...

8.  Click Open.

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


Illustration 6 The Solution Opens within BI Development Studio ...

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.

9.  Double-click AdventureWorksAS.rds, within the Shared Data Sources folder seen in Solution Explorer.

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


Illustration 7: The Shared Data Source Dialog with Default Settings ...

10.  Click the Edit button on the Shared Data Source dialog.

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


Illustration 8: The Connection Properties Dialog with Default Settings ...

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).

11.  If appropriate, type the correct server / instance name into the Server name box of the Connection Properties dialog. (Mine is MOTHER1\M1MSSQL2K5, as depicted in Illustration 9.)


Illustration 9: The Connection Properties Dialog with Corrected Settings ...

12.  Ensure that authentication settings are correct for the local environment.

13.  Click the Test Connection button.

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.


Illustration 10: Testing Positive for Connectivity ...

14.  Click OK to dismiss the message box.

15.  Click OK to accept changes, as appropriate, and to dismiss the Connection Properties dialog.

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


Illustration 11: The Shared Data Source Dialog with Modified Settings ...

16.  Click OK to close the Shared Data Source dialog, and to return to the development environment.

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.

1.  Right-click the Reports folder underneath the Shared Data Sources folder, in the Solution Explorer.

2.  Select Add --> Existing Item ... from the cascading context menus that appear, as shown in Illustration 12.


Illustration 12: Adding the Report to the Project ...

The Add Existing Item – AdventureWorks Sample Reports dialog appears.

3.  Navigate to the actual location of the sample reports (we provided the default path earlier), if the dialog has not defaulted thereto already.

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.

Click for larger image

Illustration 13: Navigating to the Sample Reports Folder ...

4.  Right-click the Sales Reason Comparisons report inside the dialog.

5.  Select Copy from the context menu that appears, as depicted in Illustration 14.


Illustration 14: Performing a Quick Copy of the Sales Reason Comparisons Report

6.  Right-click somewhere in the white space inside the Add Existing Item – AdventureWorks Sample Reports dialog.

7.  Select Paste from the context menu that appears, as shown in Illustration 15.


Illustration 15: Select Paste within the New Folder ...

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

8.  Right-click the new file.

9.  Select Rename from the context menu that appears.

10.  Type the following name in place of the highlighted existing name:

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.


Illustration 16: The New Report File, RS024_Interactive Sort.rdl

11.  Click the white space to the right of the file name, to accept the new name we have assigned.

12.  Re-select the new file by clicking it.

13.  Click Add on the dialog box to add the new report to report project AdventureWorks Sample Reports.

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.


Illustration 17: The New Report Appears in Solution Explorer – Report Folder

14.  From the main menu in the design environment, select File ---> Save All, as depicted in Illustration 18.


Illustration 18: Select File --> Save All to Save Our Work So Far ...

 

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.

1.  Right-click RS025_Dataset Field in Header.rdl in the Solution Explorer.

2.  Select Open from the context menu that appears, as shown in Illustration 19.


Illustration 19: Opening the New Report ...

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

Click for larger image

Illustration 20: Our Report Opens in Layout View ...

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

3.  Click the Preview tab to execute RS025_Dataset Field in Header.

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

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


Illustration 21: The Report Appears with Default Parameter Selection

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.

4.  Click the Data tab.

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


Illustration 22: The MDX Query Designer

5.  Within the Metadata pane for the Adventure Works cube, locate the Sales Territory dimension.

6.  Expand the Sales Territory dimension by clicking the "+" sign to its immediate left.

7.  Drag the Sales Territory Country Attribute Hierarchy to the Results pane, dropping it between the existing Sales Territory Group and Internet Order Quantity columns, as shown in Illustration 23.


Illustration 23: Placing the Sales Territory Country Attribute Hierarchy Item ...

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

8.  Within the Metadata pane, as before, locate the Sales Reason dimension.

9.  Expand the Sales Reason dimension by clicking the "+" sign to its immediate left.

10.  Drag the Sales Reason Type Attribute Hierarchy to the Results pane, dropping it to the left of the existing Sales Reason column (making Sales Reason Type the left-most column in the pane).

11.  Click the Refresh Fields button, within the toolbar, to ensure that the Dataset fields are refreshed within the Datasets pane. We should see both Sales Territory Country and Sales Reason Type appear in the pane upon refreshment, as depicted in Illustration 24.


Illustration 24: Refreshing the Dataset ...

NOTE: If the Datasets pane is not in evidence, we can call it by selecting View --> Datasets from the main menu.

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

12.  Click the Filter Expression column within the Filter pane (the top pane in the MDX Query Designer), to enable the selector button, as shown in Illustration 25.


Illustration 25: Enabling the Selector within the Filter Expression Column ...

13.  Click the downward pointing selector arrow.

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


Illustration 26: The Picklist Appears for the Product Category Parameter ...

14.  Select the All Products checkbox within the selector, unchecking any others, as depicted in Illustration 27.


Illustration 27: Selecting All Products as the Default for the Parameter ...

15.  Click OK to accept changes, and to close the Filter Expression column selector.

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


Illustration 28: The Filter Expression Column with Default of "All Products"

This will cause the parameter prompt that appears at runtime to default to "All Products," in accordance with the expressed business requirement we have obtained. We will handle the remainder of the modifications that have been requested on the Layout tab of the report.

16.  Click the Layout tab.

17.  Click at some point within the label textbox of the report (containing the cyclist image, and the textbox bearing the label Sales Reason), to make the row and column headers of the matrix data region visible.

18.  Right-click the upper left corner of the matrix. (If the headers disappear as you touch them with the cursor, you should still see a faint outline of the matrix.)

19.  Select Properties from the context menu that appears, as depicted in Illustration 29.


Illustration 29: Accessing the Matrix Properties ...

The Matrix Properties dialog opens, defaulted to the General tab.

20.  Click the Groups tab.

Two groups, plus the Static Group, appear in the Columns list box, and one group appears in the Rows list box. We will first eliminate the Sales Territory Group column grouping, in accordance with the business requirements communicated by the author / developer group.

21.  Click the matrix1_Sales_Territory_Group field (the middle in the Columns list) to select it.

22.  Click the Delete button to delete the group, as shown in Illustration 30.


Illustration 30: Deleting the Unwanted Column Group ...

Next, we will add the Sales Territory Country row group requested by the development team.

23.  Click the Add ... button to the immediate right of the Rows list.

The Grouping and Sorting Properties dialog opens to the General tab.

24.  Type the following into the Name box of the dialog.

matrix1_Sales_Territory_Country_Group

25.  Click the top row in the Group on list, just beneath the Expression label, to expose the selector.

26.  Using the selector, select the following from the picklist provided to populate the top row of the list:

=Fields!Sales_Territory_Country.Value

The Grouping and Sorting Properties dialog, General tab, with our input, appears as depicted in Illustration 31.


Illustration 31: Grouping and Sorting Properties Dialog – General Tab with Our Input

27.  Click OK to accept our input.

The Grouping and Sorting Properties dialog closes, returning us to the Groups tab of the Matrix Properties dialog. Next, we will add the Sales Reason Type row group requested by the client authors / developers group.

28.  Click the Add ... button, once more, to the immediate right of the Rows list.

The Grouping and Sorting Properties dialog again opens to the General tab.

29.  Type the following into the Name box of the dialog.

matrix1_Sales_Reason_Type_Group

30.  Click the top row in the Group on list, just beneath the Expression label, to expose the selector.

31.  Using the selector, select the following from the picklist provided to populate the top row of the list:

 =Fields!Sales_Reason_Type.Value

32.  Click the checkbox labeled Page break at start in the lower portion of the tab.

The Grouping and Sorting Properties dialog, General tab, with our input, appears as shown in Illustration 32.


Illustration 32: Grouping and Sorting Properties Dialog – General Tab with Our Input

33.  Click OK to accept our input.

The Grouping and Sorting Properties dialog closes, and we are again returned to the Groups tab of the Matrix Properties dialog.

34.  Ensuring that the new matrix1_Sales_Reason_Type_Group row group is selected in the Rows list, click the Up button twice, moving the Sales Reason Type Group to the top of the list, as depicted in Illustration 33.


Illustration 33: Moving the Sales Reason Type Group Up in the List – and Leftmost in the Report

The Groups tab of the Matrix Properties dialog, with our modifications and additions, appears as shown in Illustration 34.


Illustration 34: The Groups Tab with Our Modifications

35.  Click OK to accept changes and to close the Matrix Properties dialog.

We return to the Layout tab.

36.  Right-click the textbox, labeled Sales Reason, in the upper left corner of the report.

37.  Select Delete from the context menu that appears, as depicted in Illustration 35.


Illustration 35: Deleting the Sales Reason Label ...

Let's execute the report as it currently stands, to ascertain that all is in good working order.

38.  Click the Preview tab.

The report executes, and displays in a manner similar to that shown in Illustration 36.


Illustration 36: The Executed Report with Modifications

We can move to different pages within the report, by clicking the Next button, as depicted in Illustration 37. Doing so reveals that our instructions to force page breaks with each Sales Reason Type (within the Grouping and Sorting Properties dialog, specifically within our new Sales Reason Type Group row group) are delivering the expected behavior.


Illustration 37: Moving to Successive Pages ...

39.  Click the Layout tab to return to Layout view.

We are now ready to proceed with the final requirements of the developers / authors: to display the Sales Reason Type Dataset field, along with page information, within the report Page Header.

Procedure: Display a Dataset Field within the Report Page Header

All that remains in completing the modifications to the report is to add a Page Header, from which we will display the page information, coupled with the respective Sales Reason Type that is presented on the report.

1.  From the main menu, select Report --> Page Header, as shown in Illustration 38.


Illustration 38: Adding a Header to the Report ...

The Page Header area appears in the Layout view.

2.  Drag the Body bar (separating the Page Header from the Report Body) down to the fourth or fifth "tick" on the meter appearing on the left side of the canvas, as depicted in Illustration 39.


Illustration 39: Increasing Height of the Page Header ...

3.  Drag a textbox from the Toolbox to the right side of the report Page Header, as shown in Illustration 40.

Click for larger image

Illustration 40: Adding a Textbox to the Right Side of the Page Header ...

Before we can reference the Sales Reason Type Dataset field, as stipulated in the business requirements, within the Page Header of the report, we need to ascertain the name of the "container" of the Dataset field within the report body.

4.  Right-click the new textbox, leftmost within the Body of the report, which contains the Sales Reason Type value.

5.  Select Properties from the context menu that appears, as depicted in Illustration 41.


Illustration 41: Adding an Expression to the Textbox ...

The Textbox Properties dialog opens, defaulted to the General tab, as shown in Illustration 42.


Illustration 42: Determining the Reference for the Sales Reason Type Report Item ...

We can see that the textbox is named, simply, textbox5 (or similar default Name assigned by the design environment), as seen in Illustration 42 above. I won't go into the value of following a logical object naming convention in this article – although I typically establish such a convention in most engagements – the point here is that, whatever the reference Name, we must know it to use it in the steps that follow.

6.  Click OK to close the Textbox Properties dialog.

Keeping the textbox Name in mind, we will continue with our new textbox in the report Page Header, taking the following steps:

7.  Right-click the textbox we added on the right side of the report Page Header earlier in this section.

8.  Select Expression ... from the context menu that appears, as depicted in Illustration 43.


Illustration 43: Adding an Expression to the Textbox ...

The Expression Editor opens.

9.  Type (or cut and paste) the following expression into the upper pane of the Edit Expression dialog:


= "Page: " & Globals!PageNumber & " of " & Globals!TotalPages & vbcrlf & 
 "Sales Reason Type: " & ReportItems!textbox5.Value

The expression above concatenates the text "Page: " with PageNumber, a member of the Globals collection within Reporting Services. It then concatenates the combination of text " of ," and another Globals member, TotalPages. Next, we insert vbcrlf to tell Reporting Services to begin a new line. We then add the text "Sales Reason Type: " before finally adding a reference to the value of textbox5, a member of the ReportItems collection.

In the above example, we expose an approach to overcoming the obstacle presented by the inability to display a Dataset field directly within the report Page Header. We have drawn the desired Dataset field into the body of the report. Once we have done this, we can use that field, now a member of the ReportItems collection, within the Page Header. Although the header does not allow Dataset fields, it does allow ReportItems members; herein lies our opportunity to achieve our ends – and to meet the business requirements of our hypothetical client.

The Expression Editor appears, with our input, as shown in Illustration 44.


Illustration 44: The Expression Editor with Our Input

10.  Click OK to accept our input to the Expression Editor.

11.  Click the textbox, once more, if necessary to select it.

12.  Adjust the Font size to 8 point, in the toolbar above the Layout tab, as shown in Illustration 45 (or within the Properties pane for the textbox, if desired).


Illustration 45: Adjusting the Font Size ...

13.  Resize the textbox to a width and height that makes sense for the display of its contents, experimenting as desired.

The Page Header of our report appears within the Layout tab, similar to that depicted in Illustration 46.


Illustration 46: The Textbox within the Report Page Header

We have inserted a Dataset field into the Page Header via the knowledgeable use of the ReportItems collection. We will ascertain the effectiveness of our work in the section that follows.

Verification: Preview the Report and Inspect the Successful Display of a Dataset Field within the Report Page Header

Let's preview the report to inspect the results of our handiwork.

1.  Click the Preview tab.

RS025_Dataset Field in Header executes, and returns the data for the default parameter setting, All Products, as shown in Illustration 47.


Illustration 47: The Report Appears – with Dataset Field in the Page Header ...

Advancing pages further confirms the effectiveness of our solution: basing page breaks upon the field, through the mechanism of the Sales Reason Type Group properties settings, ensures that the Dataset field changes appropriately. I hope that this straightforward use of the ReportItems collection lends itself to extrapolation into uses that truly leverage its latent power. This is only one avenue to meeting the immediate requirement; more importantly, it is the tip of the iceberg in possible creative uses for the ReportItems collection.

2.  Select Save --> Save All to save all work to this point.

3.  Select File --> Exit to leave Reporting Services, when ready.

Conclusion ...

In this article, we examined a means of surmounting an apparent shortcoming of Reporting Services, our inability to directly present Dataset fields in the Page Header or footer of a report. As we noted in the introduction to the session, there are different ways to overcome this inconvenience (and that of most perceived deficiencies) in a business intelligence application as flexible as Reporting Services. We explored one of these approaches, the use of an "alias," supported by the ReportItems collection exposed within the Reporting Services development environment.

As a part of preparing the backdrop for a practice exercise surrounding the setup of support for the display of a Dataset field within a report Page Header, we created a copy of an existing OLAP sample report to leave the original intact for other uses. We made 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, including the need to display a Dataset field in a report Page Header. Finally, we previewed the report to ascertain the effectiveness of our solution. Throughout our practice session, we discussed the results obtained within the development techniques that we exploited.

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

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

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved