Practice
Practice:
Modify Datasets in Reporting Services to Support Cascading Parameter Picklists
We noted in Support Parameterization from Analysis Services, in the section titled “Verification: Preview the Report and Inspect the
Effectiveness of Our Solution,” that the parameter picklists we had put into place in the practice section did
not cascade. An example of this behavior can be easily
demonstrated by taking the following steps.
1.
From our
current position in Layout view, click the Preview tab.
DBJ_OLAP_Report.rdl initializes, and the first prompt
(based upon the pre-existing ProductCategory parameter), becomes
enabled.
2.
Leave the Product
Category prompt selection at its default of” Bikes, Components.”
3.
Click the
downward pointing arrow on the right side of the Year selector.
4.
Select CY
2004 within the Year parameter picklist.
5.
Click the
downward pointing arrow on the right side of the Quarter selector.
We
notice at this stage that all four quarters appear in the Quarter
selector, as depicted
in Illustration 2.
Illustration 2: Evidence
of Non-Cascading Behavior in the Report ...
The
appearance of four quarters is a circumstance that is contradicted by the fact
that CY 2004 is not a complete year in our cube – only the first
three quarters contain data, so CY Q4 should not appear as a
selection option. We can thus see clearly that we need to make some
modifications to the report to support cascading picklists. We will
accomplish this in the steps that follow.
6.
Click the Data
tab to return to the datasets underlying the report.
Here
we will make modifications to “chain” the report’s datasets, which we created
in Support Parameterization from Analysis
Services to support our
time-related parameters. This might be handled several ways, but the use of
simple WHERE expressions in the dataset queries supporting the Quarter
and Year picklists will serve as a quick, intuitive approach.
7.
Click the
downward selector arrow on the right side of the Dataset selector.
8.
Select AS_DateQtrParams_Support
within the selector, as shown in Illustration 3.
Illustration 3: Select
the AS_DateParamsQtrSupport Dataset
The Dataset opens.
9.
Underneath
the syntax appearing within Query pane of the Query Designer, type the following WHERE
clause:
<WHERE
(STRTOMEMBER(@DateCalendarYear,
CONSTRAINED))
Here
we are filtering, via the MDX WHERE clause, the data we are retrieving
for Quarter picklist support to only those quarters that are properly
included for the year we specify when prompted at runtime.
The Query
pane, with our syntax addition, appears as depicted in Illustration 4.
Illustration 4: The
Query, with Newly Added WHERE Clause ...
We
also need to declare the new query parameter (seen as the @DateCalendarYear
placeholder within the WHERE clause) by taking the next step.
10.
Click the Query
Parameters button atop the Data pane (the second button to the left
of the Execute Query (!) button).
The Query
Parameters dialog opens.
11.
Click the leftmost
box in the first row of the dialog, labeled Parameter, to activate it.
12.
Type the
following into the box:
DateCalendarYear
13.
Click the next
box to the right, labeled Dimension, to activate its selector button.
14.
Select Date
within the selection list that appears, as shown in Illustration 5.
Illustration 5: Select
the Date Dimension ...
15.
Click the next
box to the right, labeled Hierarchy, to activate its selector button.
16.
Select Date.Calendar
within the selection list that appears, as depicted in Illustration 6.
Illustration 6: Select
the Date Dimension, Calendar Hierarchy ...
17.
Click the box
to the far right, labeled Default, to activate its selector button.
18.
Using the “+”
sign to the immediate left of the All Periods level in the hierarchical
tree that appears, expand to the next level, Calendar Years.
19.
Click CY
2004 to highlight / select it, as shown in Illustration 7.
Illustration 7: Select
CY 2004 as the Default Year ...
20.
Click OK
to accept our selection and to close the selector.
The Query Parameters dialog
appears, with our settings, as depicted in Illustration 8.
Illustration 8: The
Query Parameters Dialog, with Our Input
21.
Click OK
to accept input and to dismiss the dialog.
Selecting
a default for the new Query Parameter allows us to execute the parameter
immediately, within the MDX
Query Builder, as the Default value is
substituted for the parameter placeholder in the query syntax at runtime.
22.
Click the Execute
Query (!) button to run the query against the Analysis Services
data source, as shown
in Illustration 9.
Illustration 9: Execute
the New Query ...
The query
runs, and the data is returned, as depicted in Illustration 10.
Illustration 10: The New
Dataset Appears ...
As we can
see, we have the workings of a good support dataset for the cascading Quarter
parameter picklist – the captions and values correctly
reflect the fact that our cube contains data within only three quarters for
CY 2004. (This is why I chose CY 2004 as the default, to show the
effect on the dataset, and therefore to demonstrate the effectiveness of its
use to support the properly cascading parameter picklist.)
Let’s
make a similar adjustment to the Month parameter picklist support query.
23.
Click the
downward selector arrow on the right side of the Dataset selector, once
again.
24.
Select AS_DateMoParams_Support
within the selector, as shown in Illustration 11.
Illustration 11: Select
the AS_DateParamsMoSupport Dataset
The Dataset opens.
25.
Underneath
the syntax appearing within Query pane, type the following WHERE clause:
WHERE
(STRTOMEMBER(@DateCalendarQuarterofYear,
CONSTRAINED))
We
are, of course, filtering once again, via the MDX WHERE clause. This
time, we are restricting the data we are retrieving for Month picklist
support to only those months that are properly included within the quarter we
specify when prompted at runtime. We also need, as we noted when adding a
parameter to the Quarter parameter query earlier, to declare the new query
parameter (seen as the @DateCalendarQuarterofYear placeholder within
the WHERE clause), which we will accomplish by taking the next step.
26.
Click the Query
Parameters button atop the Data pane, as we did earlier.
The Query
Parameters dialog opens, once again.
27.
Click the
leftmost box in the first row of the dialog (“Parameter”) to activate
it, as before.
28.
Type the
following into the box:
DateCalendarQuarterofYear
29.
Click the next
box to the right (“Dimension”), as we did with the last dataset, to
activate its selector button.
30.
Select Date
within the selection list that appears, exactly as we did in the previous
dataset Query Parameters dialog.
31.
Click the next
box to the right (“Hierarchy”), to activate its selector button, as we
did earlier.
32.
Select Date.Calendar
within the selection list that appears, again precisely as we did in the
previous dataset Query Parameters dialog.
33.
Click the box
to the far right of the row (“Default”), as before, to activate its
selector button.
34.
Using the “+”
sign to the immediate left of the All Periods level in the hierarchical
tree that appears, expand to the next level, Calendar Years.
35.
Expand Calendar
Year CY 2004, exposing the two Semester level members H1 CY
2004 and H2 CY 2004.
36.
Expand Semester
H1 CY 2004, exposing its Quarter level members Q1 CY 2004
and Q2 CY 2004.
37.
Click Q2 CY
2004 to highlight / select it, as depicted in Illustration 12.
Illustration 12: Select
Q2 CY 2004 as the Default Quarter ...
38.
Click OK
to accept our selection and to close the selector.
The Query Parameters dialog
appears, with our settings, as shown in Illustration 13.
Illustration 13: The
Query Parameters Dialog, with Our Input
39.
Click OK
to accept input and to dismiss the dialog.
40.
Click the Execute
Query (!) button, as we did earlier, to run the query against the Analysis
Services data source.
The query
runs, and the data is returned, as depicted in Illustration 14.
Illustration 14: The New
Dataset Appears ...
We have added
a support dataset for the cascading Month parameter picklist – the captions
and values correctly reflect the months that belong to our selected
default quarter, Q2 CY 2004.
We
have extended the parameter support structures we created within Analysis
Services in Support Parameterization
from Analysis Services to enable cascading picklists. We are now ready to verify
the effectiveness of our solution. We will verify that the date-related Report
Parameters – supported by objects within the Analysis Services layer
of our integrated BI solution – perform as expected at runtime, providing cascading
picklists as a part of operation.
» 
Comment and Contribute
