Reference
Analysis Services Support within Report Parameters
We will
return to each of the three date-related Report Parameters at this
juncture, where we will reference the supporting dataset we created in the
previous subsection, within the respective Dataset and Value fields.
This will align the dataset, which is itself aligned with our newly created
support objects in Analysis Services, with the Report Parameters
themselves. The ultimate objective, to provide intuitive, useful parameter
defaults (“last activity periods for the Product Categories
selected” within the Analysis Services data source, per our client
colleagues) will be accomplished at the completion of this step.
1.
From the main
menu, select Report ->Report
Parameters, as shown
in Illustration 31.
Illustration 31: Select
Report ->Report Parameters
The Report
Parameters dialog opens.
2.
Within the Parameters
list box, on the left side of the dialog, click DateCalendarYear.
The
Properties, Available values, and Default values
settings for the selected Report Parameter appear.
3.
Within the Properties
section on the right side of the dialog, within the Default values section, underneath
the Available values section, ensure that the From query radio
button is selected (click it to fill in the button).
4.
Select AS_DateParamsDefault_Support
in the Dataset selector, as depicted in Illustration 32.
Illustration 32: Select
AS_DateParamsDefault_Support as the Dataset ...
5.
Underneath the
Dataset selector, select the following in the Value field
selector:
Year_Param_Default_Value
The
Default values settings for the DateCalendarYear Report Parameter
appear, with our modifications, as shown in Illustration 33.
Illustration 33:
DateCalendarYear Report Parameter Settings, with Modifications
6.
Within the Parameters
list box, as before, click DateCalendarQuarterofYear.
The
Properties, Available values, and Default values
settings for the selected Report Parameter appear.
7.
Within the Properties
section on the right side of the dialog, within the Default values section, underneath
the Available values section, ensure that the From query radio
button is selected, as we did for the previous Report Parameter.
8.
Select AS_DateParamsDefault_Support
in the Dataset selector, as we did earlier.
9.
Underneath the
Dataset selector, select the following in the Value field
selector:
Qtr_Param_Default_Value
The
Default values settings for the DateCalendarQuarterofYear Report
Parameter appear, with our modifications, as depicted in Illustration 34.
Illustration 34: DateCalendarQuarterofYear
Report Parameter Settings, with Modifications
10.
Within the Parameters
list box, as before, click DateMonthofYear.
The
Properties, Available values, and Default values
settings for the selected Report Parameter appear.
11.
Within the Properties
section on the right side of the dialog, within the Default values section, underneath
the Available values section, ensure that the From query radio
button is selected, once more.
12.
Select AS_DateParamsDefault_Support
in the Dataset selector, as we did in the last Report Parameter.
13.
Underneath the
Dataset selector, select the following in the Value field
selector:
Month_Param_Default_Value
The
Default values settings for the DateMonthofYear Report Parameter
appear, with our modifications, as shown in Illustration 35.
Illustration 35: DateMonthofYear Report Parameter Settings, with Modifications
14.
Click OK
to accept all our Report Parameter modifications, and to dismiss the Report
Parameters dialog.
Having
created the parameter default support structures within Analysis
Services, we added a supporting dataset, based upon those
structures, within our sample report. We then referenced the new dataset
to the respective Report Parameters. We are now ready to verify the
effectiveness of our solution, specifically that the date-related Report
Parameters – whose Available and Default values are now
wholly supported by objects within the Analysis Services layer of our
integrated BI solution – perform as expected at runtime.
Verification:
Preview the Report and Inspect the Effectiveness of Our Solution
Let’s
preview the report to inspect the results of our handiwork.
1.
Click the Preview
tab.
DBJ_OLAP_Report.rdl initializes, and the first prompt
(based upon the pre-existing ProductCategory parameter), becomes
enabled.
2.
Click the
downward pointing arrow on the right side of the Product Category
selector.
3.
Select All
Products, while deselecting both Bikes and Components (the parameter
defaults) within the Product Category parameter selector, as
depicted in Illustration 36.
Illustration 36: Select
All Products in the Product Category Parameter ...
4.
Click outside
the Product Category selector (somewhere in the grey area containing the
parameter selectors – but not upon any of the parameter selections themselves).
We
notice at this stage that the date –related parameters contain
defaults. These defaults represent, indeed, the last Year, Quarter
and Month within the cube containing Internet Sales Amounts for
any of the Product Categories sold by the Adventure Works
organization. (This can be verified via alternate means, such as the Cube
Browser within Analysis Services, among others.) These intelligent defaults
give our consumers a better runtime experience – especially if this is a report
that is executed normally at the end of each month, and so forth. In any
event, the defaults meet the stated requirements of our client colleagues.
5.
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 shown in Illustration
37.
Illustration 37: The Report
Parameters Operate as Expected ...
Our
verification process has demonstrated the effective support of Report
Parameter defaults through the use of objects within the Analysis
Services layer of our BI solution.
6.
Experiment
further with the report, if desired.
7.
When finished
with the report, click the Layout tab.
8.
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 later reference, if
that is useful..
9.
Select File
-> Exit to leave the design environment,
when ready.
Conclusion ...
In
this article, we continued the extended examination of Parameters in Reporting
Services 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, and which we continued in Reporting Services: Customize Automatically Created
Parameter Support Objects and Parameter
Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets, as well as in our most recent article prior
to this one, Support
Parameterization from Analysis Services – Cascading Picklists.
Working
with the basic OLAP report we prepared in Support Parameterization from Analysis Services, we once more began within the scenario we established
within Parameter Support Objects, Pt II:
Support OLAP Parameter Defaults with Datasets, with an objective of meeting the
need of the hypothetical client to support parameter defaults within the
report. This time, however, we exposed the steps involved in offering a simple
means of supporting our solution from within the Analysis Services layer
of the integrated Microsoft BI solution.
In
examining the requested addition of parameter defaults within a sample
OLAP report, we began with a review of the concept of parameterization in general,
and briefly overviewed options for supporting report parameterization among
the three primary layers of the integrated Microsoft business intelligence
solution. We then examined and took up the next requested refinement to our
parameters that we were, once again, to support via objects that we created
within Analysis Services. Specifically, we were asked to meet a
business requirement to generate parameter selection defaults at runtime
within the same OLAP report for which we have established cascading
parameters supported within the Analysis Services layer.
In
pursuing our objective of supporting parameter defaults via objects in
the Analysis Services layer, we prepared for our practice session by reopening the sample Report Server project, AdventureWorks
Sample Reports, and accessing the existing sample report we prepared in Support Parameterization from Analysis
Services,
with which we have been working through our most recent article prior to this
one, Support
Parameterization from Analysis Services – Cascading Picklists. We next generally discussed the
utility of establishing default parameters within a report to make it
more user-friendly and effective.
We
then discussed and implemented an approach, from within Analysis Services,
to meet the need of our hypothetical client to present simple parameter
picklist defaults that represent the last period of data entry in
our cube. We then added
a single dataset into our report to incorporate parameter default support
for all our date-related parameters. Moreover, we overviewed, at appropriate junctures, how the various
components of the default support solution we proposed are tied together,
as a part of a hands-on practice session where we create and align the
necessary components to support our parameter defaults. Finally, we previewed the report to observe
the effectiveness of our solution in runtime action.
»
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Reporting Services Forum.
» 
Comment and Contribute
