Procedure:
Adding Cascading Parameters in Reporting Services 2005
As we
noted earlier, our intent within this article is to begin our examination of Cascading
Parameters by taking the simplest approach: we will add Parameters using
the Query Builder interface in Design Mode. We will parameterize
hierarchical levels of the Date dimension, so as to be able to easily
verify, with no ambiguity, that the resulting cascading parameters work
effectively that they present the appropriate child members based upon the
selection made in the parameter preceding the level to which they belong. Moreover,
we will examine the internal processes that take place behind the scenes.
In other
articles of this series, we 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), for various reasons that become apparent
within the scenarios we encounter there. We compare and contrast methods
throughout the related articles, where relevant, introducing, within each, new
concepts upon which we can build more sophisticated parameterization support
into our organizations reports.
Add
Cascading Parameters through the Query Builder Interface
As we
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
graphical interface.
We
have produced the core dataset, and are ready to build the cascading, date-related
prompts that will meet our objectives. We will create our rudimentary cascading parameter chain
within Design view, by taking the following steps:
We
enter the Data view, as we did earlier. 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 shown in Illustration
36.
Illustration 36: MDX Query
Builder in Design Mode (Compressed View)
We have
returned to add three chained filters (which we will parameterize): one each
for Calendar Year, Calendar Quarter and Calendar Month.
2.
In the Metadata
pane, expand the Date dimension, as required.
3.
Expand the Date
Calendar hierarchy, once again.
While,
as we shall see, we might simply drag the hierarchical levels that we
need to the Filter pane, it is, in my opinion, easier to work directly
with the pane selectors. (We have expanded the Date.Calendar hierarchy more
as a visual reference, in this case.)
4.
Click the
leftmost box (which contains the placeholder <Select dimension>),
within the Dimension column, in the single existing row of the Filter
pane, to enable its dropdown selector.
5.
Select Date
from the list that appears in the selector, as depicted in Illustration 37.
Illustration 37: Adding
the First Date Filter ...
6.
Select Date.Calendar.Year
within the Hierarchy box to the immediate right, using the selector that
is built in.
7.
Leave the Operator
setting (to the immediate right of the Hierarchy setting) at its default
of Equal, which appears when we click the box.
8.
Place a
checkmark in the checkbox that appears in the Parameters setting (to the
immediate right of the Filter Expression setting), the rightmost column
in the top row of the Filter pane.
Our
completed Filter-with-Parameter entry appears in the top row of the Filter
pane, as shown in Illustration 38.
Illustration 38: Filter
Pane of the MDX Query Builder - Settings for First Parameterized Filter
We have
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 triggered the
automatic creation of a 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 filters
within the chain.
NOTE: As we have noted in other articles, and as we will reiterate
multiple times, 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.
9.
Click the Layout
tab to transit to Layout view.
10.
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 DateCalendarYear. 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 Value
and Label fields therein) with the same name as the new Report
Parameter - a Dataset, we will discover, that has also been
automatically created. The purpose of this Dataset is to support the
selection picklist for the new Report Parameter.
Finally,
we note that a couple of settings have been set for us: in the Prompt section
(within the upper third of the dialog) of the Report Parameters dialog,
the Multi-value setting is checked. While we might certainly leave the
setting in place, we will disable it for the purposes of our practice session.
In
addition, in the Default values section (in the lower third of the
dialog), we find that the radio button for Null has been preselected.
We will leave the latter preselected setting (Null) in place; the
disablement of the Allow null value setting in the Prompt section
effectively means that, although the parameter selector defaults to blank at
runtime, the information consumer will be forced to make a selection before he
/ she can proceed further with executing the report. This arrangement works
well in many environments, particularly where default settings tend to be
overlooked, at times, and the report run with unintended constraints -
obviously settings should be adjusted to accommodate local requirements and
realities.
11.
Replace the
default string that appears within the Prompt box, Date.Calendar Year,
with the following, more user-friendly label:
Calendar Year:
12.
Uncheck the
checkbox labeled Multi-value to disable it.
The Report
Parameters dialog appears, with our adjustments, as depicted in Illustration
39.
Illustration 39: Settings
for the Automatically Created Report Parameter
13.
Click OK to
accept settings, and to dismiss the Report Parameters dialog.
14.
Click the
Preview tab next, to execute the report.
The
report runs, and then displays a drop-down parameter prompt labeled Calendar
Year, the selector box of which, by default, contains the placeholder <Select
a Value>, as shown in Illustration 40.
Illustration 40: The
New Runtime Parameter Appears ...
15.
Click the
downward arrow to the right of the Calendar Year parameter selector to
expose the picklist of Year choices.
16.
Select CY
2003.
17.
Click the View
Report button in the upper right corner of the Preview tab.
The
report runs again, and returns for the Date level groupings, for Calendar
Year 2003, as expected.
We
will now return to the Data tab, to examine the Dataset which has
been automatically created, to populate the picklist in support of the new Report
Parameter.
18.
Click the Data
tab once again.
19.
Click the
downward selector arrow on the right side of the Dataset selector.
20.
Select the new
DateCalendarYear Dataset that appears, as depicted in Illustration 41.
Illustration 41: 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
DateCalendarYear Report Parameter (we saw the DateCalendarYear
Dataset referenced within the Dataset selector, where columns from
the new Dataset were assigned to the Value field and Label
field selectors, within the Available values section of the dialog.
As we have noted, the Dataset was created automatically, after the Report
Parameter was itself created, and connected to its underlying Dataset
via the three settings we described, which were also set automatically by Reporting
Services.
As we
noted in Mastering OLAP Reports: Parameters for Analysis
Services Reporting, Pt. I, the automatically MDX generated query
creates additional fields, in addition to retrieving data from the cube, 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 settings
within the Report Parameter dialog reference the ParameterValue
and ParameterCaption fields, respectively, within this Dataset.
NOTE: While we will encounter and 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.
Having examined various details about the objects that are
created to support a parameterized filter, we will return to the Data
tab to create the remaining two members of the cascading parameters chain.
21.
Using the
dropdown Dataset selector, as before, select ProductData, to
return to the primary dataset and Filter pane.
22.
Click the
leftmost box (which contains the placeholder <Select dimension>, as
before), within the Dimension column, in the bottom row of the Filter
pane (this time, beneath the box containing Date, which we added in
creating the first entry in the Filter pane earlier).
The dropdown selector is again enabled.
23.
Select Date
from the list that appears in the selector, once again.
24.
Select Date.Calendar
Quarter of Year within the Hierarchy box to the immediate right,
using the selector that is built in.
25.
Leave the Operator
setting (to the immediate right of the Hierarchy setting) at its default
of Equal, as we did within the first entry.
We
will take a small detour, at this point, to confirm our understanding as to the
timing of Report Parameters creation, based upon our activities within
the Filter pane.
26.
Leaving settings
in the second row of the Filter pane, select Report ->Report Parameters ... from the main menu, once again.
The Report
Parameters dialog opens, revealing only the single Report Parameter
that we saw earlier, DateCalendarYear.
27.
Click OK
to close the Report Parameters dialog.
28.
Returning to
the second row of the Filter pane, where we left off with our settings
to establish a second parameterized filter, place a checkmark in the
checkbox that appears in the Parameters setting, once again.
29.
Leaving the Filter
pane once again, select Report ->Report Parameters ... from the main menu.
The Report
Parameter dialog opens, this time revealing two Report Parameters.
We see that the DateCalendarYear parameter that existed before has been
joined by the DateCalendarQuarterofYear parameter. It thus becomes evident that the
act of checking the Parameters checkbox in the respective Filter pane
row triggers the creation of the associated Report Parameter.
30.
Click the DateCalendarQuarterofYear
item within the Parameters list to select the new parameter within
the Report Parameters dialog.
31.
Replace the
default string that appears within the Prompt box, Date.Calendar Quarter
of Year, with the following, more user-friendly label:
Calendar Quarter:
32.
Uncheck the
checkbox for the Multi-value setting to disable it, as we did with the
first Report Parameter.
The Report
Parameters dialog - with the DateCalendarQuarterofYear parameter selected - appears, with our adjustments,
as shown in Illustration 42.
Illustration 42: Settings
for the New Calendar Quarter Report Parameter
33.
Click OK to
accept settings, and to dismiss the Report Parameters dialog.
Our
completed entry appears in the second row of the Filter pane.
34.
Click the
downward selector arrow on the right side of the Dataset selector, once
again.
An
examination of the Dataset selection picklist reveals only the ProductData
and DateCalendarYear Datasets. In this manner, we can verify that,
although the creation of the Report Parameter, complete with internal
references to a supporting Dataset, is triggered by our placing a
checkmark in the Parameters setting of the respective Filter pane
row, the Dataset itself is not physically created until we leave the Data
tab (as we did in our previous example, when we clicked the Layout tab
to transit to the Layout view).
We
will now create the final parameterized filter within the Filter pane.
35.
In the bottom
row of the Filter pane, click the leftmost box within the Dimension
column, once again.
36.
Select Date
from the list that appears in the selector, as we did before.
37.
Select Date.Calendar
Month of Year within the Hierarchy box to the immediate right, using
the selector that is built in.
38.
Leave the Operator
setting (to the immediate right of the Hierarchy setting) at its default
of Equal, as we did within the first entry.
39.
Place a
checkmark in the checkbox that appears in the Parameters setting, as we
have done with the previous two row entries we have made in the Filter pane.
The Filter
pane, with all three entries, appears as depicted in Illustration
43.
Illustration 43: The
Three Parameterized Filters in the Filter Pane
40.
Select Report
->Report Parameters ... from the main menu, as before.
The Report
Parameters dialog opens, this time revealing three Report Parameters.
41.
Click the DateMonthofYear
listing (the bottom of the three) within the Parameters list to
select the new parameter within the Report Parameters dialog.
42.
Replace the
default string that appears within the Prompt box, Date.Month of Year,
with the following, more user-friendly label:
Calendar Month:
43.
Uncheck the
checkbox for the Multi-value setting to disable it, as we did with the
previous two parameters.
The Report
Parameters dialog - with the DateMonthofYear parameter selected - appears,
with our adjustments, as shown in Illustration 44.
Illustration 44: Settings
for the New Month Report Parameter
44.
Click OK to
accept settings, and to dismiss the Report Parameters dialog.
45.
Click the
downward selector arrow on the right side of the Dataset selector, once
again.
An
examination of the Dataset selection picklist reveals only the ProductData
and DateCalendarYear Datasets, as we left them earlier.
46.
Click the Layout
tab, once again, to transit away from the Data tab.
47.
Click the Data
tab, once more, to return immediately.
48.
Click the
downward selector arrow on the right side of the Dataset selector, one
last time.
Based
upon our earlier discussion, we see the behavior we have expected. All four Datasets,
including the primary Dataset and the three we have added via the
enactment of parameterized filters within the Filter pane (and
subsequent departure from the Data tab) now appear, as depicted in Illustration
45.
Illustration 45: All
Datasets Appear as Expected ...
We have
successfully added our cascading parameters, based upon filters we added
within the Filter pane of the primary Dataset underlying our
report. The addition of the parameters within the graphical interface,
as we have verified, triggered the automatic creation of corresponding Report
Parameters. Supporting datasets were also created, as soon as we
moved away from the Data tab. The timing of the automatic creation of
objects can thus be understood; the sequencing becomes very important in the
event that we need to modify or remove any of the objects after they are
initially created and aligned.
NOTE: As we have noted previously, although a 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.
Order is somewhat important in preventing confusion: for
example, when deleting, the Report Parameter needs to be deleted first, with
the underlying Dataset (to which it refers) being deleted next, before
creating a new Report Parameter even of the same name within
the Filter pane. A Report Parameter can, of course, be
repointed, but will default to an incorrect, remaining Dataset
if the Dataset to which it was initially married was, for some reason,
deleted from under it.
It is important to remember that changing a name can also
have consequences from the perspective of alignment, and that if one side of
the equation is modified, the other side must also be modified. Changing the Prompt
label has no adverse effect, as we have seen multiple times within the
steps of our practice exercise.
We are
now ready to verify the operation of the cascading parameters we have
installed within the OLAP report.