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.
» 
Comment and Contribute
