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, LastPeriods(), 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 date within the report (from which to look back, as
it were), rather than forcing them to rely upon current month (or last
month with activity within the cube) as the starting point from which
they could look back. We concluded Part I with the addition of the required query
parameters to support both date and function parameterization.
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
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.
-> Report Parameters 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
Look Back Periods with the MDX LastPeriods() Function, Part I), the presence of three new Report
Parameters, called TimeYear, TimeMonth, and LookBackSpan.
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 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 checklist 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 (among other reasons, 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 shown in Illustration 3.
Illustration 3: The
Dataset Created for Year Parameter Picklist Support
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 references 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 establish manually.
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.