Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II

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.

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