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”), presenting an
overview of its features, with tips and techniques for real-world use. For
more information on the series in general, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting. For the software components, samples and tools needed to complete the hands-on portion
of this article, see BlackBelt
Administration: Linked Reports in Report Manager, another article within this series.
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 Analysis Services reporting with this
tool is, to say the least, sparse. As I stated in my article, Mastering
OLAP Reporting: Cascading Prompts (where I treated the subject of cascading
parameters for Reporting Services 2000), 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 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, even taking into consideration the
release of several books surrounding Reporting Services 2005 in recent
months, continues to represent a serious “undersell” of Reporting Services,
from an OLAP reporting perspective. I hope to contribute to making this
arena more accessible for everyone, and to share my implementation and
conversion experiences as the series evolves. In the meantime, we can rest
assured that the OLAP potential in Reporting Services will
contribute significantly to the inevitable commoditization of business
intelligence, via the integrated Microsoft BI solution.
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
A common enterprise reporting
requirement among information consumers is the capability to filter reports at
run time for specific information they need. This is typically managed via parameterization,
also known as “prompting”, where the filter criteria is requested (and
hence the consumer is “prompted”) when the report is run. Depending upon the parameter
type (the most common are type-in and picklist), the filters are
typically enacted when the consumer types or selects a value, or a series of
values.
Type-in parameters accept directly typed user input for the values
upon which the report is based. Alternatively, the picklist presents a
selection of choices to a consumer based upon a static file, a dataset from a
larger data source, or through other means. The picklist is often the tool of
choice, because of its inherent elimination of typing errors, as well as the
enforcement of standard selections. A well-constructed picklist makes
selection easy for the consumer, who is not often pleased with a long scrolling
process, or other cumbersome method, as the initial step in generating a
commonly requested report.
A further
refinement of the picklist parameter type is the cascading picklist. In
a cascading picklist scenario, the set of available values among which a
consumer can select for one parameter depends upon the value previously chosen
in another parameter. For example, the first parameter (“state”) could present
a list of states within which the organization’s customers reside. When the
consumer selects a state, the set of possible values presented from which to
select the second parameter (“city”) is updated with a list of cities within
the chosen state. A third parameter (“customers”) could then display a list of
customers within the selected city. The customer name or other ID number could
then be used to filter the report to a particular customer. The process of
filtering a list of parameter values, based upon the value selected for a previous
parameter, is described as “cascading” (and also known as “hierarchical,” or “dependent”).
I have
implemented cascading parameters in numerous ways. One of my favorite
ways to accomplish any sort of picklist parameterization has been to create
support objects within the Analysis Services cube that is used as a data
source for the reports under consideration. For an example of implementing
support for a hierarchical picklist in this manner, see my Database
Journal article Create
a Cube-Based Hierarchical Picklist.
In
this article, we will provide an approach to creating a rudimentary set of cascading
parameters completely within Reporting Services 2005, where the
debut of the new Query Builder makes the assembly of the various parts
much more straightforward than the steps we had to take in Reporting
Services 2000. (For a detailed example of creating cascading parameters
in Reporting Services 2000, see my article Mastering
OLAP Reporting: Cascading Prompts, an earlier member of this series). The use of the Query Builder also presents
challenges, however, when we need to make modifications to components that are
largely automatically generated. In this article, we will discuss the general concepts, and
then set up a scenario within which we work with a basic OLAP report, to expose
the steps involved in setting up cascading parameters, as well as to
identify potential issues that might arise within the set of automatically
generated support objects. In
examining the setup of cascading parameters within an OLAP report
containing a Matrix data region, we will:
-
Open the
sample Report Server project, AdventureWorks Sample Reports, and
ascertain connectivity of its shared Analysis Services data source; -
Create a clone
of an existing sample report, containing a Matrix data region, with
which to perform our practice exercise; -
Make
structural modifications to the clone report, to prepare for our practice
exercise session with Cascading Parameters within a Matrix
data region; -
Create, within
the graphical Design Mode of the MDX Query Builder, multiple
filters for which parameterization is enabled via the Filter pane
setting; -
Inspect the
automatically created Report Parameters and their settings; -
Examine the
automatically created Datasets underlying the new Report Parameters; -
Discuss how
the various components are tied together, and potential challenges we face in
modifying these objects without consideration of the resulting dependencies; -
Preview the report
to observe the Cascading Parameters in runtime action.
Cascading Parameters for Analysis Services Reporting
Objective and Business Scenario
In Mastering
OLAP Reports: Parameters for Analysis Services Reporting, Pt. I, we introduced parameterization in general, discussing the challenges that faced many of us within the Reporting
Services 2005 environment. Chief among the difficulties in working with
the early release of the application was the absence of an MDX editor: while
those of us who were comfortable with MDX syntax were not impaired
significantly (although we had to deal with circumstances that accompanied cascading
parameterization in Reporting Services 2000, such as the need to
string our queries for passage from Reporting Services to the Analysis
Server, and the inability to “test generate” our Datasets, once we
had Parameters in place within the MDX queries), those who were already
challenged with MDX as a language almost certainly found no amusement in
dealing with the added mechanics. We noted that Reporting Services 2005
introduced the MDX Query Builder, a tool that appeals, due to its
“on-off” flexibility, to most practitioners who are comfortable writing direct
MDX queries, as well as to the MDX- challenged, among those authors and
developers who need to work with OLAP data sources. This arrival of the
current edition has, unsurprisingly, changed the way that many of us had become
accustomed to writing the underlying MDX queries for our Analysis Services
reports.
In this article, we continue
the extended
examination of Parameters in Reporting Services 2005 that we
began in Mastering
OLAP Reports: Parameters for Analysis Services Reporting, Pt. I,
where our objective was to explore
the basics surrounding Parameters, revealing several events that take
place in conjunction with their creation in the design environment. Just as we
examined the interaction among the various components that comprise and support
run-time parameterization within our initial article, we will continue our
observation of the actions that Reporting Services takes behind the
scenes in our creation of cascading parameters. We will begin, once
again, by adding simple filter / Parameter combinations to a basic OLAP
report, containing a Matrix data region, via the graphical interface of
the MDX Query Builder. We will then, more importantly, examine the objects
that Reporting Services automatically creates, in conjunction with our
additions, to support cascading parameterization at runtime. Moreover,
we will discuss the relationship of these objects, and issues that can emerge
when we make modifications to them – issues that we can circumvent by taking
steps we will suggest.
