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:
- 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
- Microsoft Internet Explorer 6.0 with scripting enabled
- Business Intelligence Development Studio (optional)
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.
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 arrangementrelated. 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.
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
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.
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
the Sample Report Server Project and Ascertain Connectivity of the Shared Data
begin, we will launch the SQL Server Business Intelligence Development Studio.
and click, the SQL
Server Business Intelligence Development Studio, as appropriate.
equivalent on my PC appears as depicted in Illustration 3.
Launching SQL Server Business Intelligence Development Studio
briefly see a splash page that lists the components installed on the PC, and
then Visual Studio .NET 2005 opens at the Start page.
Close the Start
page, if desired.
--> Open from the main menu.
/ Solution ... from the cascading menu, as shown in Illustration 4.
Selecting a Project ...
Project dialog appears.
Browse to the AdventureWorks
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
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 ...
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 ...
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
within the Shared Data Sources folder seen in Solution Explorer.
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
Properties dialog opens, and appears with default settings shown in Illustration
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
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
15. Click OK to accept
changes, as appropriate, and to dismiss the Connection Properties dialog.
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
We are now ready to "clone" a sample report and
proceed with the practice exercise.
a Copy of the Sales Reason Comparisons Report
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 Reports folder underneath the Shared Data Sources folder, in
the Solution Explorer.
--> Existing Item ... from the cascading context menus
that appear, as shown in Illustration 12.
Illustration 12: Adding
the Report to the Project ...
Existing Item AdventureWorks Sample Reports dialog appears.
the actual location
of the sample reports (we provided the default path earlier), if the dialog has
not defaulted thereto
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.
the Sales Reason Comparisons report inside the dialog.
from the context menu that appears, as depicted in Illustration 14.
Performing a Quick Copy of the Sales Reason Comparisons Report
somewhere in the white space inside the Add Existing Item AdventureWorks Sample Reports dialog.
from the context menu that appears, as shown in Illustration 15.
Illustration 15: Select
Paste within the New Folder ...
of the Sales Reason Comparisons report appears within the dialog.
the new file.
from the context menu that appears.
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.
renamed copy of the Sales Reason Comparisons sample report appears as
depicted in Illustration 16.
The New Report File, RS024_Interactive Sort.rdl
white space to the right of the file name, to accept the new name we have
new file by clicking it.
on the dialog box to add the new report to report project AdventureWorks
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
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 ...
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.
Enhance the Report per the Business Requirements
As we noted in
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
Field in Header.rdl in the Solution Explorer.
from the context menu that appears, as shown in Illustration 19.
Illustration 19: Opening
the New Report ...
Field in Header.rdl opens
in Layout view, and appears as depicted in Illustration 20.
preview the report, so as to get a feel for its general operation prior to
performing our enhancements.
Click the Preview
tab to execute RS025_Dataset Field in Header.
begins (the report initially executes with the default parameter setting).
report executes, and appears as shown in Illustration 21.
Illustration 21: The
Report Appears with Default Parameter Selection
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.
Query Designer appears, with the existing query in place, as depicted in Illustration
Illustration 22: The MDX
Within the Metadata
pane for the Adventure Works cube, locate the Sales Territory dimension.
Expand the Sales
Territory dimension by clicking the "+" sign to its
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
Within the Metadata
pane, as before, locate the Sales Reason dimension.
Expand the Sales
Reason dimension by clicking the "+" sign to its immediate
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).
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.
made the Dataset adjustments, we will now modify the query parameter
default within the Filter pane.
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
Illustration 25: Enabling
the Selector within the Filter Expression Column ...
downward pointing selector arrow.
picklist appears, as depicted in Illustration 26.
Illustration 26: The
Picklist Appears for the Product Category Parameter ...
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 ...
to accept changes, and to close the Filter Expression column
Expression column of the Filter pane appears, reflecting our change,
as shown in Illustration
Illustration 28: The
Filter Expression Column with Default of "All Products"
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.
Click the Layout
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.
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.)
from the context menu that appears, as depicted
in Illustration 29.
Accessing the Matrix Properties ...
Properties dialog opens, defaulted to the General tab.
Click the Groups
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.
Click the matrix1_Sales_Territory_Group
field (the middle in the Columns list) to select it.
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.
Click the Add
... button to the immediate right of the Rows list.
and Sorting Properties dialog opens to the General tab.
following into the Name box of the dialog.
Click the top
row in the Group on list, just beneath the Expression label, to expose
selector, select the following from the picklist provided to populate the top
row of the list:
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
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.
Click the Add
... button, once more, to the immediate right of the Rows list.
and Sorting Properties dialog again opens to the General tab.
following into the Name box of the dialog.
Click the top
row in the Group on list, just beneath the Expression label, to
expose the selector.
selector, select the following from the picklist provided to populate the top
row of the list:
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
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.
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
Illustration 34: The
Groups Tab with Our Modifications
to accept changes and to close the Matrix Properties dialog.
return to the Layout tab.
the textbox, labeled Sales Reason, in the upper left corner of the
from the context menu that appears, as depicted in Illustration 35.
Illustration 35: Deleting
the Sales Reason Label ...
execute the report as it currently stands, to ascertain that all is in good
report executes, and displays in a manner similar to that shown in Illustration
Illustration 36: The
Executed Report with Modifications
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 ...
Layout tab to return to Layout view.
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.
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.
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