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 "look back"
months (for purposes of our illustration, we will select six months
– 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 is not
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:
LookBackSpan
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 within which
we have been working), to place the cursor there.
27.
Type the
following into the selected cell:
6
as shown
in Illustration 35.
Illustration 35: Input “6” 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 “6” 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, LookBackSpan, 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 construct, no
basis exists within the “auto create” capability upon which to build the
dataset. We will, of course, be able to manage this 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 “look back” 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, LastPeriods(), 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 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 LastPeriods() 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 LastPeriods() 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
