BI Architect Bill Pearson continues
his hands-on demonstration of an approach to the parameterization of the
highly useful LastPeriods() function.
About the Series ...
This
article is a member of the series MSSQL
Server Reporting Services. The series is designed to introduce MSSQL Server
Reporting Services (Reporting Services), presenting an overview of
its features, with tips and techniques for real-world use. For more
information on the series in general, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting. For the software components, samples and tools
needed to complete the hands-on portion of this article, see BlackBelt
Administration: Linked Reports in Report Manager, another article within this
series.
About the Mastering OLAP Reporting
Articles ...
One of
the first things that become clear to early adopters of Reporting Services
is that the knowledgebase for Analysis Services reporting with this
tool is, to say the least, sparse. As I stated in my article, Mastering
OLAP Reporting: Cascading Prompts (where I treated the subject of cascading
parameters for Reporting Services 2000), the purpose of the Mastering
OLAP Reporting subset of my Reporting Services series is to focus on techniques for using Reporting
Services for OLAP reporting. In many cases, which I try to outline in my articles at
appropriate junctures, the functionality of well-established, but expensive,
solutions, such as Cognos PowerPlay, can be met in most respects by Reporting
Services at a tiny fraction of the cost.
The
vacuum of documentation in this arena, even taking into consideration the
release of several books surrounding Reporting Services 2005 in
recent months, continues to represent a serious undersell of Reporting
Services, from an OLAP reporting perspective. I hope to contribute
to making this arena more accessible for everyone, and to share my
implementation and conversion experiences as the series evolves. In the
meantime, we can rest assured that the OLAP potential in Reporting
Services will contribute significantly to the inevitable commoditization of
business intelligence, via the integrated Microsoft BI solution.
For
more information about the
Mastering OLAP Reporting articles, see the section entitled About the Mastering OLAP Reporting Articles in my article Ad Hoc
TopCount and BottomCount Parameters.
Overview
As I
noted in Mastering
OLAP Reports: Parameterizing Number of Look Back Periods with the MDX
LastPeriods() Function, Part I, we have focused, in several recent articles of this series,
upon various aspects of parameterization within the Reporting
Services environment. In some cases we have supported parameterization from
structures completely contained within Reporting Services, and in others
we have created parameter (predominantly picklist) support
from within other layers of the integrated Microsoft business intelligence
solution. As many of us are aware, enterprise reporting applications
typically allow for parameterization (via what are sometimes known as
prompts or parameter prompts) to enable information consumers to quickly
find the information they need from a report. These parameters, whose
values are physically passed to an axis specification or a slicer in the dataset
query, often act to put filters into place on the fly; the filters
are thus enacted when the consumer types or selects a value, or a series of
values, at run time.
We emphasized, in Part I, that, because they allow information consumers to assume a
role in guiding the delivery of information and add a self-serve component
to the reporting experience - parameterization in general is a popular
topic in the forums and newsgroups of most enterprise reporting applications.
My continued application of the underlying concepts over many years within Cognos,
Crystal, Business Objects, MicroStrategy,
and a host of other, more specialized applications, has given me a great
appreciation for the opportunities that exist in the business environment for
effective parameterization. Whether the reports are to be printed,
displayed on screen, or any of the other options for production / deployment, it
is challenging to overstate the value that parameterization can add in
making the selection and delivery of enterprise data more focused and
consumer-friendly.
While
I have extended parameterization concepts into many arenas, none have captured
my attention as much as their deployment within the integrated Analysis
Services / Reporting Services pairing. These applications work
together to provide business intelligence in a way that is powerful and highly
flexible. As I have noted in
Part I, I often advise clients who are
attempting to locate a consultant to implement the integrated Microsoft BI
solution (composed of MSSQL Server, MSSQL Server Analysis Services,
and Reporting Services) to seek a multidimensional architect - a
consultant who has a good working knowledge of each of the components, and who
can determine where, among three or more possible logical layers, to
place which components so as to optimize the system as a whole.
NOTE: For details surrounding hands-on
approaches (as you will see, they are Legion) to the mechanics behind
supporting parameterization, (including the generation of picklists)
in Reporting Services, see these articles in
MSSQL Server
Reporting Services series
here at Database Journal:
Throughout these articles, as well as elsewhere, we have
generated simple lists to provide virtually all we need to support parameterization
within Reporting Services and other enterprise reporting applications.
In this article, we will continue to pursue the objective we stated in Part I: We will perform a more detailed
examination of the mechanics behind parameterizing an MDX function
or more precisely, for parameterizing the variable argument within such
a function. We will illustrate the process using the popular LastPeriods()
function, which we introduced in MDX
Time Series Functions, Part I: PeriodsToDate() and Kindred Functions (a
member of my MDX Essentials
series at Database Journal), but the same logic can be
extrapolated to many other similar MDX functions, as I have noted, and will
continue to note, in my articles.
In this article, we will continue to get hands-on exposure to
parameterizing LastPeriods() within the sample OLAP report that
we cloned in Part I. Beginning with the general
concepts, in Part I, we began our practice session by
setting up a scenario within which to work with a basic OLAP report in exposing
the steps involved in parameterizing the LastPeriods() function
specifically. We began by opening
the sample Report Server project, AdventureWorks Sample Reports,
and ascertaining connectivity of its shared Analysis Services data source.
Next, we created a clone of an existing
sample report, containing a matrix data region, with which to perform
our practice exercise.
Once we had made structural modifications to the clone report, to
further prepare for our practice exercise session, we performed a brief
overview of the MDX LastPeriods() function, discussing details of the
use we intended for the function to perform in support of the stated reporting
needs of a hypothetical client, as well as touching upon general concepts
surrounding the parameterization of MDX functions in general, and the LastPeriods()
function specifically.
In this, the second part of our article, we will:
-
Ensure the
adequacy of the datasets, created automatically when we added query
parameters as a last step in Part I, to support report
parameters and meet business requirements;
-
Add syntax to
the Month dataset query to enforce cascading, based upon
the selection made for the Year parameter by an information
consumer at runtime;
-
Leverage the
MDX LastPeriods() function, containing index and Month parameter
placeholders;
-
Discuss the
interaction of the various components in supporting the runtime parameter
that the end consumer sees;
-
Discuss the
results obtained with the development techniques that we exploit.
Parameterizing the LastPeriods() Function (continued ...)
Throughout many past
articles of the MSSQL Server Reporting Services series, we have leveraged
parameters within the context of MDX queries. To reiterate my introduction in Mastering OLAP Reports: Parameterizing Number of
Look Back Periods with the MDX LastPeriods() Function, Part I, Reporting Services 2000, initially intended
as a component of the MSSQL Server 2005 BI Release, was released to
market early, as many of us are aware, with resulting limitations in some of
its capabilities. One of the challenges that faced many of us was the absence
of an MDX Editor: while those of us who were comfortable with MDX
syntax were not impaired significantly (although we had to deal with
circumstances that accompanied parameterization in Reporting Services
2000, such as the need to string our queries for passage from Reporting
Services to the Analysis Server, and the inability to test
generate our Datasets, once we had parameters in place within
the MDX queries), those who were already challenged with MDX as a language almost
certainly found no amusement in dealing with the added mechanics.
As we discussed in Part I, Reporting Services 2005 introduced the MDX Query Builder,
a tool that satisfies, due to its on-off flexibility, most practitioners
who are comfortable writing direct MDX queries, as well as the MDX- challenged
among those authors and developers, who need to work with OLAP data sources.
This enhancement, unsurprisingly, changed the way that many of us had become
accustomed to writing the underlying MDX queries for our Analysis Services
reports.
Objective and Business Scenario
In Part I, we stated our objective to perform an extended examination of parameterization
within a copy of an existing
sample Reporting
Services 2005 report that we created
for this purpose. Our focus, we stated, was and is to parameterize a specific MDX function, LastPeriods(), to demonstrate the process behind supporting parameterization
of an index value we can supply in this and similar functions at
runtime. We stated that we would concentrate, in this two-part article, upon
the passage of our selection of a value to the function, more than the
details of setting up parameters and so forth in Reporting Services.
(We cover the latter in detail in various articles I have written for that
specific purpose), although we do touch upon most aspects of setting up parameters
in general.
In Part I, we reviewed the basics surrounding
the LastPeriods() function, and then we began to prepare to perform an exercise within
which we would put the function to work to meet the stated business
requirements of a hypothetical client.
NOTE: To gain the most from completing
the steps of our continuing practice session below, you will have needed to
complete Part I of this article. We will
continue where we left off in the first half, and continue to work with objects
that we created in Part I, as we complete the steps of our
practice session. You must complete the preparation and initial practice
session in Part I before continuing with the
practice session below.
As we
noted in MDX
Time Series Functions, Part III: The LastPeriods() and ParallelPeriod()
Functions, a member of
my MDX
Essentials series, the LastPeriods() function allows
us to meet numerous common business needs, including (and especially) those
that require that we return values from, for example, several periods back,
up to, and including, the specified period. That specified member might
be, for example, a specific quarter for which we would like monthly values over
the range of, say, two quarters back through the current quarter. A calculation
can be driven for a several months activities, as another example, whose range
is determined by the beginning and ending points that result from the index
we provide. (We will work within a simple months scenario in our
practice session, but keep in mind that LastPeriods() can certainly flex
to various levels of a dimensional hierarchy).
I
emphasized in Part
I of this article that I have found
the LastPeriods() function to be highly useful in both statistical and
financial reporting. The capability to specify number of periods to look
back, for example, in patient readings (both aggregated and individual)
can become highly popular among, say, doctors who are tracking the
effectiveness of new treatments and the like, allowing them to extend the
review range over a variable number of months.
We
noted that the values
most likely to be of interest in typical uses of the LastPeriods() function
will often relate to deltas over the given periods. (As an illustration,
the total sales over each of a given group of months - itself the delta of
ending total sales between the point in time that we establish as the specified
member within the function, and the total sales value that existed, in this
example, at the earlier point in the year to which we are looking back, and
which is index 1 months back). LastPeriods() is excellent for
the derivation of total activity for the last [specified number] periods
back, and, thus for a to date cumulative total of sorts. Further power
can be gained, again, in the use of the function with .CurrentMember,
etc. to derive a context sensitive calculation that can flex automatically as
time marches on. Running averages and other derivatives of the accumulated
totals are obvious easy byproducts, as well, given formulation within an
expression that includes LastPeriods().
MDX
provides an excellent means for accumulating these time-ranged activity values
over specified time frames. The LastPeriods() function affords us a
ready means to navigate to the first member of the range for which one endpoint
(the specified member) is defined, and for which the opposite end of the range
is specified by the index. When we combine LastPeriods() with the
grouping capabilities found in the Reporting Services matrix data
region, we can extend its power into the dynamic generation of number of
periods look back that we present within chart and summary reports
reports for which we can dictate the number of periods to look back
at runtime.
Lets
review an illustration I used in Part I
to further clarify
the operation of LastPeriods(). Syntactically, the index and the
specified member are placed within the parentheses to the right of LastPeriods(),
as shown in the following illustration:
LastPeriods(«Index»[, «Member»])
The function returns the set
of members prior to (by «Index» - 1 member-places), and including, «Member». The following simple example
expression:
LastPeriods (11, [Time].[1998].[Q3].[8])
would return the
following set of month-members:
{[Time].[1997].[Q4].[10],
[Time].[1997].[Q4].[11],
[Time].[1997].[Q4].[12]
[Time].[1998].[Q1].[1]
[Time].[1998].[Q1].[2]
[Time].[1998].[Q1].[3]
[Time].[1998].[Q2].[4]
[Time].[1998].[Q2].[5]
[Time].[1998].[Q2].[6]
[Time].[1998].[Q3].[7]
[Time].[1998].[Q3].[8]}
As we
noted in Part I of this article, and as we shall see in our practice sections below, parameterization
of the index within the LastPeriods() function lies at the heart
of the procedure we shall undertake within our procedural example below. We
will detail the steps within a sample report, which we have cloned to save
time, focusing largely upon setup of the parameter mechanism within the primary
report dataset, together with its linkage to the corresponding Report
Parameter and elsewhere.
Parameterizing LastPeriods()
I
often parameterize the LastPeriods() function within a Reporting
Services application I am developing as part of a larger implementation of
the integrated Microsoft Business Intelligence solution for a given client.
While this is only a tiny part of the overall structure typically assembled for
a combined OLAP and relational reporting system, it will provide an interesting
glimpse of the much larger population of opportunities that I find daily in
working with these powerful analysis and reporting tools.
In the
following sections, we will perform the steps required to add parameterized number
of periods of look back to an OLAP report. To provide a report upon which
to practice our exercises, we began in Part
I with the Sales
Reason Comparisons sample report that, among other samples, accompanies the
installation of Reporting Services 2005, and which is based upon the
ubiquitous Adventure Works cube, contained within the Analysis
Services database named Adventure Works DW, that comes along with an
installation of Analysis Services 2005.
To
restate the scenario we posed in Part I, we will assume that information
consumers within the Marketing office of the Adventure Works
organization have expressed the need for modifications of the existing Sales
Reason Comparisons report. The existing capabilities in the report meet
most of their analysis needs, but recent requirements for a more flexible
presentation of the data has resulted in our being called to assist with
creating a custom report that meets an immediate, specialized need in a
user-friendly manner.
In discussing their
requirements in detail, representatives of the Marketing department have stated
that ongoing analysis operations would be dramatically enhanced if they could
simply specify, at report run time, the number of months displayed. That is,
instead of seeing the fixed number of months (thirty-seven total
months data is available, as we shall see), our client colleagues told us
that they would like to be able to specify the number of months for the
report to look back from the most recent operating month, July 2004.
Our colleagues informed
us, again as we summarized in Part I, that the original report needed
some basic changes (removal of a filter that limited the data presented
to a couple of operating years, so as to open all available periods within
the cube for reporting; removal of a pre-existing report parameter and
its underlying support objects; etc.), which we accomplished within the
preparation section of Part I. We also streamlined and
regrouped the report in a couple of ways, to allow grouping of fewer measure
totals, by months instead of by Sales Territories. Grouping the two
remaining measures of interest by month, we noted, would allow us
to present both measures for a given month or a range of
months, depending upon our selection of number of months look back
at runtime, which we have discussed earlier. Finally, we added year and
month report parameters, to allow information consumers to run the
report as of any month not just the latest month for which values are
stored within the Analysis Services database.
The modifications we
made in Part I, and that we will continue to make in this half of the
article, will result in a multi-purpose report, the months presented by
which can be dictated on the fly by the easy, ad hoc input of varying ranges
of months to look back, as well as the year / month from which to look
back, allowing the analysts to rapidly meet differing conditions and
analysis needs. As is often the case with the addition of parameterization,
the organization will ultimately be able to accomplish more with fewer reports.
For more information
on the LastPeriods() function, as well as for the original specification
of the needs as expressed by our client colleagues, see Mastering OLAP Reports: Parameterizing Number of
Look Back Periods with the MDX LastPeriods() Function, Part I.
Practice continued ...
Recall
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.
We stated
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
that follow.
Ensure
Adequacy of Automatically Created Datasets to Support Report Parameters and Meet
Business Requirements
Lets
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
report parameters.
When we
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.
1.
Select Report
-> Report Parameters from the main menu, once again.
We
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.
2.
Ensure that
the TimeYear parameter is selected within the Parameters pane on
the left side of the Report Parameters dialog.
Another
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
Parameter.
Finally,
we note that the MDX qualified name for Fiscal Year 2005 ([Date].[Fiscal].[Fiscal
Year].&[2005] ), 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.)
3.
Click OK to
dismiss the Report Parameters dialog for the present time.
We
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.)
4.
Click the Data
tab once again.
5.
Click the
downward selector arrow on the right side of the Dataset selector.
6.
Select the new
TimeYear dataset that appears, as shown in
Illustration 1.
Illustration 1: Select
the New Dataset
The 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.
7.
Click the Execute
Query button in the toolbar, as depicted in Illustration 2.
Illustration 2: Execute
the Query ...
The
query executes, and the data grid becomes populated, as shown in Illustration 3.
Illustration 3: The
Dataset Created for Year Parameter Picklist Support
An
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:
-
ParameterCaption
-
ParameterValue
-
ParameterLevel
All three
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.
Having
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.
We can
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:
8.
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 ...
The Dataset
dialog appears, defaulted to the Query tab, as shown in Illustration
5.
Illustration 5: The
Dataset Dialog Appears ...
9.
Click the Filters
tab on the Dataset dialog.
10.
Click the Expression
box in the top row of the table labeled Filters on the Filters
tab, to activate its downward pointing selector arrow.
11.
Using the
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 ...
12.
Ensure that an
equals ( = ) sign appears in the Operator column, to
the right of the Expression column, within the same row of the Filters
table.
13.
Type the
following into the Value column, to the right of the Operator
column, within the same row:
=1
The
Dataset dialog, along with our settings, appears as shown in Illustration
7.
Illustration 7: The
Dataset Dialog, with Our Input
14.
Click OK
to accept our input, and to close this dialog.
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, lets 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,
lets 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.
Work
with Underlying Dataset Queries to Enforce Cascading and Support Parameterized
Look Back Capability
Add
Syntax to the TimeMonth Dataset Query to Enforce Cascading
We
will return to the Data tab, at this point, to make modifications to the
primary dataset query to both properly enable the date-related parameters
for Year and Month, as well as to add and enable the MDX function
that will support the parameterized look back capability within
the report that our client colleagues have requested.
Before we
work specifically with the primary dataset query, however, we will modify
our TimeMonth dataset query to support the cascading parameter
picklist for the Month parameter, the current absence of which we
discussed earlier. As we mentioned before, we seek to enforce cascading
so that the appropriate child members (Months) appear within the Month
parameter picklist, based upon the selection made in the Year
parameter parent level to which they belong.
At this stage,
we face a couple of modifications to the TimeMonth dataset. First, we
must add a Query Parameter within the dataset for TimeYear, and
then we must embed the parameter, via a placeholder, into the syntax.
We shall make these modifications within the steps that follow. We will then
need to make a relatively minor final adjustment to the TimeYear report
parameter, as a reaction to a reset that it performs based upon our
addition of the Query Parameter within the TimeMonth dataset, as
we shall see.
1.
Click the Data
tab once again.
2.
Click the
downward selector arrow on the right side of the Dataset selector.
3.
Select the TimeMonth dataset, if necessary.
The dataset
query appears, presenting the MDX syntax in Query view, as we saw in
earlier steps. Because we are establishing a dependency, based upon the TimeYear
dataset, within the TimeMonth
dataset, we must
establish this dependency within a Query Parameter within the dataset,
just as we did on a larger scale within the primary dataset query earlier.
4.
Click the
Query Parameters
button on the toolbar, as we did earlier.
The Query
Parameters dialog (currently empty) appears.
Here we
add the new variable we are associating, via the query syntax,
with the TimeMonth parameter picklist, which appears at runtime. It is
in this manner that we build in support for cascading of (only) the
appropriate child-months.
5.
In the single
row of the Query Parameters dialog, in the table underneath the caption
Specify details of the parameters defined in the query, click
the leftmost cell (containing the placeholder <Enter Parameter>)
to select it.
6.
Type the
following into the selected cell:
TimeYear
7.
Within the
cell to the immediate right of the Parameter cell just populated,
labeled Dimension, select Date, just as we did within the same
position within the primary dataset query earlier.
8.
Within the
cell to the immediate right of the Dimension cell just populated,
labeled Hierarchy, select Date.Fiscal, as we did within the same
position within the primary dataset query earlier.
9.
Leave the
Multiple values checkbox, appearing in the cell to the immediate right of the Hierarchy
cell just populated, unchecked, once again.
10.
Using the
selector in the Default cell (the rightmost cell in the row within which
we have been working), expand the All Periods entry by clicking the +
sign to its immediate left.
11.
Select the FY
2005 member, as
we did within the same position within the primary dataset query earlier.
12.
Click OK
to accept our selection, and to dismiss the Default selector.
The Query
Parameters dialog appears, with our addition, as shown in Illustration 14.
Illustration 14:
The Query Parameters Dialog, with Our Input ...
13.
Click OK
to accept our new parameter addition, and to close the Query Parameters
dialog.
We are
returned to the Data tab, where we can see the original MDX syntax in
the Query pane. The MDX appears as depicted in Illustration 15.
Illustration 15: The
TimeMonth Dataset Query
All
that remains to enforce a cascading TimeMonth parameter is to insert the
appropriate syntax into the TimeMonth dataset query. Our overall
objective is simply to force a dynamic filter based upon the value of the TimeYear
parameter (which we have already declared within the Query Parameters
dialog). We will do this in the steps that follow:
14.
Remove the
following from the existing dataset query:
FROM [Adventure Works]
15.
Replace the
newly removed clause with the following syntax (simply add it to the end of the
query):
FROM
(
SELECT
(
STRTOSET(@TimeYear, CONSTRAINED) ) ON AXIS(0)
FROM
[Adventure Works]
)
The entire query should
appear as shown in Illustration
16 (I have expanded the additional syntax to promote easier understanding).
Illustration 16: Modified
TimeMonth Dataset Query
16. Click the Execute Query (!)
button in the toolbar.
The
query executes, and a data grid becomes populated, as partially depicted in Illustration 17.
Illustration 17: The
Dataset with Our Modifications (Partial View)
We can
scroll through the results dataset and easily see that only those Months
that are the children of FY2005 (the default TimeYear
parameter value) appear. This will support our desired objective,
selections within the Month picklist at runtime that are associated with
(and only those that are associated with) the year that is
selected at runtime within the parent TimeYear parameter. We will
ascertain the effectiveness of our handiwork via the performance of a simple Preview
later, once we have completed the syntax modification procedures in the next
subsection, where we make final syntax modifications - this time to the primary
dataset - to embed the look back capabilities requested by our
client colleagues, based upon the selections they make at run time for year,
month, and number of months to look back.
Leverage
the MDX LastPeriods() Function, Containing the Year and Month Parameter
Placeholders
Our last
procedure will comprise turning it all on by another syntax modification
this time within the query supporting the primary dataset, ProductData.
Recall that we have already added query parameters for TimeYear, TimeMonth
and LookBackSpan into the ProductData dataset. At this point, we
have only to embed the parameters into the query syntax, which we
will do in the steps that follow.
1.
From our
present position on the Data tab, click the downward selector arrow on
the right side of the Dataset selector, once again.
2.
Select the ProductData dataset,
at the top of the list of datasets.
The dataset query appears,
presenting the MDX syntax in Query view, as shown in Illustration 18.
Illustration 18: The Primary
Dataset (ProductData) Query
3.
Carefully
remove the following syntax from the query:
([Date].[Fiscal].[Month].ALLMEMBERS
shown circled in the portion of the query depicted in Illustration
19.
Illustration 9: Remove
the Daterelated Portion of the (Partially Shown) Query
4.
Leaving the
cursor in place, type the following substitute syntax into the query:
(LASTPERIODS(@LookBackSpan, STRTOMEMBER(@TimeMonth,
CONSTRAINED))
So that the complete query is as follows (cut and
paste it into the place of the existing query, if that is easier):
SELECT NON EMPTY { [Measures].[Internet
Order Quantity], [Measures].[Internet Sales Amount] } ON COLUMNS, NON EMPTY { (LASTPERIODS(@LookBackSpan,
STRTOMEMBER(@TimeMonth, CONSTRAINED)) * [Sales Reason].[Sales
Reason].[Sales Reason].ALLMEMBERS * [Sales Territory].[Sales Territory
Group].[Sales Territory Group].ALLMEMBERS ) } DIMENSION PROPERTIES
MEMBER_CAPTION, MEMBER_UNIQUE_NAME, [Sales Reason].[Sales Reason].[Sales
Reason].KEY, [Sales Territory].[Sales Territory Group].[Sales Territory
Group].KEY ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, BACK_COLOR,
FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Note: The
replacement syntax is in red in the code above.
The dataset query appears,
with our substitution (circled, once again), within the Query pane as partially
shown in Illustration 20.
Illustration 20: The Dataset
with Our Modifications (Partial View)
5.
Click the Execute
Query button on the toolbar, once again, to run the newly modified query.
The query
executes, and the data grid again becomes populated, as partially
depicted in Illustration
21.
Illustration 21: The Modified
ProductData Dataset (Partial View)
An
examination of the query and the data it returns reveals the presence of data
for six months (February 2004 through July 2004) which
equates to the default look back value we have supplied within the query
parameter we instituted for LookBackSpan earlier. From the
perspective of the Data tab, therefore, our query appears to be
performing as expected. We will verify performance of this and other items
with which we have worked in the next section, where we will conclude our
practice exercise.
Verification:
Preview the Report and Inspect the Effectiveness of Our Modifications and
Enhancements
Lets
preview the report to inspect the results of our handiwork.
1.
Click the Preview
tab.
DBJ_OLAP_Report.rdl initializes, and the Year
prompt becomes enabled.
We
note that the Year parameter is once again displaying the ParameterCaptionIndented
Label field - something we had already changed to the standard ParameterCaption
Label field within the Report Parameters dialog for the respective
parameter TimeYear earlier. It is, perhaps, useful to note, at this
stage, that the Label field has reset itself to this, the Reporting
Services default, when we declared the TimeYear query parameter
within the TimeMonth dataset, as a part of making the latter cascade
properly, based upon the selection we made within the TimeYear picklist.
To restore the standard ParameterCaption Label field, we can simply
return to the Report Parameters dialog for the TimeYear parameter,
and reselect ParameterCaption via the Label field
selector, as shown in Illustration 22.
Illustration 22: Adjusting
the Label Field, which has Reset to Default ... (Optional)
2.
Click the Preview
tab, once again, if you have made the change noted above.
3.
Click the
downward pointing arrow on the right side of the Month parameter
selector.
We
note that only two months appear within the Month parameter
picklist - the only populated member months of FY 2005 within the Adventure
Works sample cube. Let's try changing the Year at this point to
demonstrate further the fact the cascading is working effectively:
4.
Select FY
2004 within the Year parameter picklist.
Once
we make a selection within the Year dropdown selector, the next parameter
within the cascading chain, Month, becomes enabled, once
again.
5.
Click the
downward selector button that appears on the right of the Month
parameter, as we did before, to expose the parameter picklist, as
depicted in Illustration 23.
Illustration 23: The
Parameter Picklist Cascades to Display the Member Months of FY 2004
We
note that twelve months appear within the Month parameter
picklist - the only populated member months of FY 2004 within the
Adventure Works sample cube. We can easily see that the Month
parameter picklist cascades appropriately, based upon the selection we make
in the Year parameter picklist.
6.
Select June
2004 within the Month parameter picklist (the bottom entry appearing
within the selector).
Let's
verify the operation of the look back capability we have added via the
No. Months to Report parameter. As we can see, the default of 6
appears in the selector.
7.
Leaving the No.
Months to Report parameter selection at default (6),
click the View Report button in the upper right corner of the Preview
tab, to execute the report with current settings.
The
report executes, and displays its output within six columns, ranging between June
2004 and January 2004, as shown in Illustration 24.
Illustration 24: The
Report Displays a Look Back Span of Six Months
Let's
perform another quick test of the parameterized look back capability.
8.
Click the
downward pointing arrow to the immediate right of the No. Months to Report parameter
selector.
9.
Select the number
3 within the No. Months to Report parameter
picklist.
10.
Click the View
Report button in the upper right corner of the Preview tab, to
execute the report with current settings.
The
report executes, displaying its output, this time, within three columns,
ranging between June 2004 and April 2004, as depicted in Illustration
25.
Illustration 25: The
Report Displays a Look Back Span of Three Months
We therefore
see that the look back parameter we have put into place, No. Months
to Report, accomplishes the intended ends, and enables us to meet the business
need expressed by the information consumers. More generally, we can easily see
that the Reporting Services 2005 environment, with its graphical design
environment, supports easy and flexible design of innovative parameters,
which can be based upon a sizable inventory of MDX functions and
operators, among other options. Further, we have seen how we can design parameters
to cascade, and to otherwise interact in an intuitive, user-friendly
fashion. While we might have gone significantly further in designing function-based
defaults for cascading parameter picklists, or even more elaborate,
dynamically adjusting look back ranges, based upon dates we
selected, and a host of other possibilities, we assure our client colleagues
that, once they understand the basics, we can certainly help them to further enjoy
the myriad more advanced capabilities that are exposed within the current
version of Reporting Services.
11.
Experiment
further with the report, if desired.
12.
Select File
-> Save All to save our work to this point.
13.
Select File
-> Exit to leave the design environment,
when ready.
Conclusion ...
In this article, we concluded another extended examination, which
we began in Mastering OLAP Reports:
Parameterizing Number of Look Back Periods with the MDX LastPeriods()
Function, Part I,
of parameters in Reporting Services 2005. We continued toward our
primary objective of getting hands-on exposure to parameterizing LastPeriods()
within the sample OLAP report that we cloned in the first half of the article,
where we began our practice session by setting up a scenario within which to
work with a basic OLAP report in exposing the steps involved.
We picked up, in this article, with the clone of an existing sample
report, containing a matrix data region, which we had created, and structurally
modified further for our practice session, in Part I. We also briefly recounted an overview of the MDX LastPeriods()
function we had performed in the first half of this article, discussing details
of the use we intended for the function to support the stated reporting needs
of a hypothetical client. In so doing, we touched upon general concepts
surrounding the parameterization of MDX functions in general, and the LastPeriods()
function specifically.
In this,
the second half of our article, we continued our practice session by reviewing
and ensuring the adequacy of the datasets (automatically created when we
added the required query parameters to support date and function
parameterization in Part I) to support report parameters and
meet the stated business requirements. We next added syntax to the Month
dataset query to enforce cascading, based upon the selection made
for the Year parameter by an information consumer at runtime.
Finally, we leveraged the MDX LastPeriods() function, containing index
and Month parameter placeholders, to provide the look back
capability requested by our client. At appropriate junctures throughout both Part I and Part II of this article, we discussed the
interaction between the various components in supporting the runtime parameter
that the end consumer sees, as well as discussing the results obtained with the
development techniques that we exploited.
»
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Reporting Services Forum.