Practice
Having
added the requested date-related parameters in the foregoing Preparation
section, we are ready to begin making modifications to the client’s existing
report to meet the two requirements they have communicated. We will first
eliminate the “All Periods” selection that appears within the picklists
for the new parameters at runtime (the “focus procedure” in this, the first
half of our article). Then, in Part II of this article, we will begin
the construction of example intuitive defaults for the new report parameters,
to provide illustrations of ways our client colleagues might approach similar reporting
needs.
Procedure:
Removing the “All” Selection from Auto-Created Parameter Picklists in Reporting
Services 2005
We
noticed in our earlier “trial run” of the report (where our intent was to
verify the operation of our newly added Date parameters) that an “All
Periods” option appeared atop each picklist. While I have had myriad
clients state that they did not want this option to be available, the fact is
that Reporting Services is delivering exactly what is requested via the
datasets that were automatically created to support the newly added parameters.
The dataset created to support each parameter simply does not filter, by
default, the dimensional levels retrieved from the dataset and presented in the
runtime picklist, which, as we shall see, means that the “All” level is
retrieved among the members of the desired level.
The good
news is that provision has been made, within the dataset, to entrain the level
number as a data column. We can easily perform our own filtering via this
column, as we will do in the following steps.
We arrive
at the Data view, as we did earlier.
2.
Using the Dataset
selector on the left side of the Data tab toolbar, select the DateCalendarYear
dataset, as shown in Illustration
16.
Illustration 16: Select the
DateCalendarYear Dataset ...
The MDX
Query Builder opens, exposing the MDX that was created (to populate the Report
Parameter supporting dataset for the new parameter) as an activity in the
chain of events triggered by our addition of the parameterized filter
for DateCalendarYear in the Preparation section above. Let’s
retrieve the dataset to examine its composition.
3.
Click the Execute
Query button atop the Data tab.
4.
Data populates the Data pane,
and appears similar to that depicted in Illustration 17.
Illustration 17: The
Retrieved Parameter Support Dataset
The
column upon which we will focus is the ParameterLevel column. This
column contains the values of a calculated member, [Measures].[ParameterLevel],
which represents the data retrieved from the AdventureWorks cube by the
following expression: [Date].[Calendar Year].CURRENTMEMBER.LEVEL.ORDINAL.
This expression simply generates a number for the dimensional level at which
the ParameterCaption (the label seen in the picklist by the
information consumer at runtime) and the ParameterValue (the
corresponding MDX value passed by Reporting Services as filter criteria
in generating the data selected in the report at runtime) belong.
NOTE: For more detail surrounding the MDX
.Ordinal function, see MDX
Numeric Functions: The .Ordinal Function. For information on the .Level
function, see various articles throughout my MDX
Essentials series (of which the .Ordinal
function article is also a member) at Database Journal.
To
eliminate the “All Periods” selection from our Year parameter
picklist, we have only to implant a dataset filter at this stage.
5.
Ensuring that
the DateCalendarYear dataset appears within the Dataset selector,
click the ellipses (“...”) button that appears to the selector’s immediate
right, as shown in Illustration 18.
Illustration 18: Opening
the Dataset Dialog
The
multi-tabbed Dataset dialog for the DateCalendarYear dataset
opens.
6.
Click the Filters
tab.
7.
In the Filters
table on the tab, click the top row in the Expression column to activate
the downward-pointing selector.
8.
Select =Fields!ParameterLevel.Value
within the selector, as depicted
in Illustration 19.
Illustration 19: Selecting
the Parameter Level Value Expression ...
9.
Ensure that
the “=” sign occupies the Operator column to the immediate right
(it is populated by default when we select an entry for the Expression
column).
10.
Click the Value
column to the right of the Operator column for the same row, and type
the following into the box:
=1
The Filters
tab of the Dataset dialog appears, with our input, as shown in Illustration 20.
Illustration 20: The
Filters Tab of the Dataset Dialog with Our Input
11.
Click OK,
to accept our modifications, and to close the Dataset dialog.
The
dialog closes, and we return to the MDX Query Builder. At this point,
we should note that, should we execute the query once again, we will see no
difference in the dataset that is returned: the filter is not applied at this
level, although it is enforced when we run the report, as we shall see a few
steps below.
12.
Select the DateCalendarQuarterofYear
dataset within the dropdown Dataset selector.
13.
Click the Execute
Query button atop the Data tab to retrieve the dataset.
The Query
Parameters dialog appears. It is here that we provide a default value for
the Year parameter, to enable the query to execute. In effect, passage
of the Year parameter value, at execution time of the Quarter
dataset, is the mechanism behind the cascading nature of the parameter
picklists.
14.
In the table labeled
Specify details of the parameters defined in the query, click the second
row in the Default column (the row containing DateCalendarYear) to
activate the downward-pointing selector.
15.
Expand All
Periods in the selector picklist, and place a check in the box to the left
of CY 2003 within the selector, as depicted in Illustration 21.
Illustration 21: Select
a Default for the DateCalendarYear Parameter ...
16.
Click OK to
accept the new default.
17.
Click OK
on the Query Parameters dialog to save our input and to dismiss the
dialog.
Data
populates the Data pane, and appears similar to that shown in Illustration 22.
Illustration 22: The
DateCalendarQuarterofYear Dataset Appears ...
Here
again we see that the “All” level value is zero (“0”).
We will, therefore, filter the data supporting the corresponding parameter
picklist to Level 1, as we did for the DateCalendarYear dataset
earlier.
18.
Click the
ellipses (“...”) button that appears to the immediate right of the Dataset
selector, as we did earlier.
The Dataset
dialog for the DateCalendarYear dataset opens.
19.
Click the Filters
tab.
20.
In the Filters
table on the tab, click the top row in the Expression column to activate
the downward-pointing selector, once again.
21.
Select =Fields!ParameterLevel.Value
within the selector, as we did for the DateCalendarYear dataset earlier.
22.
Ensure that
the “=” sign occupies the Operator column to the immediate right,
once again.
23.
Click the Value
column to the right of the Operator column for the same row, and type
the following into the box:
=1
24.
Click OK,
to accept our modifications, and to close the Dataset dialog.
The
dialog closes, and we return to the MDX Query Builder, once again. We
are now ready to perform level filtering on the last of our date-related
datasets, DateMonthofYear.
25.
Select the DateMonthofYear
dataset within he dropdown Dataset selector.
26.
Click the Execute
Query button atop the Data tab to retrieve the dataset.
The Query
Parameters dialog appears, as it did for the DateCalendarQuarterofYear dataset earlier. We will provide a default
value for both the Month and Quarter parameters, at this point,
to enable the query to execute.
27.
In the table labeled
Specify details of the parameters defined in the query, click the second
row in the Default column (the row containing DateCalendarYear) to
activate the downward-pointing selector.
28.
Expand All
Periods in the selector picklist, and place a check in the box to the left
of CY 2003 within the selector, as we did earlier.
29.
Click OK to
accept the new default.
30.
Click the third
row in the Default column (the row containing DateCalendarQuarterofYear)
to activate the downward-pointing selector.
31.
Expand All
Periods in the selector picklist, and place a check in the box to the left
of CY Q3 within the selector, as we did earlier.
32.
Click OK to
accept the new default.
The Query
Parameters dialog appears, with our input, as that depicted in Illustration 23.
Illustration 23: The Query
Parameters Dialog for the DateMonthofYear Dataset with Our Input
33.
Click OK
on the Query Parameters dialog to save our input and to dismiss the
dialog.
Data
populates the Data pane, and appears similar to that shown in Illustration 24.
Illustration 24: The
DateMonthofYear Dataset Appears ...
Once
again we see that the “All” level value is zero (“0”).
We will filter the data supporting the corresponding parameter picklist
to Level 1, as we did for the DateCalendarYear and DateCalendarQuarterofYear
datasets earlier.
34.
Click the
ellipses (“...”) button that appears to the immediate right of the Dataset
selector, once again.
35.
Click the Filters
tab on the Dataset dialog for the DateMonthofYear dataset, which
opens next.
36.
In the Filters
table on the tab, click the top row in the Expression column to activate
the downward-pointing selector.
37.
Select =Fields!ParameterLevel.Value
within the selector, as we did for the DateCalendarYear and DateCalendarQuarterofYear
datasets earlier.
38.
Ensure that
the “=” sign occupies the Operator column to the immediate right,
as we did earlier.
39.
Click the Value
column to the right of the Operator column for the same row, and type
the following into the box, once again:
=1
Having
applied the filters required to the new parameters’ supporting datasets, we are
now ready to verify the effectiveness of our solution, and to ascertain the
removal of the “All Periods” option within the date-related parameters’
picklists at runtime.
» 
Comment and Contribute
