Next,
let’s add a query parameter for Month.
10.
Once again
within the Query Parameters dialog, in the newly appearing second row of
the table underneath the caption “Specify details of the parameters defined
in the query,” click the leftmost cell (containing the placeholder
“<Enter Parameter>”) to select it, as we did in the first row of the
table for the TimeYear parameter.
11.
Type the
following into the selected cell:
TimeMonth
12.
Within the Dimension
cell, again to the immediate right of the Parameter cell, select Date,
as we did in the first row.
13.
Within the Hierarchy
cell, once more to the immediate right of the Dimension cell, select Date.Fiscal,
as we did in the row above.
14.
Leave the
Multiple values checkbox, appearing in the cell to the immediate right of the Hierarchy
cell just populated, unchecked, once again.
15.
Using the
selector in the Default cell (the rightmost cell in the row within which
we have been working), expand the All Periods entry by clicking the “+”
sign to its immediate left.
16.
Expand the FY
2005 member.
17.
Expand the H1
FY 2005 member that appears underneath the expanded FY 2005 member.
18.
Expand the Q1
FY 2005 member that appears underneath the expanded H1 FY 2005 member.
19.
Select the July
2004 member, as depicted
in Illustration 34.
Illustration 34: Select July
2004 as the Default ...
20.
Click OK
to accept our selection, and to dismiss the Default selector.
Next, we
will add a query parameter for the span of desired top count (for
purposes of our illustration, we will select three items – keeping in
mind that the default here, in effect, serves only to provide a value to
substitute for the associated query parameter value at run time (as well
as for the associated report parameter default). This parameter
will have some different settings than the more “typical” settings above –
including blanks in a couple of places (a fact that has not, at the time of
this writing, been well documented ...).
21.
Once again
within the Query Parameters dialog, in the newly appearing third row of
the table underneath the caption “Specify details of the parameters defined
in the query,” click the leftmost cell (containing the placeholder
“<Enter Parameter>”) to select it, as we did in the rows of the table
for the two parameters we have already added above.
22.
Type the
following into the selected cell:
TopCountSpan
23.
Leave the Dimension
cell (again to the immediate right of the Parameter cell) blank.
24.
Leave the Hierarchy
cell, (again, to the immediate right of the Dimension cell) blank.
25.
Leave the
Multiple values checkbox, appearing in the cell to the immediate right of the Hierarchy
cell just populated, unchecked, as we have done in the TimeYear and TimeMonth
rows above.
26.
Click within
the Default cell (the rightmost cell in the row, once again), to place
the cursor there.
27.
Type the
following into the selected cell:
3
as shown
in Illustration 35.
Illustration 35: Input “3” as the Default Span for
Look Back ...
28.
Click the Parameter
cell on the same row (where we typed “LookBackSpan” earlier), once
again, to “set” the “3” within the Default cell.
The third
row, within which we have been working in the Query Parameters dialog,
appears as depicted in
Illustration 36.
Illustration 36: The
Query Parameters Dialog with Our Input ...
29.
Click OK
to accept our selection, and to dismiss the Query Parameters dialog.
We are
returned to the Data tab, where we will make some modifications to the query
syntax in the section that follows. Before doing so, let’s quickly confirm
some events that have transpired behind the scenes, as a part of our creating
the three query parameters in the preceding steps of this section.
30.
Select Report
-> Report Parameters ..., from the main menu, as shown in Illustration 37.
Illustration 37: Select
Report -->
Report Parameters ...
We note
the appearance of the three new query parameters within the Parameters
pane of the Report Parameters dialog, which appears next, as depicted in Illustration 38.
Illustration 38: The
Report Parameters Dialog with New Entries ...
31.
Click OK
to dismiss the Report Parameters dialog for now.
32.
Click the
downward pointing arrow on the Dataset selector (currently displaying
the ProductData selection) within the Data tab.
We note
that only the ProductData selection appears within the selector, as shown
in Illustration 39.
Illustration 39: The
Single Existing Dataset within the Selector ...
33.
Click the Layout
tab in the Report Designer.
34.
Click the Data
tab immediately to return to our former position.
35.
Click the
downward pointing arrow on the Dataset selector, once again.
We note
that the TimeYear and TimeMonth datasets (automatically created when
we moved to the Layout tab) join the ProductData selection within
the selector, as depicted in Illustration 40.
Illustration 40: The
Newly Created Datasets Also Appear within the Selector ...
The
datasets we see have been automatically created to support parameters that we
have defined with simple datasets that we may or may not choose to use “out of
the box.” (See the various articles I have published surrounding parameters
in Reporting Services 2005, within my MSSQL Server Reporting Services series at Database
Journal.)
NOTE: it is important to remember that, although the report
parameter and its underlying Dataset are created automatically
when we create the underlying query parameters within the Query
Parameters dialog of the MDX query designer (Query mode), removal of
a corresponding, supporting row from the Query Parameters dialog will
not produce an opposite effect. The Report Parameter and the Dataset
will remain until they are manually removed. Moreover, the disablement of parameterization
in this manner from within the Query Parameters dialog, followed by re-creation
of the affected row will, unless we intervene before we recreate the Parameter,
result in the creation of two Report Parameters.
One might
wonder why the third query parameter we created, TopCountSpan, did not
trigger the creation of a third dataset. Because the simple parameter is based
upon a mere number, in this case, and not upon a dimensional structure, no
basis exists within the “auto create” capability upon which to build the
dataset. We will, of course, be able to manage parameter picklist
support via another avenue, as we shall see in a later section in Part II. Our first step in Part II, will be to overview the two
datasets that were created, and ensure their adequacy to support the associated
date-related report parameters in meeting the business requirements of
our client colleagues. We will then conclude by working with underlying
dataset queries to enforce cascading, and to support the parameterized “number
of top items” selection capability that is the primary focus of this article.
NOTE: Please consider saving the .rdl file we have created to this point
for use in the article that follows, so as
to avoid the need to repeat the preparation process we have
undertaken above.
36.
Experiment
further with the report, if desired.
37.
When finished
with the report, click the Layout tab.
38.
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 the next article
within our series.
39.
Select File
-> Exit to leave the design environment, when ready.
Conclusion ...
In this article, we began another extended examination of Parameters
in Reporting Services 2005. This time, we set out to obtain some
hands-on exposure to parameterizing an MDX function, TopCount(), within
a preexisting sample OLAP report. Beginning with the general concepts, we
continued into a practice session where we set up a scenario within which we would
work with a basic OLAP report, to expose the steps involved. In examining the rudiments of
specific function parameterization within an OLAP report containing a matrix
data region, we performed
a brief overview of the MDX TopCount() function, which we stated that we
would use to support a stated reporting need of a hypothetical client. We discussed the parameterization of MDX functions
in general, and the TopCount() function specifically.
We
next prepared a copy of a sample report sourced from Analysis Services, containing a Matrix data
region, with which to perform our practice exercise. We then added the required query parameters
to support date and function parameterization, which we
stated we would complete in
Part II of this article. Throughout our various preparation
and other steps, we discussed
the interaction of the various components in supporting the runtime parameters
that the end consumer sees, as well as touching upon the results obtained
within 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.
» 
Comment and Contribute
