Procedure:
Adding Basic Parameters in Reporting Services 2005
As we
noted earlier, our intent within this article is to begin our examination of Parameters
by taking the simplest approach: we will add a Parameter using the Query
Builder interface in Design Mode. Moreover, we will examine the
internal processes that take place "behind the scenes." In prospective
articles, we will add Parameters via more manual, less "visually direct"
channels (some of the steps of which will take place within the Query
view versus the Design view). We will compare and contrast methods
throughout the related articles, introducing, within each, new concepts upon
which we can build more sophisticated parameterization support into our
organizations reports.
Add a
Parameter through the Query Builder Interface
As many
of us know already, 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 MDX queries. The idea, obviously, is to provide those
who are not at ease with direct MDX a means of creating queries within a
graphical interface.
We will
create our first Parameter within Design view, by taking the
following steps:
We
enter the Data tab, as we did earlier. We can tell that we are in Design view,
primarily because we see the column headings for our query components in the
data area, and we do not see MDX syntax. We also see that the Design Mode
button is depressed, as shown in Illustration 27.
Illustration 27: MDX Query
Builder in Design Mode (Compressed View)
Let's add
a filter (which we will parameterize) for Sales Territory Country.
2.
In the Metadata
pane, expand the Sales Territory dimension.
3.
Drag the
Sales Territory Country attribute hierarchy into the box marked <Select
dimension>, within the second row of the Filter pane (the row
underneath the Date filter that we inserted earlier), as depicted in Illustration
28.
Illustration 28: Adding
a Filter for Sales Territory Country...
We might
have begun the creation of this Parameter by selecting Sales Territory using the selector in the Dimension
column, and then specifying Sales Territory Region in the Hierarchy column.
Dragging an attribute hierarchy into the filter row, as we have done, results
in the simultaneous population of the Dimension and the Hierarchy
settings.
4.
Click the Filter Expression
box to enable its selector.
5.
Click of the downward arrow
that appears on the right side of the Filter
Expression box.
6.
Expand All
Sales Territories within the dropdown selector.
We see
that the individual Countries appear. We will set the default here to United States.
7.
Click-check United States within the list of Countries,
as shown in Illustration 29.
Illustration 29: Select United States as Default...
8.
Click OK to
accept our selection, and to close the selector.
9.
Place a
checkmark in the Parameters box to the immediate right of the Filter
Expression, now populated by {United States}.
10.
Right-click a
point within the shading of the new row.
11.
Select Move
Up from the context menu that appears, as depicted in Illustration 30.
Illustration 30: Move
the Parameter Row to the Top...
Our
completed Filter-with-Parameter entry appears in the top row of the Filter
pane, as shown in Illustration 31.
Illustration 31: The
Completed Row in the Filter Pane
We have
successfully added a new Parameter, based upon a filter, within the Dataset
underlying our report. The addition of the Parameter within the
graphical interface, as we shall see, has already triggered the automatic
creation of a Report Parameter. A supporting Dataset will also be
created, as soon as we move to the Layout tab.
NOTE: 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 two Report
Parameters.
12.
Click the Layout
tab.
13.
Select Report
-> Report Parameters from the main menu.
We
recall that we previously removed all Report Parameters, as part of our
preparation for the practice exercise. Yet we note, within the Report
Parameters dialog that has appeared, the presence of a new Report Parameter,
called SalesTerritorySalesTerritoryCountry. This Report Parameter
was created automatically when we designated our new row within the Filter
pane as a Parameter (via the enabling checkbox).
Another
important observation we might make is that the From query setting
within the Available values section of the dialog is active (the radio
button is selected), and that the setting references a Dataset (and the
associated Value and Label fields) with the same name as the new Report
Parameter - a Dataset, we will discover, that also been
automatically created. The purpose of this Dataset is to support the
selection checklist for the new Report Parameter.
Finally,
we note that the MDX qualified name for Sales Territory Country
United States, ( [Sales Territory].[Sales Territory Country].&[United States] ), is referenced as the Default
Value for the Report Parameter. (Even though we use the MDX
qualified name within the Default Value here, as at any other time
in the Report Parameter dialog when we set a default, the "label" name -
a "consumer-friendly" name, generally - is what actually appears within the
parameter selector at runtime.)
These
settings appear within the Report Parameters dialog, as depicted in Illustration
32.
Illustration 32: Settings
for the Automatically Created Report Parameter
14.
Click OK.
15.
Click the Preview tab
next, to execute the report.
The report runs, and
then displays a drop-down parameter called Sales Territory Country, defaulted
to United States, as shown in Illustration 33.
Illustration 33: The
New Runtime Parameter Appears...
16.
Click the downward arrow to the
right of the Sales Territory Country parameter to expose the picklist of
Country choices.
17.
Select United Kingdom, in addition to the already selected United States.
18.
Click the View Report
button in the upper right corner of the Preview tab.
The report runs again,
and returns data with higher values than when we ran it for the United States alone.
We will now return to
the Data tab, to examine the Dataset which has been automatically
created (to populate the picklist) to support the new Report Parameter.
19.
Click the Data tab once
again.
20.
Click the downward selector
arrow on the right side of the Dataset selector.
21.
Select the new SalesTerritorySalesTerritoryCountry
Dataset that appears, as depicted in Illustration 34.
Illustration 34: Select
the New Dataset
The Dataset
loads, and presents the MDX syntax in Query view. This is the query
that has been automatically created to generate the picklist to support the new
SalesTerritorySalesTerritoryCountry
Dataset Report Parameter.
22.
Click the Execute
Query button in the toolbar, as shown in Illustration
35.
Illustration 35: Execute
the Query
The query executes, and a data grid becomes populated, as depicted in Illustration 36.
Illustration 36: The Dataset Created for Parameter Picklist Support
An examination of the query and
the data it returns reveals that, in addition to retrieving data from the cube,
the automatically generated query creates additional fields whose sole purpose
is support of the parameter picklist, from which information consumers make the
selections at runtime. These fields are:
-
ParameterCaption
-
ParameterValue
-
ParameterLevel
All three
fields are products of calculated members created via MDX syntax (using
the WITH MEMBER keyword combination). As we discover within other
articles of the series, there are multiple ways to approach picklist support (I
very often construct members such as the above at the Analysis Services
level, to afford central maintenance and reusability, among other, perhaps less
obvious, advantages). This is simply the mechanism through which Reporting
Services accomplishes support of the automatically generated Report
Parameter. As we saw earlier, the Value and Label references
within the Report Parameter dialog reference the ParameterValue
and ParameterCaption fields, respectively, within this Dataset.
NOTE: While we will discuss MDX functions, expressions,
queries, and related subject matter throughout the MSSQL Reporting
Services series, please refer to the articles of my MDX
Essentials series, whose member articles are published monthly here at Database
Journal, for detailed information about MDX.
In
summary, it is important, as we go forward in our extended examination of Parameters,
to realize how Reporting Services automatically creates both the Report
Parameter and the Dataset that supports the Parameter at
runtime. As we venture into custom parameterization within various practice
sessions elsewhere, we will need to keep in mind how the parts knit together
within this basic context, so as to understand the relationships that we need
to establish manually.
23.
Select File
-> Save All to save our work to this point.
24.
Select File
-> Exit to leave the design environment,
when ready.
Conclusion...
In
this article, we began an extended examination of Parameters in Reporting
Services 2005. In this, the introductory article for a subseries that we
will devote to the subject, we explored the basics surrounding Parameters,
revealing several actions that take place in conjunction with their creation in
the design environment. After preparing a copy of a sample report for our
practice session, we added a standard filter to the existing Dataset.
Keeping this and subsequent efforts within the MDX Query Builder, the
graphical interface within Reporting Services for creating Datasets
from an Analysis Services data source, we next created another filter-this
time a filter with a Parameter.
We previewed the
report, noting the appearance of the runtime parameter during execution. We
then examined, from within the Layout view, the Report Parameter
that had been automatically created in the background by Reporting Services when
we enabled parameterization within the Filter pane of the MDX Query
Builder. Finally, we noted that a Dataset had also been created
automatically (its creation having been triggered upon our initial entry to the
Layout tab, once we had enabled the Parameter within the
associated row of the Filter pane); this Dataset, we recalled,
had been referenced within the new Report Parameter, where both the Value
and Label fields used within the runtime Parameter picklist
are specified. Our examination of these basic underlying processes served to
arm us with a working knowledge of how Report Parameters interact with
their underlying Datasets in general, and, specifically, how these
components are created when we design parameters into a report from within the MDX
Query Builder in Design Mode.
»
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Reporting Services Forum.