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