Verification:
Preview the Report and Inspect the Effectiveness of Our Modifications and
Enhancements
Let’s
preview the report to inspect the results of our handiwork.
1.
Click the Preview
tab.
DBJ_OLAP_Report.rdl initializes, and the Year
prompt becomes enabled.
We
note that the Year parameter is once again displaying the ParameterCaptionIndented
Label field - something we had already changed to the standard ParameterCaption
Label field within the Report Parameters dialog for the respective
parameter TimeYear earlier. It is, perhaps, useful to note, at this
stage, that the Label field has reset itself to this, the Reporting
Services default, when we “declared” the TimeYear query parameter
within the TimeMonth dataset, as a part of making the latter cascade
properly, based upon the selection we made within the TimeYear picklist.
To restore the standard ParameterCaption Label field, we can simply
return to the Report Parameters dialog for the TimeYear parameter,
and reselect ParameterCaption via the Label field selector, as
shown in Illustration 23.
Illustration 23: Adjusting
the Label Field, which has Reset to Default ... (Optional)
2.
Click the Preview
tab, once again, if you have made the change noted above.
3.
Click the
downward pointing arrow on the right side of the Month parameter
selector.
We
note that only two months appear within the Month parameter
picklist - the only populated member months of FY 2005 within the Adventure
Works sample cube.
NOTE: Ignore the results that appear if the report auto-executes
at this point. The default year / month parameter selections are
for a period that experienced limited (less than three) Sales Reasons
associated with its activity.
Let's
try changing the Year at this point to demonstrate further the fact the cascading
is working effectively:
4.
Select FY
2004 within the Year parameter picklist.
Once
we make a selection within the Year dropdown selector, the next parameter
within the cascading chain, Month, becomes enabled, once
again.
5.
Click the
downward selector button that appears on the right of the Month
parameter, as we did before, to expose the parameter picklist, as partially
depicted in Illustration 24.
Illustration 24: The
Parameter Picklist Cascades to Display the Member Months of FY 2004 (Partial
View)
We
note (scrolling down, as necessary) that twelve months appear within the
Month parameter picklist - the populated member months of FY
2004 within the Adventure Works sample cube. We can easily see that
the Month parameter picklist cascades appropriately, based upon the
selection we make in the Year parameter picklist.
6.
Select June
2004 within the Month parameter picklist (the bottom entry appearing
within the selector).
Let's
verify the operation of the “top count” capability we have added via the
No. Top Items parameter. As we can see the default of “3”
appears in the selector.
7.
Leaving the No.
Top Items parameter selection at default (“3”),
click the View Report button in the upper right corner of the Preview
tab, to execute the report with current settings.
The
report executes, and displays its output, including a “top count” of three
Sales Reasons (based upon Internet Sales Amount), as shown in Illustration
25.
Illustration 25: The
Report Displays a “Top Count” Span of Three Sales Reason Items
Let's
perform another quick test of the parameterized “top count” capability.
8.
Click the
downward pointing arrow to the immediate right of the No. Months to Report parameter
selector.
9.
Select the number
“5” within the No. Top Items parameter picklist.
10.
Click the View
Report button in the upper right corner of the Preview tab, to
execute the report with current settings.
The
report executes, displaying its output, this time presenting the Sales
Reasons associated with five largest Internet Sales Amounts, as depicted
in Illustration 26.
Illustration 26: The
Report Displays the Five Largest (Based upon Internet Sales)
Sales Reason Items
We see, therefore,
that the “top sales” parameter we have put into place, No. Top Items,
accomplishes the intended ends, and enables us to meet the business need expressed
by the information consumers. More generally, we can easily see that Reporting
Services 2005, with its graphical design environment, supports easy and
flexible design of innovative parameters, which can be based upon a
sizable inventory of MDX functions and operators, among other
options. Further, we have seen how we can design parameters to cascade,
and to otherwise interact in an intuitive, user-friendly fashion. While we
might have gone significantly further in
designing function-based defaults for cascading parameter picklists,
or even more elaborate, dynamically adjusting “top count” spans, based
upon other criteria we selected, and a host of other possibilities, we assure
our client colleagues that, once they understand the basics, we can certainly
help them to further enjoy the myriad more advanced capabilities that are
exposed within the current version of Reporting Services.
11.
Experiment
further with the report, if desired.
12.
Select File
-> Save All to save our work to this point.
13.
Select File
-> Exit to leave the design environment,
when ready.
Conclusion ...
In this article, we concluded another extended examination, which
we began in Mastering OLAP Reports: Parameterizing Number of
“Top” Items with the MDX TopCount() Function, Part I, of parameterization in Reporting Services
2005. We continued toward our primary objective of getting hands-on
exposure to parameterizing TopCount() within the sample OLAP
report that we cloned in the first half
of the article, where we began our practice session by setting up a scenario
within which to work with a basic OLAP report in exposing the steps involved.
We picked up, in this article, with the clone of an existing sample
report, containing a matrix data region, which we had created, and
structurally modified further for our practice session, in Part I. We also briefly recounted an overview of the MDX TopCount()
function we had performed in the first half
of this article, discussing details of the use we intended for the function to
support the stated reporting needs of a hypothetical client. In so doing, we
touched upon general concepts surrounding the parameterization of MDX
functions in general, and the TopCount() function specifically.
In this,
the second half of our article, we continued our practice session by reviewing
and ensuring the adequacy of the datasets (automatically created when we
added the required query parameters to support date and function
parameterization in Part I) to support report parameters and
meet the stated business requirements. We next added syntax to the Month
dataset query to enforce cascading, based upon the selection made
for the Year parameter by an information consumer at runtime.
Finally, we added syntax to enforce the Month parameter selection and
leveraged the MDX TopCount() function, containing the set
from which we wished to present the “top” items, the count of
items desired) and the numeric expression (the measure Internet
Sales Amount, in our example) upon which we wished to base our “top”
ranking, to provide the “top count” capability requested by our client.
At appropriate junctures throughout both Part I and Part II of
this article, we discussed the interaction between the various components in
supporting the runtime parameter that the end consumer sees, as well as
discussing the results obtained with the development techniques that we exploited.
»
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Reporting Services Forum.
» 