BI Architect Bill Pearson continues
his hands-on demonstration of an approach to the parameterization of the highly
useful TopCount() 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 Top Items with the MDX TopCount()
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
my 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 Mastering
OLAP Reports: Parameterizing Number of Top Items with the MDX TopCount()
Function, 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 TopCount() function, which we introduced in Basic
Set Functions: The TopCount() Function, Parts I and
II
(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 TopCount() within the sample OLAP report that we
cloned in Part
I. After
introducing 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 TopCount()
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 TopCount() 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 TopCount()
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 TopCount() function, containing count 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 TopCount() 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 Top Items with the MDX
TopCount() 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. 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 however, 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 already 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, TopCount(),
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 of the typical aspects of setting up parameters.
In Part I, we reviewed the basics surrounding
the TopCount() 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 Basic Set Functions: The TopCount() Function, Parts I and II, both members of my Database
Journal MDX Essentials series, the TopCount() function allows
us to meet numerous common business needs, including (and especially) those
that require that we return the top n members (such as cities or stores),
usually for a given time frame (which might actually be all time), based upon
a valid measure (such as total profits, total margin, etc.).
An
example might be the top ten Warehouse Cities for operating year 1997,
based upon total Warehouse Profit for each city.
I emphasized
in Part I of this article that I have found the TopCount() function
to be highly useful in both statistical and financial reporting. The
capability to specify the top [number of values we request], for
example, in patient readings, can become highly popular among, say, doctors who
are tracking the effectiveness of new treatments and the like, allowing them to
further explore (via drilldown, drillthrough, etc.) why certain clinics, for
example, are experiencing better patient results than others, once these top
clinics are identified. For more information, together with an illustration
that further clarifies the operation of TopCount(), please see Part I.
As we
noted in Part I of this article, and as we shall see in our continuing practice session,
parameterization of the count variable within the TopCount()
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
TopCount()
I
often parameterize the TopCount() 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 top items within a given scenario to an OLAP report. To provide a
report upon which to practice our exercises, we cloned and modified, in Part I, 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 limit, at report run time, the data returned and
presented in the report to the top x number of Sales Reasons,
removing the others to support a specific analysis focus.
Our colleagues have informed
us, again as we summarized in Part I, that the original report needed some other basic
changes (primarily the removal of the existing report parameter, as
well as its underlying support mechanisms, which originally allowed us to
filter products by Product Category, among other minor changes), which
we accomplished within the preparation section of Part I. We also streamlined and regrouped the report with
regard to other considerations: Instead of three measures in the column
axis, we modified the report to display only two, Internet Orders and Internet
Sales Amount. Moreover, as the client representatives told us that the
report would apply to the Adventure Works organization as a whole, and
would not need to break out measures by Territory Group, we eliminated
this existing grouping within the matrix data region. Finally, we grouped the
measures by Month, instead, so as to present both measures for a
given month / year combination. We then added Year and Month
report parameters, which would allow information consumers to run the
report as of any month / year they choose at runtime.
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 month
/ year presented by which can be dictated on the fly by the easy, ad hoc
selection from associated picklists. Moreover, and more along the lines of
our specific focus , the analysts will be able to limit the presentation to a
top number (determined at runtime) of Sales Reasons, removing the others to
allow these information consumers 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 TopCount() function from the perspective of our reporting
requirements, as well as for the original specification of the needs as
expressed by our client colleagues, see Mastering
OLAP Reports: Parameterizing Number of Top Items with the MDX TopCount()
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, 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
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.
Re-open the DBJ_OLAP_Report
with which we worked in the practice session in Part I.
2.
From the Layout
tab, 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
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.
3.
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 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
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.)
4.
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 (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.)
5.
Click the Data
tab once again.
6.
Click the
downward selector arrow on the right side of the Dataset selector.
7.
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.
8.
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 partially shown
in Illustration 3.
Illustration 3: The
Dataset Created for Year Parameter Picklist Support (Partial View)
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 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
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:
9.
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 ...
10.
Click the Filters
tab on the Dataset dialog.
11.
Click the Expression
box in the top row of the table labeled Filters on the Filters
tab, to activate its downward pointing selector arrow.
12.
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 ...
13.
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.
14.
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
15.
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.
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, lets 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,
lets 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.
Work
with Underlying Dataset Queries to Enforce Cascading and to Support the Parameterized
Top Count 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 top count capability within
the report, as 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.
From the Data
tab, once again, click the downward selector arrow on the right side of the Dataset
selector.
2.
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.
3.
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.
4.
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.
5.
Type the
following into the selected cell:
TimeYear
6.
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.
7.
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.
8.
Leave the
Multiple values checkbox, appearing in the cell to the immediate right of the Hierarchy
cell just populated, unchecked, once again.
9.
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.
10.
Select the FY
2005 member, as
we did within the same position within the primary dataset query earlier.
11.
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 ...
12.
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:
13.
Remove the
following from the existing dataset query:
FROM [Adventure Works]
14.
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
15. 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 retrieval 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 enhancements - this time to the primary dataset
- to embed the top count capabilities requested by our client
colleagues, based upon the selections they make at runtime for year, month,
and number of top items to retrieve.
Add
Syntax to Enforce the Month Parameter Selection and Leverage the MDX TopCount()
Function, Containing the TopCountSpan Parameter Placeholder
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 TopCountSpan 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 19: Remove the
Daterelated Portion of the (Partially Shown) Query
4.
Leaving the
cursor in place, type the following substitute syntax into the query:
(STRTOSET(@TimeMonth, CONSTRAINED)
5.
Carefully
remove the following syntax from the query:
[Sales Reason].[Sales Reason].[Sales Reason].ALLMEMBERS
shown circled in the query presented in Illustration 20.
Illustration 20: Remove
the Sales Reasonrelated Portion of the Query
6.
Leaving the
cursor in place, type the following substitute syntax into the query:
TOPCOUNT([Sales Reason].[Sales Reasons].[Sales Reason].MEMBERS, @TopCountSpan, [Measures].[Internet Sales Amount])
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 { (STRTOSET(@TimeMonth, CONSTRAINED) * TOPCOUNT([Sales Reason].[Sales Reasons].[Sales Reason].MEMBERS, @TopCountSpan,
[Measures].[Internet Sales Amount])
* [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 ( SELECT ( { [Date].[Fiscal].[Fiscal Year].&[2004],
[Date].[Fiscal].[Fiscal Year].&[2005] } ) ON COLUMNS
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 (the replacements have been buffered with extra space for
easier viewing), with our substitutions (boxed in red), within the Query pane
as shown in Illustration
21.
Illustration 21: The
Dataset Query with Our Modifications
7.
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
22.
Illustration 22: The Modified
ProductData Dataset (Partial View)
An
examination of the query and the data it returns reveals that, 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 23.
Illustration 23: 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.
NOTE: Ignore the results that appear if the report auto-executes
at this point. The default year / month parameter selections are
for a period that experienced limited (less than three) Sales Reasons
associated with its activity.
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 partially
depicted in Illustration 24.
Illustration 24: The
Parameter Picklist Cascades to Display the Member Months of FY 2004 (Partial
View)
We
note (scrolling down, as necessary) that twelve months appear within the
Month parameter picklist - the 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 top count capability we have added via the
No. Top Items parameter. As we can see the default of 3
appears in the selector.
7.
Leaving the No.
Top Items parameter selection at default (3),
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, including a top count of three
Sales Reasons (based upon Internet Sales Amount), as shown in Illustration
25.
Illustration 25: The
Report Displays a Top Count Span of Three Sales Reason Items
Let's
perform another quick test of the parameterized top count capability.
8.
Click the
downward pointing arrow to the immediate right of the No. Months to Report parameter
selector.
9.
Select the number
5 within the No. Top Items 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 presenting the Sales
Reasons associated with five largest Internet Sales Amounts, as depicted
in Illustration 26.
Illustration 26: The
Report Displays the Five Largest (Based upon Internet Sales)
Sales Reason Items
We see, therefore,
that the top sales parameter we have put into place, No. Top Items,
accomplishes the intended ends, and enables us to meet the business need expressed
by the information consumers. More generally, we can easily see that Reporting
Services 2005, 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 top count spans, based
upon other criteria 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
Top Items with the MDX TopCount() Function, Part I, of parameterization in Reporting Services
2005. We continued toward our primary objective of getting hands-on
exposure to parameterizing TopCount() 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 TopCount()
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 TopCount() 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 added syntax to enforce the Month parameter selection and
leveraged the MDX TopCount() function, containing the set
from which we wished to present the top items, the count of
items desired) and the numeric expression (the measure Internet
Sales Amount, in our example) upon which we wished to base our top
ranking, to provide the top count 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.