Mastering OLAP Reports: Parameterizing Number of "Top" Items with the MDX TopCount() Function, Part I - Page 2

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. Let’s 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.

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.