Practice continued ...
that we left off working, in Part I, with the copy we had
created of the Sales Reason Comparisons sample report, after we had accomplished the
preparatory changes noted above. We had progressed the report through the
earliest procedures for adding and parameterizing a single MDX
function, TopCount(), in combination with taking the initial steps
to parameterize the Date dimension itself (both Year and Month),
so as to allow our information consumers to declare an as of period within
the report. We concluded Part I with the addition of
the required query parameters to support both date and function
that we would resume our practice session by reviewing the two datasets that
were created, primarily to ensure their adequacy to support the associated
date-related report parameters in meeting the business requirements of
our client colleagues. We will resume our practice session in the sections
Adequacy of Automatically Created Datasets to Support Report Parameters and
Meet Business Requirements
take a brief look at the two datasets whose automatic creation by Reporting
Services was triggered by our addition of the TimeYear and TimeMonth
query parameters earlier. Our focus here will be to understand their
construction and to ascertain their fitness to support the Year and Month
looked at the Report Parameters that were created within our last
section, we may have noted that each referenced its respectively named,
underlying dataset. Lets return to the Report Parameters dialog
to inspect the resulting relationship further.
Re-open the DBJ_OLAP_Report
with which we worked in the practice session in Part I.
From the Layout
tab, select Report ->
from the main menu, once again.
recall that we had previously removed all Report Parameters, as part of
our preparation for the practice exercise. Yet we note, within the Report
Parameters dialog that has appeared (as we did in Mastering OLAP Reports: Parameterizing Number of
Top Items with the MDX TopCount() Function, Part I), the presence of three new Report
Parameters, called TimeYear, TimeMonth, and TopCountSpan.
These Report Parameters, as we have learned, were created automatically
when we created the associated rows within the Query Parameter dialog on
the Data tab.
the TimeYear parameter is selected within the Parameters pane on
the left side of the Report Parameters dialog.
important observation we might make for the TimeYear (and all
observations we make here for TimeYear apply to the TimeMonth parameter,
within the context of its own supporting dataset, as well) parameter is that
the From query setting within the Available values section of the
Report Parameters dialog is active (the radio button is selected), and
that the setting references a Dataset (and the associated default Value
and Label fields) with the same name as the new Report Parameter
- a Dataset that has also been automatically created. The purpose of
this Dataset is to support the selection picklist for the new Report
we note that the MDX qualified name for Fiscal Year 2005 ([Date].[Fiscal].[Fiscal
Year].& ), is referenced as the Default Value for the Report
Parameter. (Even though we use the MDX qualified name within the Default
Value here, as at any other time in the Report Parameter dialog when
we set a default, the label name - a consumer-friendly name, generally - is
what actually appears within the parameter selector at runtime.)
Click OK to
dismiss the Report Parameters dialog for the present time.
will now return to the Data tab, to examine one of the datasets which
have been automatically created (to populate the parameter picklist at
report run time) the dataset (and dataset query, of course)
specifically created to support the new TimeYear report parameter.
(The columns of the TimeYear dataset are identical to those of the TimeMonth
dataset, and so we will examine only the former at present.)
Click the Data
tab once again.
downward selector arrow on the right side of the Dataset selector.
Select the new
TimeYear dataset that appears, as shown in
Illustration 1: Select
the New Dataset
query appears, presenting the MDX syntax in Query view. This is the query
half of the query / dataset pair that have been automatically
created to generate the parameter picklist (as well as the MDX qualified
name that is passed to Reporting Services, based upon the selection
made at runtime, among other items) to support the new TimeYear report parameter.
Click the Execute
Query button in the toolbar, as depicted in Illustration 2.
Illustration 2: Execute
the Query ...
query executes, and the data grid becomes populated, as partially shown
in Illustration 3.
Illustration 3: The
Dataset Created for Year Parameter Picklist Support (Partial View)
examination of the query and the data it returns reveals that, in
addition to retrieving data directly from the cube, the automatically generated
query creates additional derived columns whose sole purposes are
the support of the report parameter, from whose picklist information
consumers make the selections at runtime, and from which MDX-qualified values
are passed to Reporting Services to generate the associated report. The
derived columns are:
fields are products of calculated members created via MDX syntax (using
the WITH MEMBER keyword combination), within the dataset query.
As we discover within other articles of the series, there are multiple ways to
approach picklist support (For example, I very often construct members
such as the above at the Analysis Services level, to afford central
maintenance and reusability, among other, perhaps less obvious, advantages).
This is simply the mechanism through which Reporting Services
accomplishes support of the automatically generated report parameter.
As we saw earlier, the Value and Label settings within the report
parameter dialog reference the ParameterValue and ParameterCaption
fields, respectively, within this Dataset.
NOTE: While we will discuss MDX functions, expressions,
queries, and related subject matter throughout the MSSQL Server
Reporting Services series, please refer to the articles of my MDX Essentials series, whose member articles are
published monthly here at Database Journal, for detailed information
about MDX, as well as MDX keywords, functions, and other considerations.
As I note
repeatedly throughout the extended examination of parameters that I
perform within this and numerous other articles throughout the MSSQL Server Reporting Services series, it is important to realize
how Reporting Services automatically creates both the report parameter
and the dataset that supports the parameter at runtime. As we
venture into custom parameterization within various practice sessions
elsewhere, I urge my readers to keep in mind how the parts knit together within
this basic context, so as to understand the relationships that we need to
discussed the components of the two automatically created datasets, TimeYear and TimeMonth, we are ready to make a single
modification to each.
If we examine the TimeYear
dataset at this point, we can see that both the ParameterCaption and ParameterValue
derived columns include a great deal more than year-level members: for
example, we see not only a caption for FY 2002 itself, but captions for FY
2002 semesters (such as H1 FY 2002), quarters (such as Q1
FY 2002), months (such as July 2001), and individual dates
(such as July 1, 2001). These occurrences are to be expected, based
upon the MDX syntax used within the system-generated dataset query, but
the varying granularity of the captions and values poses a problem when we consider
that these columns exist to support single-level parameter picklists
within the report at runtime. This means, for example, our TimeYear
dataset needs to provide only the Year (or, more specifically in our
case, Fiscal Year) level members to the ultimate picklist.
filter out all except the Year level members by using the ParameterLevel
column for this purpose. As we can see, the Year level members of the
dataset comprise ParameterLevel 1. All we have to do is filter by this
level, as we shall do in the following steps:
Click the Edit
Selected Dataset (...) button to the right of the Dataset selector
(currently containing TimeYear), as depicted in Illustration 4.
Illustration 4: Click the Edit
Selected Dataset (...) Button ...
dialog appears, defaulted to the Query tab, as shown in Illustration
Illustration 5: The
Dataset Dialog Appears ...
Click the Filters
tab on the Dataset dialog.
Click the Expression
box in the top row of the table labeled Filters on the Filters
tab, to activate its downward pointing selector arrow.
selector, select the =Fields!ParameterLevel.Value option (at the
bottom of the list), as depicted in Illustration 6.
Illustration 6: Select
the =Fields!ParameterLevel.Value Option as the Expression ...
Ensure that an
equals ( = ) sign appears in the Operator column, to
the right of the Expression column, within the same row of the Filters
following into the Value column, to the right of the Operator
column, within the same row:
Dataset dialog, along with our settings, appears as shown in Illustration
Illustration 7: The
Dataset Dialog, with Our Input
to accept our input, and to close this dialog.