Parameterizing the TopCount() Function
Throughout many past
articles of the MSSQL
Server Reporting Services series,
we have leveraged parameters within the context of MDX queries. 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 (as most realistic
practitioners would expect). 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 grapple
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.
Reporting Services
2005 introduced
the MDX Query Builder, a tool that appeals, due to its on-off
flexibility, to 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 this article, we will
perform an extended
examination of parameterization within a copy of an existing sample Reporting
Services 2005 report that we will create for this purpose. Our focus will
be to parameterize a specific MDX function, TopCount(), to
demonstrate the process behind supporting parameterization of an index value
(a numeric expression), which we can supply in this and similar functions at
runtime. We will concentrate, in this article, upon the passage of our
selection of a value to the function, as well as with some of the general details
of setting up parameters and so forth in Reporting Services (we
cover the latter in more detail in various articles I have written for that
specific purpose).
Our initial objective
will be to review the basics surrounding the TopCount() function, and
then to perform an exercise within which we put the function to work to meet
the stated business requirements of a hypothetical client.
A Quick Overview of the TopCount() Function
According to the Analysis Services Books Online,
the TopCount() function returns a specified number of items from
the topmost members of a specified set, optionally ordering the set first.
The TopCount() function stands out as an excellent general example of
the potential power of MDX. We specify three entities, a set, a count,
and a measure, in most cases, and TopCount() returns the number
of top performers (or top anything, in effect), based upon our input.
As we shall see, TopCount()
sorts the set we specify by another expression we provide within the function,
thus breaking the natural hierarchy of the set. The basis of sorting by TopCount()
closely resembles that used by the Order() function. We should
therefore keep in mind that, in the absence of a specified sort, the function
will institute a sort based upon the default member.
NOTE: For information surrounding the Order()
function, see my article Basic
Set Functions: The Order() Function, a member of the MDX Essentials series at Database Journal.
To
restate our initial explanation of its operation, the TopCount() function
sorts a set based upon a numerical expression we provide, and
then picks the top (whatever number we provide in the Count parameter of
the function) items in that set, based upon the rank order established
by the function. The frequent requirement to examine a subset of members at
the top of the general set is easily answered by the TopCount() approach
of sort, and then pick this number of members from the top down.
Syntactically, the set upon which we seek to perform the TopCount()
operation is specified within the parentheses to the right of TopCount,
a common arrangement within MDX functions, as we have seen in our previous
articles. The syntax is shown in the following string.
TopCount(<< Set >>, << Count >> [,<< Numeric Expression >>])
We follow <<Set>>, the set specification,
with a comma, which is followed by <<Count>>, the numeric
expression we provide to dictate the number of top members we wish for the
function to return. <<Count>>» is, in turn, followed by a numeric
expression whereby we specify the sort by criteria. As we have
mentioned, TopCount() always breaks the natural hierarchy of the set
due to the fact that the set is sorted upon the third argument we
provide as <<Numeric Expression>>, before returning the top
(specified number of) members from that sort.
The items returned by TopCount() are limited to the
<< Count >> input that we provide, even in the case of
ties in the <<Numeric Expression>> value. This may be
important to consider when evaluating the data retrieved by the function, in addition
to default behavior of the function when no sort criteria (in the form, again,
of the <<Numeric Expression>>) is provided.
The following example expression illustrates the use of
the TopCount() function, within a simple business context. Lets say
that a group of information consumers within the FoodMart (from the Analysis
Services 2000 environment) Planning organization wishes to see the top ten
Warehouse Cities for operating year 1997, based upon total Warehouse
Profit for each city.
The basic TopCount() function specifies the top
ten Warehouse Cities (with the number 10
as the Count specification, and [Warehouse].[City].Members as the
Set specification, of the function). TopCount() assembles the
top ten members from the perspective of Warehouse Profit (the Numeric
Expression upon which the complete set of Warehouse Cities will
first be sorted by the function). The function with arguments is represented in
the ON ROWS line of the following query:
SELECT
{[Measures].[Warehouse Profit]} ON COLUMNS,
{TOPCOUNT([Warehouse].[City].Members, 10,
[Measures].[Warehouse Profit])} ON ROWS
FROM
[WAREHOUSE]
WHERE
([Time].[Year].[1997])
Assuming
that we placed the TopCount() function within the query as constructed
above, our returned dataset would resemble that shown in Table 1.
|
Warehouse
Profit
|
Salem
|
17,041.57
|
Tacoma
|
16,725.63
|
Los
Angeles
|
12,789.25
|
San
Diego
|
12,536.28
|
Bremerton
|
12,063.17
|
Seattle
|
12,021.58
|
Spokane
|
5,835.46
|
Beverly
Hills
|
5,674.85
|
Yakima
|
5,635.98
|
Portland
|
4,186.58
|
Table
1: Results Dataset, with TopCount() Defining Columns
As we would
expect, the top ten Warehouse Cities are returned, having been sorted by
Warehouse Profit, and having had the top ten values within that sort
isolated, as we requested in the function.
As is the
case with many MDX functions, TopCount() can be used to deliver
sophisticated analysis datasets, particularly when we employ it along with
other MDX functions. We will practice the use of TopCount() in the
section that follows, building from a simple example, similar to that which we
saw above, to a scenario where we apply the function, in conjunction with a
calculated member, to reveal an additional analytical perspective. In our next
article, we will examine even more sophisticated applications of the function,
combining its use with that of other functions we have explored in articles of
the series.
In the
following sections, we will perform the steps required to add a parameterized number
of top items specification capability to an OLAP report. To provide a
report upon which we can practice our exercises, we will begin 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.
The Business Requirement
For
purposes of our practice procedure, we will assume that information consumers
within the Marketing office of the Adventure Works organization have
expressed the need for modifications to the existing Sales Reason
Comparisons report. The existing capabilities in the report meet some 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.
That is, when running
the report, they wish to limit the presentation to the top x number of Sales Reasons, removing
the others to support a specific analysis focus.
The consumers request
some additional changes to the report itself, primarily that we remove the
existing report parameter, as well as its underlying support mechanisms,
which allows us to filter products by Product Category.
The new report will
also be simpler with regard to other considerations: Instead of three measures
in the column axis, we will only display two, Internet Orders and Internet
Sales Amount. Moreover, as the report will apply to the Adventure Works
organization as a whole, and will not break out measures by Territory Group,
we will eliminate this existing grouping within the matrix data region.
Finally, we will group the measures by Month, instead, so as to present
both measures for a given month / year combination. We will add Year
and Month report parameters, which will allow information consumers to
run the report as of any month / year they choose at runtime.
These changes will
result in a multi-purpose report, which will allow 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.
As part of our typical
business requirements gathering process, we listen attentively to the details,
formulating, in the background, an idea of the steps we need to take in
modifying a copy of the report to produce the desired results. Then, having
grasped the stated need, and having confirmed our understanding with the
intended audience, we begin the process of modifying the Sales Reason
Comparisons report to satisfy the information consumers.