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

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

In this article, we will get hands-on exposure to providing the
most basic Parameter support, from the perspective of reports based upon
an Analysis Services 2005 cube, for the information consumers we
support. Our hands-on practice will specifically focus upon how to create a Parameter
(or, more precisely, a “parameterized filter”) through the use of the graphical
user interface, the MDX Query Builder. As a part of our examination we
will take a look at some of the processes that transpire “out of sight” within
Reporting Services, primarily to form a foundation for subsequent articles,
where we build upon this knowledge to create parameterization capabilities
which we can finely customize to the needs of our local environment. While some
of the practical concepts we cover in this article are quite basic, a good
understanding of the nature and sequencing of object creation, based upon
settings we make within the MDX Query Builder, is essential to the more
extended procedures we undertake elsewhere. 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 examining the rudiments of parameterization 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 Parameters within a Matrix data region;

• Create, within
  the graphical Design Mode of the MDX Query Builder, a filter for
  which parameterization is enabled via the Filter pane setting;

• Preview the
  report to observe the runtime Parameter in action;

• Inspect the
  automatically created Report Parameter and its settings;

• Examine the
  automatically created Dataset underlying the new Report Parameter;

• Discuss the
  interaction of the various components in supporting the runtime Parameter
  that the end consumer sees;

• Discuss the
  results obtained with the development techniques that we exploit.