first objective is to create a copy of the Sales Reason Comparisons sample report, within which we can implement the
solutions that we propose to meet our clients requirements. The focus of our efforts in this,
the first half of this article, will be the elimination of the All
level we have described earlier. In Part II of this article we will
examine the addition of intuitive parameter defaults for the same parameters.
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.)
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. We will examine a solution for the
elimination of the default All selections within the parameter
picklists, then, within the first half of this article, and position our
report for the addition of intelligent parameter defaults in the second half.
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 Development 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
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 samples 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 report 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. We can, therefore, use the original 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
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:
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.
Preparation: Modify the OLAP Report for Use within Our Practice Session
next make a few modifications to prepare the report for our practice session.
Our objective will be to begin the session with a simple OLAP report that
contains basic parameterized filters as required by our client
open the report in Layout view (for those of us not already there) and
make the necessary settings to place it into a state from which we can commence
our from scratch practice steps.
(or your own choice of a similar report) in the Solution Explorer.
from the context menu that appears, as shown in Illustration 1, as
Illustration 1: Opening
the New Report ...
begin by adding additional parameters to our sample report. These parameters
will initially be of a standard, basic picklist variety. We will then work
with these parameters as we explore our options for eliminating a
characteristic default behavior of their respective picklists (in this half of
our article), and for adding intuitive parameter defaults to our report (in the
second half of our article).
Additional Parameters through the Query Builder Interface and Review the
Automatic Creation of Support Objects
stated in Mastering OLAP Reports: Parameters for Analysis Services
Reporting, Pt. I, and elsewhere within this,
and other of my, series, the Design Mode button in the toolbar of the Data
tab allows us to easily shift between Design view and Query view,
when working with our OLAP queries. The idea, obviously, is to provide those
who are not at ease with direct MDX a means of creating queries within a
have created the basic report with the core datasets, and are ready to add three
rudimentary, date-related prompts that will meet our objectives. We will create these prompts
within Design view, by taking the following steps:
enter the Data view, which defaults to the presentation of the first of
two pre-existing datasets, ProductData. To state what is probably
obvious, we can tell that we are in Design view, primarily because we see the column headings for our
query components in the Data grid, and we do not see MDX syntax. We
also see that the Design Mode button is depressed, as depicted in Illustration
Illustration 2: MDX
Query Builder in Design Mode (Compressed View)
We have returned
to add three date-related filters (each of which we shall parameterize): one
each for Calendar Year, Calendar Quarter and Calendar Month.
Lets first run the existing query to ascertain that all is in order with
regard to the retrieval of data.
Click the Execute
Query button atop the Data tab, as shown in Illustration 3.
Illustration 3: Click
Execute Query to Retrieve Data with the Existing Query ...
Data populates the Data
pane, and appears similar to that depicted in Illustration 4.
Illustration 4: Initial
Query Operates Successfully in a Quick Test
ascertained that the core query is in working order, we will now add the
date-related filters, as well as the vanilla parameters that we intend for
them to support.
In the Metadata
pane, expand the Date dimension, as required.
expanded Date dimension, expand the Calendar folder.
the Calendar Year attribute hierarchy that appears within the expanded Calendar
to Filter within the context menu that appears, as shown in Illustration
Illustration 5: Select
Add to Filter in the Context Menu
dimension populates the Dimension column, within a newly created row
of the Filter pane. The Calendar Year attribute hierarchy
the Hierarchy column, as well.
Leave the Operator
setting (to the immediate right of the Hierarchy column) at its default
checkmark in the checkbox that appears in the Parameters setting (to the
immediate right of the Filter Expression setting), the rightmost column
in the new row of the Filter pane.
completed filter-with-parameter entry appears in the new row of the Filter
pane, as depicted in Illustration 6.
Illustration 6: The
Filter Pane Reflects Our Addition of the Calendar
Year Attribute Hierarchy
successfully added a new parameter, in conjunction with a filter,
within the dataset underlying our report. The addition of the parameter
within the graphical interface, as we shall see, has already begun a sequence
of events that will trigger the automatic creation of a corresponding Report
Parameter. A supporting dataset will also be created, as soon as we
move to the Layout tab. But first, we need to add entries within the Filter
pane for the additional Date filters we intend to add.
NOTE: As we have noted in other articles, it is important to
remember that, although the Report Parameter and its underlying Dataset
are created automatically when we enable parameterization within the Filter
pane of the MDX Query Builder (Design Mode), the removal of the Parameter
checkmark within the associated row of the Filter pane, or even the
deletion of the entire corresponding filter row, will not produce an opposite
effect. The Report Parameter and the dataset will remain until
they are manually removed. Moreover, the disablement of parameterization
within the Filter pane, followed by re-enablement and / or recreation of
the Filter pane entry will, unless we intervene before we re-enable /
recreate the Parameter, result in the creation of a second Report