MSSQL Server Reporting Services: Mastering OLAP Reporting: Cascading Prompts - Page 2
July 27, 2004
Create Cascading Picklists for an OLAP Report
Objective and Business Scenario
In the following sections, we will perform the steps required to create a cascading picklist parameter for a clone of an existing sample report. Once we have prepared the report for our exercise, we will construct the datasets required to support cascading parameters. We will base our report and parameter datasets on the FoodMart 2000 sample Sales cube that accompanies the installation of MSAS.
For purposes of our practice procedure, we will assume that information consumers within the Marketing department of the FoodMart organization have expressed the need for cascading, prompted parameters, based upon geographical location of their stores, within a basic report that focuses on sales by product. The Marketing team informs us that they already have a report in place with which they are happy, from the perspective of appearance. The existing report, however, contains a simple parameter that allows them to filter the presentation by Product Family, a characteristic that will not be useful in the report that we are being asked to produce. The consumers want the report to combine all Product Families so as to present a consolidated view of Product sales, which they can then filter to present by individual store.
Having been originally created to present product sales, cost and profit, the report also carries a couple of columns that we will remove, Store Cost and Store Profit, for purposes of this simple revenue report.
The requirement for parameterization surrounds geographical store location. The consumers indicate that they want users of the report to be able to select on a given Country within which FoodMart operates, and then select a State (or other province) from a second parameter, whose picklist presents only the states that reside within the country that is selected in the initial parameter. Next, a third picklist will become available for the Cities within the state selected in the second prompt. Finally, the specific Store can be selected from a fourth parameter picklist, which presents only the stores that exist in the city selected in the third parameter.
Obvious benefits are many, and include easier, more consistent reporting, as well as greater functionality centralized within a given report--parameterized reports can be used to retrieve a wider range of information within a single report, meaning a dramatic reduction in the number of individual reports in the library that, in essence, accomplish the same objectives for individual locations.
Considerations and Comments
For purposes of this exercise, we will prepare a copy of the FoodMart Sales sample report that accompanies the installation of Reporting Services, along with other samples. The "clone" will allow us to leave the original sample report in its pristine (or otherwise existing) condition, as we might have saved various settings, structures, and so forth, for referential or other reasons. There will therefore be no need to remember to return and remove settings that we modify for purposes of the lesson, or to otherwise restore the original sample to its previous state. We can simply discard our clone upon the conclusion of our session, or at any convenient time thereafter.
While the cloning process is simple, ensure that you have the authority, access and privileges needed to accomplish the process, as well as a place to store the copy of the sample report outside of its original location. After the session, the clone can be deleted or used for another purpose, whatever is convenient.
If the sample reports were not installed, or if the FoodMart Sales report was removed prior to your beginning this article, please see the Reporting Services documentation, including the Books Online, for straightforward instructions for obtaining the sample files. As of this writing, a copy of the samples set can be obtained from the installation CD or via download from the appropriate Microsoft site(s).
I am selecting an OLAP report here for several reasons. Primarily, I intend to focus on techniques for using Reporting Services for OLAP reporting within the MSSQL Server 2000 Reporting Services as a specific interest area. Having worked with virtually all enterprise-level OLAP tools over the years, I am quite impressed with the OLAP capabilities that have appeared in the first release of Reporting Services. (In many cases, which I try to outline in my articles at appropriate junctures, the functionality of the reporting solutions of the "Big Sisters" is already met or exceeded, for a fraction of the cost).
Another motivation for wanting to deliver articles surrounding OLAP reporting in this exciting new tool is that a vacuum in the existing documentation is both obvious and unfortunate. The first three major books that were rushed to market almost completely ignore OLAP reporting with Reporting Services (one depicted an MDX snippet expression at the end of the book, as if an afterthought) focusing entirely on relational reporting and making heavy use, typically enough, of the Books Online and other scraps of documentation that we already have anyway. (I could go on, but my overall opinion of the technical book industry is already well known.)
This is, in my opinion, a serious "undersell" of OLAP reporting (indeed, I spoke with a client representative the other day who stated that her department had reviewed Reporting Services, but rejected it, as it "didn't do OLAP!"). I hope to contribute to making this arena more accessible to everyone, and to share my implementation 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.