Next, we
will add a similar filter to the TimeMonth dataset. The steps are
almost identical as those we took for the TimeYear dataset; the only
material difference is that the ParameterLevel for the Month
level within the Date dimension is identified by the number “4”.
15.
Select TimeMonth,
within the Dataset selector on the Data tab.
16.
Click the Edit
Selected Dataset (“...”) button to the right of the Dataset selector,
as we did for the TimeYear dataset earlier.
The Dataset
dialog appears, once again.
17.
Click the Filters
tab on the Dataset dialog.
18.
Click the Expression
box in the top row of the table labeled Filters on the Filters
tab, to activate its downward pointing selector arrow, as we did for the TimeYear
dataset earlier.
19.
Using the
selector, select the “=Fields!ParameterLevel.Value” option, exactly as
we did for the TimeYear dataset.
20.
Ensure, once
again, that an “equals” (“ = ”) sign appears in the Operator
column, to the right of the Expression column, within the same row of
the Filters table.
21.
Type the
following into the Value column, to the right of the Operator
column, within the same row:
=4
The
Dataset dialog, along with our settings, appears this time as depicted in Illustration
8.
Illustration 8: The
Dataset Dialog, with Our Input
22.
Click OK
to accept our input, and to close the Dataset dialog.
Let's
take a quick look at the picklists for the date-related parameters,
to ascertain the effectiveness of our dataset filters, as well as to
verify their satisfactory operation.
23.
Click the Preview
tab next, to execute the report, once again.
The
report runs, and then displays the drop-down parameters as specified in
the preceding steps, including the defaults requested.
24.
Click the
downward pointing arrow to the right of the Month parameter selector, to
expose the parameter picklist.
The
parameter picklists appears, displaying months as partially shown in Illustration
9.
Illustration 9: The
Month Parameter Picklist Appears...
As we
can see, our filter has been effective in populating the parameter
picklist with months only. (If we click the downward pointing arrow
to the right of the Year parameter selector, we can see that there, too,
the correct parameter level only is displayed.) But while our dataset filters
appear to be effective, we note in the Month parameter picklist that
the months displayed are not restricted to FY 2005. This indicates that
the Month parameter picklist is not cascading appropriately.
As is
probably obvious, we must perform some additional steps before the new Month
parameter picklist cascades correctly. Moreover, even though the parameter
picklists in general appear to function at the Preview tab level, we
will need to perform the steps to make them actually work (to make the “look
back” capability actually restrict the presentation to the number of “look
back” months specified, etc.). These steps lie within the realm of the
underlying dataset query, within which we will be working in the next
section.
Before
we do that, let’s return to the report parameters dialog to conclude
this section with a couple of cosmetic changes.
25.
Click the Data
tab to return to the Data view.
26.
Click Report
--> Report Parameters to re-open the Report
Parameters dialog, as before.
27.
Ensure
selection of the TimeMonth parameter in the Parameters pane of
the dialog.
28.
Within the Prompt
box, found within the Properties section (the third input box from the
top) of the Report Parameters dialog, replace the existing prompt name
(“TimeYear”) with the following:
Year:
29.
Within the Label
field selector, the third setting from the top within the Available
values section of the dialog (about midway down and underneath the Properties
section), select Parameter Caption as presented in Illustration 10.
Illustration 10: Select
Parameter Caption for the Label Field Reference ...
The Report
Parameters dialog, including our setting changes, appears as depicted in Illustration
11.
Illustration 11: Modified
Settings for the Automatically Created Report Parameter
30.
Select the TimeMonth
parameter within the Parameters pane on the left side of the Report
Parameters dialog.
31.
Within the Prompt
box, found within the Properties section of the Report Parameters
dialog, once again, replace the existing prompt name (“TimeMonth”) with the
following:
Month:
32.
Within the Label
field selector, the third setting from the top within the Available
values section of the dialog, select Parameter Caption.
The Report
Parameters dialog, including our setting changes, appears as shown in Illustration
12.
Illustration 12: Settings
for the Second Automatically Created Report Parameter
Finally,
let’s make some settings for the new LookBackSpan parameter. As we noted
in Mastering OLAP Reports: Parameterizing
Number of “Look Back” Periods with the MDX LastPeriods() Function, Part I,
the LookBackSpan parameter will differ from the other two automatically
created parameters, particularly in the fact that it will not reference
a corresponding, automatically created dataset – a situation we both
described and explained in Part I.
Because the parameter picklist will simply offer a range of
numerals as selection options, we will reference those numerals through
direct specification within the Report Parameters dialog (although other
approaches exist to support such a parameter, such as using a manually
created dataset, etc.)
33.
Select the LookBackSpan parameter within the Parameters
pane on the left side of the Report Parameters dialog.
34.
Within the Prompt
box, again within the Properties section of the Report Parameters
dialog, replace the existing prompt name (“LookBackSpan”) with the following:
No.Months to Look Back:
35.
Within the Available
values section of the dialog, ensure that the Non-queried radio
button is selected.
36.
Populate the
table that appears to the right of the Non-queried radio button as
specified in Table 1 below.
|
Label
|
Value
|
|
1
|
1
|
|
2
|
2
|
|
3
|
3
|
|
4
|
4
|
|
5
|
5
|
|
6
|
6
|
|
7
|
7
|
|
8
|
8
|
|
9
|
9
|
|
10
|
10
|
|
11
|
11
|
|
12
|
12
|
|
13
|
13
|
|
|
|
Table 1: Label and Value
Input for the Non-queried Available Values Table
The Report
Parameters dialog, including our setting changes, appears as partially depicted
in Illustration 13.
Illustration 13: Settings
for the Third Automatically Created Report Parameter (Partial View)
37.
Click OK to
accept our changes and to dismiss the Report Parameters dialog.
As we
mentioned earlier, we must perform some additional steps before the new parameters
actually work. These steps will need to be accomplished within the syntax of a
couple of underlying dataset queries, within which we will be working in
the next section.
» 
Comment and Contribute
