Parameterization from Analysis Services – Cascading Picklists

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.

Introduction

As we learned in Mastering OLAP Reports: Parameters for Analysis Services
Reporting, Pt. I
and Mastering OLAP Reports: Parameters for Analysis Services
Reporting, Pt. II,
a common enterprise reporting
requirement is the capability to filter reports at run time for specific
information. We noted that this is typically managed via parameterization
(known in other enterprise reporting applications 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.

In
Customize
Automatically Created Parameter Support Objects Pt. I
, we reviewed type-in and picklist
parameters in general, and then concentrated our focus, once again, upon picklist
parameters, which we noted to be a frequent choice among information
consumers for user-friendly operation within reports. We next focused upon the
effective use of parameter defaults, in Parameter
Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets
, in making reports that contain
parameters even more user-friendly and efficient at runtime.

In the
most recent article of this series, Support
Parameterization from Analysis Services
, we extended our examination
of parameterization support yet further, continuing our work within the scenario we established within
Customize
Automatically Created Parameter Support Objects Pt. I
, again with an objective of
meeting the need of the hypothetical client to support parameterization,
including picklists, within the report, but with a significant
difference: We exposed the steps involved in offering a simple means of
supporting our solution from within the Analysis Services layer of the
integrated Microsoft BI solution. In examining the support of parameterization from Analysis
Services
, we created a new clone of an existing sample OLAP report,
containing a Matrix data region, to which we made structural modifications, to
prepare for our practice exercise session. We next discussed, and then implemented,
an approach, from within Analysis Services, to meet the need of our
hypothetical client to support Report Parameters from the Analysis
Services
layer of the integrated Microsoft BI solution. Finally, we accessed and employed the new Analysis
Services
parameter support components from within Reporting Services,
reviewing how the various components of the parameter support solution
we proposed were tied together, and concluding with a preview of the report to observe
the effectiveness of our solution in runtime action.

In
this article, we will continue to work with the basic OLAP report we created in Support Parameterization from Analysis Services. Our focus will be primarily to
perform a refinement from the perspective of the support we provided from the Analysis
Services
layer of our integrated BI solution. We will establish cascading
picklists
within the report we created in Support
Parameterization from Analysis Services
(just as we will undertake the addition of support
for intuitive parameter defaults at runtime in our next article), for
greater user-friendliness and overall operational efficiencies. In pursuing this refinement within the support of
parameterization from Analysis Services, we will:

  • Reopen the
    sample Report Server project, AdventureWorks Sample Reports, and
    access the existing sample report we prepared in Support Parameterization
    from Analysis Services
    .
  • Discuss the
    utility of establishing cascading parameters within a report to make it
    more user-friendly and effective.
  • Make
    modifications to the datasets underlying our report parameters to
    incorporate cascading picklist support.
  • Preview the
    report to observe the effectiveness of our solution in runtime action.

Support Parameterization from Analysis Services

Objective and Business Scenario

As any regular reader of this series
is aware, parameterization can be implemented in many ways to fit client
business needs, ranging from the most basic picklist prompts to sophisticated
prompts that “push the envelope,” with regard to going beyond out-of-the-box
uses for these components. In Mastering OLAP Reports: Parameters for Analysis Services
Reporting, Pt. I
and Mastering OLAP Reports: Parameters for Analysis Services
Reporting, Pt. II,
we
noted that one of the most impressive enhancements introduced with Reporting
Services 2005
is its capability to automatically create the complete
“chain” of components, including filter, report parameter, and
supporting dataset(s), that we need to quickly add basic parameterized
filters
to our reports. We discussed the fact that the automatically
created objects serve us well with minimal modification in many cases; we emphasized,
however, the frequent need to “tweak” the components supporting
parameterization, often a bit beyond mere cosmetics, to obtain more
sophisticated features. In two subsequent articles of our series, Reporting Services: Customize Automatically Created
Parameter Support Objects
and Parameter
Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets
,
we outlined some of the customization needs that might arise, and got some
hands-on exposure to the steps required to make the modifications involved.

As we mentioned within
these two articles, as well as within many others throughout my various Database Journal series’, we might offer solutions to meeting reporting requirements that are
completely contained within the reporting layer of the integrated
Microsoft business intelligence solution, but that other options exist at the Analysis
Services
and MSSQL Server RDBMS levels. An important consideration
during the design phase of any implementation effort, as we have noted
repeatedly, is that, while we can still manage much customization within the reporting
/ presentation layer
of our integrated business intelligence solution, we
also have multiple options for placing the intelligence behind parameterization
(as well as other functionality) within other layers of the solution. For
example, I have often supported parameter picklists with support objects
I have created within the Analysis Services database that serves as a data source for the report(s) under
consideration. As I
have emphasized in many other of my articles, one of the numerous advantages of
“placing the intelligence” into layers outside Reporting Services lies
within the fact that we can thus avoid adding the intelligence to support our
needs within every individual report where it is useful; it also means a
central point of maintenance, the enforcement of consistent application of the
logic and / or business rules across multiple reports, and other benefits.

Working
with a new basic OLAP report in Support
Parameterization from Analysis Services
, we began with an objective of meeting the need
of a hypothetical client to support parameterization, including picklists,
within the report. In that article we exposed the steps involved in offering a
simple means of supporting our solution from within the Analysis Services
layer of the integrated Microsoft BI solution, and then accessed and employed the new Analysis
Services
parameter support components from within Reporting Services.
Finally, we previewed
the report to observe the effectiveness of our solution in runtime action.

NOTE: When we consider the fact that
we have many options for “where to install the intelligence” within the
multi-layered BI solution, we can begin to see that a grasp of all layers is
critical. For a general summary of my opinions surrounding the importance of
thinking "multi-dimensionally" within the design and implementation
of a business intelligence system, see Multi-Layered Business Intelligence Solutions … Require
Multi-Layered Architects
.

Having created, in Support Parameterization from Analysis Services, a basic sample OLAP report
containing a matrix data region, to which we added simple filter
/ parameter combinations (based upon structures we created within Analysis
Services
) and having previewed the effectiveness of our solution for rudimentary
runtime parameterization, we will next delve into the modifications we
need to make to meet additional business requirements of a hypothetical client,
the AdventureWorks organization.

To detail the
specifics, the same client team with which we consulted in Support Parameterization from Analysis Services, the Adventure Works
Customer Care department, has made an additional request for our support in
accomplishing its objectives. Our client colleagues inform us that information
consumers have asked that they refine the report we created together in Support
Parameterization from Analysis Services
to make the existing parameterization even more
user-friendly by providing cascading behavior.

As most of us are
aware, in a cascading
picklist
scenario, the set of values of one parameter depends upon the
value chosen in another, typically “previous” parameter. For example, the first
parameter 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, is updated with a list of
cities within the chosen state. A third parameter could then display a list of
customers within the selected city. The customer name, or other identifying
number, could then be used to filter the report to a particular customer. The
process of filtering a list of parameter values, based upon a value from a previous
parameter, is described as “cascading” (and also sometimes known as “hierarchical”
or “dependent”).

We confirm our understanding
of the foregoing need, and propose to construct a working example of a way to
provide the cascading picklists that our colleagues have requested for
the new report parameters – an example, we add, that can be modified to drive
different picklist behavior, should the consumers request further refinements after
“road testing” the initial enhancements.

Preparation

Preparation: Locate and
Open the Report Project and Report Created in Pt I

As a part
of preparation for our practice session in Support
Parameterization from Analysis Services
, we created a clone of a sample OLAP report,
containing a matrix data region, for which we ascertained connectivity of its
shared Analysis Services data source. We then made structural modifications to the
report, to prepare for our practice exercise session with three date-related parameters,
whose additions to the report were requested by our hypothetical client. We
created, within the graphical Design Mode of the MDX Query Builder,
three filters for which parameterization was enabled via the Filter
pane setting.

We discussed,
and then implemented, an approach to meet the need of our hypothetical client
to support Report Parameters from the Analysis Services layer of
the integrated Microsoft BI solution. We then accessed and employed the new Analysis Services
parameter support components from within Reporting Services, examining how
the various components of the parameter support solution we proposed are
tied together to support our runtime parameters.

In this practice session, we will
resume where we left off at the end of Support Parameterization from Analysis Services, modifying our newly created Report
Parameters
further to support the cascading behavior requested
by the client, as described in the section above. We will perform the
preparation and practice steps which follow within the SQL Server Business
Intelligence Development Studio
, as we did within Support Parameterization from Analysis Services.

1. 
Reopen SQL Server Business Intelligence
Development Studio
,
as appropriate.

2. 
Close the Start
page, if desired.

3. 
Reopen the AdventureWorks
Sample Reports project, which contains the sample reports that ship with
the Reporting Services component of the MSSQL Server 2005 suite,
as well as the clone report we created in Support
Parameterization from Analysis Services
.

4. 
Reopen the
report with which we conducted our practice session in Support Parameterization from Analysis Services, which we named DBJ_OLAP_Report,
by double-clicking the report within the Solution Explorer.

DBJ_OLAP_Report
opens in Layout
view, as shown in Illustration 1.



Illustration 1: Our
Sample Report in Layout View

We are
now ready to begin working with the existing report to add support for cascading
picklists
.

William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Latest Articles