Parameters for Analysis Services Reporting
Objective and Business Scenario
Throughout many past
articles of the MSSQL Server Reporting Services series, we have leveraged
parameters within the context of MDX queries. Reporting Services 2000,
initially intended as a component of the MSSQL Server 2005 "BI Release",
was released early, as many of us are aware, with resulting limitations in some
of its capabilities, as most realistic practitioners would expect. One of the
challenges that faced many of us 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 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. 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 the MDX- challenged
among those authors and developers who need to work with OLAP data sources.
This enhancement 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
begin an extended
examination of Parameters in Reporting Services 2005. Our
initial objective will be to explore the basics surrounding Parameters,
revealing several events that take place in conjunction with their creation in
the design environment. Within our examination of the interaction among the
various components that comprise and support run-time parameterization in Reporting
Services 2005 in general, and with a specific focus upon the interaction of
these components in conjunction with the use of MDX as the query language, we
will establish the foundation for more sophisticated parameterization concepts
that we introduce in prospective articles. We will begin by adding a simple
filter / Parameter combination 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 addition, to support
parameterization at runtime. An understanding of the relationship of these objects
will serve as the foundation for more elaborate parameterization techniques in
sessions that follow.
Practice
Our
first objective is to create a copy of the Sales Reason Comparisons sample report, within which we can implement basic
Parameters.
The focus of our
efforts will be the addition of parameterization into an OLAP report containing
a Matrix data region (the mechanics behind adding the capability,
not the design of the report itself). Because of time limitations, we will be
working with a simple, pre-existing sample report – in reality, the business
environment will typically require more sophistication. The process of setting
up basic parameterization is the same in real world scenarios, with perhaps a
more complex set of underlying considerations.
(I virtually never encounter a client reporting requirement that does not
involve at least basic parameterization.)
We
will perform our practice session from inside the MSSQL Server Business
Intelligence Development Studio. For more exposure to the Business
Intelligence Development Studio itself, and the myriad design, development
and other evolutions we can perform within this powerful interface, see other
articles in this series, as well as within my Database Journal series Introduction
to MSSQL Server Analysis Services. In this article, we will be commenting only on the
features relevant to our immediate practice exercise, to allow us to keep to
the focus of the article more efficiently.
Preparation:
Create a Clone Report within the Reporting Services Development Environment
For purposes of our
practice session, we will create a copy of the Sales Reason Comparisons report, one of several samples
that are available with (and installable separately from) the MSSQL Server
2005 integrated business intelligence suite. Making preparatory
modifications, and then making the enhancements to the report to add the
functionality that forms the subject of our lesson, can be done easily within
the Business Intelligence Studio environment. Working with a copy
of the report will allow us the luxury of freely exploring our options, and will
leave us with a working example of the specific approach we took, to which we
can refer in our individual business environments.
Open
the Sample Report Server Project
For purposes of our
practice session, we will open the AdventureWorks Sample Reports
project, which contains the sample reports that ship with the Reporting
Services component of the MSSQL Server 2005 suite. We will complete
our practice session within the sample project so as to save the time required
to set up a development environment from scratch within the Business
Intelligence Development Studio.
To open the AdventureWorks
Sample Reports project, please see the following procedure in the References
section of my articles index:
Ascertain
Connectivity of the Shared Data Source
Let’s
ensure we have a working data source. Many of us will be running "side-by-side"
installations of MSSQL Server 2000 and MSSQL Server 2005. This
means that our installation of the latter will need to be referenced as a server
/ instance combination, versus a server name alone. (The default for
the Adventure Works DW project sample’s connection is localhost,
which will not work correctly in such a side-by-side installation, as MSSQL
Server 2000 will have assumed the identity of the local PC by default.)
If you do not know how
to ascertain or modify connectivity of the Analysis Services data
source, please perform the steps of the following procedure in the References
section of my articles index:
Create
a Copy of the Sales Reason Comparisons Report
We will begin with a copy
of the Reporting Services 2005 Sales Reason Comparisons OLAP report,
which we will use for our practice exercise. Creating a "clone" of the project means we can make
changes to select contents (perhaps as a part of later exploration with our
independent solution), while retaining the original sample in a pristine state
for other purposes, such as using it to accompany relevant sections of the Books
Online, and other documentation, as a part of learning more about Reporting
Services (particularly an OLAP report using an Analysis Services data
source), and other components of the Microsoft integrated business
intelligence solution in general.
If you do not know how
to create a copy of an existing report, please perform the steps of the
following procedure in the References section of my articles index:
We now
have a clone OLAP report file within our Reporting Services 2005 Project,
with which we can proceed in the next section to make modifications for our
subsequent practice session.
» 
Comment and Contribute
