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”.
16.
Select TimeMonth,
within the Dataset selector on the Data tab.
17.
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.
18.
Click the Filters
tab on the Dataset dialog.
19.
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.
20.
Using the
selector, select the “=Fields!ParameterLevel.Value” option, exactly as
we did for the TimeYear dataset.
21.
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.
22.
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
23.
Click OK
to accept our input, and to dismiss the Dataset dialog.
Let's
take a quick look at the runtime picklists for the date-related parameters,
to ascertain the effectiveness of our dataset filters, as well as to
verify their satisfactory operation.
24.
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.
25.
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 month
/ date selections and the “top count” capability actually
restrict the presentation to the number of “top” values 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 few cosmetic changes.
26.
Click the Data
tab to return to the Data view.
27.
Click Report
--> Report Parameters to re-open the Report
Parameters dialog, as before.
28.
Ensure
selection of the TimeYear parameter in the Parameters pane of the
dialog.
29.
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:
30.
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 First Automatically Created Report Parameter
31.
Select the TimeMonth
parameter within the Parameters pane on the left side of the Report
Parameters dialog.
32.
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:
33.
Within the Label
field selector, the third setting from the top within the Available
values section of the dialog, select Parameter Caption, as we did for the TimeYear
report parameter.
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 “Top” Items
with the MDX TopCount() Function, Part I, the TopCountSpan 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.)
34.
Select the TopCountSpan parameter within the Parameters
pane on the left side of the Report Parameters dialog.
35.
Within the Prompt
box, again within the Properties section of the Report Parameters
dialog, replace the existing prompt name (“TopCountSpan”) with the following:
No. Top Items:
36.
Within the Available
values section of the dialog, ensure that the Non-queried radio
button is selected.
37.
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
|
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)
38.
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.
IE 7
Firefox 2.0