Verification:
Preview the Report and Inspect the Effectiveness of Our Solution
Let’s
preview the report to inspect the results of our handiwork. (One of the
reasons that I chose hierarchical time for the dimensional example with
which we undertook our practice efforts is the unambiguous relationship between
parent and child level members. The idea was to both demonstrate the means of
eliminating the “All” selection within the parameter picklists,
while providing the dual benefit of demonstrating the cascading nature of the
picklists as a secondary advantage of taking the avenue we have taken.)
1.
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 Calendar Year selector.
4.
Select CY
2004 within the Calendar Year parameter picklist.
We
notice the “All Periods” selection that previously appeared atop the Year
parameter’s picklist has now disappeared. We also notice that, once we
make a choice within the Calendar Year dropdown selector, the next
parameter selector, Calendar Quarter, becomes enabled. The
cascading nature of the newly added, date-related parameter set becomes obvious
in this scenario, as the AdventureWorks cube contains data only through August,
and the third quarter (CY Q3) of 2004, a
fact that is reflected in the picklist of three Quarter selection
options, as we shall see next.
5.
Select CY Q2
from the Calendar Quarter picklist, as depicted in Illustration 25.
Illustration 25: Selection
Options Demonstrate Cascading Nature of the Date Parameters
6.
Select the
month of June in the Month parameter picklist, as shown in Illustration
26.
Illustration 26: Cascading
Nature of the Date Parameters Again in Evidence
We
notice again that the “All Periods” selections that previously appeared
atop the Quarter and Month parameters’ picklists have disappeared.
The cascading nature of the date-related parameters is also demonstrated again,
as we see that our selection of CY Q2 enables the picklist for
the Month parameter, which appropriately reflects only the three member Months
belonging to CY Q2 as selections, April, May and June.
7.
Click the View
Report button.
The
report executes quickly and returns the data for the selections we have made
within our parameter picklists, in a manner similar to that depicted in Illustration
27.
Illustration 27: The
Sample Report Operates as Expected with New Date Parameters
Our
verification process has demonstrated the effective removal of the “All”
selection (which appears by default when we chose to generate our parameter
picklists via the automatic creation mechanism that Reporting Services
offers) from our newly added picklists. Moreover, we have witnessed the
cascading nature of the new parameters that is established as a secondary
benefit of adding parameters in the manner we have chosen.
We have
verified that the parameter level filtering we have put into place
accomplishes the intended ends, and allows us to meet the expressed requirement
of the information consumers to remove the “All” selection. We have
also noted that the Reporting Services 2005 environment, with its
graphical design environment, supports easy and flexible design of cascading
parameters, in addition to allowing us to customize picklists via filtering
within the automatically created datasets.
We will
extend our examination of parameterization yet further in the second half of
this article, where we will generate a solution to meet the need of our
colleagues to provide intuitive parameter defaults to information consumers at
report runtime.
NOTE: Please consider saving the .rdl file we have created to this point
for use in the second half of this
article, so as to avoid the need to repeat the preparation process we have
undertaken above.
8.
Experiment
further with the report, if desired.
9.
When ready,
click the Layout tab.
10.
Select File
-> Save DBJ_OLAP_Report.rdl As ... to save our work, up to this
point, to a location where it can be easily accessed for Part II of this
article.
11.
Select File
-> Exit to leave the design environment,
when ready.
Conclusion ...
In
this article, we continued the extended examination of Parameters in Reporting
Services 2005 that we began in Mastering OLAP Reports: Parameters for Analysis Services
Reporting, Pt. I and Mastering OLAP Reports: Parameters for Analysis Services
Reporting, Pt. II. After discussing parameterization
in general, we defined a hypothetical scenario within the context of a
client business requirement: the need to remove the “All” selection that
appears by default within parameter picklists created automatically by Reporting
Services. We then moved into our hands-on practice session.
After
creating a clone of a sample OLAP report, containing a Matrix data
region, we ascertained
connectivity of its shared Analysis Services data source. We then made
structural
modifications to the report, to prepare for our practice exercise session with
three date-related parameters, whose additions to the report were
requested by our hypothetical client. We created, within the graphical
Design Mode of the MDX Query Builder, three filters for which parameterization
was enabled via the Filter pane setting.
In
conjunction with the creation of the parameterized filters, we inspected the automatically created
Report Parameters and their settings, as well as the subsequently
created datasets underlying the new Report Parameters. We then
focused upon the modification of the respective datasets to filter the “All”
parameter level from the data rows retrieved and presented within the parameter
picklists at runtime. Throughout the steps we undertook, we discussed how the various
components were tied together, and the potential challenges we face in
modifying these objects without consideration of the resulting dependencies.
Finally, we previewed
the report to observe the effectiveness of our solution in eliminating the “All”
selection in the picklists at runtime, as well as the added benefit of the
general cascading nature of the parameters at runtime.
»
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Reporting Services Forum.
MS SQL Archives
» 