Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 16, 2005

MSSQL Server Reporting Services : Mastering OLAP Reporting: Ad Hoc TopCount and BottomCount Parameters - Page 2

By William Pearson

Ad HocTopCount and BottomCount Parameters

Introduction and Business Scenario

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 first exposure to the concepts of parameterization was in working with very early versions of Cognos Impromptu. My continued application of those concepts over the succeeding 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 easy to see 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. Indeed, I often advise clients who are seeking 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.

An excellent example of parameterization within this context can be had in the exploitation of the popular MDX TopCount() and BottomCount() functions.

A Quick Overview of the TopCount and BottomCount Functions

As many of us who routinely use MDX in our business environments are aware, the TopCount() and BottomCount() functions are highly valuable in enabling us to isolate best performers from among hundreds, perhaps thousands, of fellow members. This ranking capability is critical in data analysis and decision support scenarios: In many business situations, we seek to report upon "best performers" for various reasons. TopCount() facilitates our doing so, allowing us to sort on a numeric value expression that we can provide. We can tell the function how many "top" members we wish to retrieve (say, the "top ten," or the "top twenty"), for a "custom-fit" approach, that matches our needs precisely. BottomCount() allows us to perform the opposite, but in a procedurally similar manner: we can thus force a sort on a numeric value expression for the number of "bottom" members we wish to retrieve. Particularly useful in the analysis of "underperformers" within many contexts, BottomCount() helps us to isolate candidates for elimination (as with underperforming locations, products - even people), or other appropriate action.

According to the Analysis Services Books Online, the TopCount() function returns a specified number of items from the topmost members of a specified set, ordering the set first." We specify three parameters, 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. BottomCount(), by contrast, provides the specified number bottommost members, and works, in all considerations except "direction" and starting point, in a manner similar to TopCount().

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, and is identical, except for the keyword that begins it, for BottomCount().

TopCount(<< Set >>, << Count >> [,<< Numeric Expression >>])

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 with whom we are working within the FoodMart organization wishes to see the top ten Product Names for operating year 1997, based upon total Store Sales for each product.

The basic TopCount() function specifies the "top ten Product Names" (with the number "10" as the Count specification, and [Product].[Product Name].Members as the Set specification, of the function). TopCount() assembles the top ten members from the perspective of Store Sales (the Numeric Expression upon which the complete set of Product Names will first be sorted by the function). The function with arguments is represented in the ON ROWS specification of the following query (enclosed in the dashed-line box):

   {[Measures].[Store Sales]} ON COLUMNS,
{TOPCOUNT([Product].[Product Name].Members, 10, [Measures].[Store Sales])} ON ROWS
FROM [SALES] 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.

Store Sales

Hermanos Green Pepper


Just Right Rice Soup


Urban Small Eggs


Washington Apple Drink


Carlson Head Cheese


Hermanos Lemons


Tell Tale Cantelope


Imagine Waffles


Hilltop Mint Mouthwash


Moms Foot-Long Hot Dogs


Table 1: Results Dataset, with TopCount() Defining Columns

NOTE: For more information on the TopCount(), and, indirectly, the Bottomcount(), functions, see the introductory articles, Basic Set Functions: The TopCount() Function, Part I and Part II in my MDX Essentials series at Database Journal. For use of the function in more elaborate practice scenarios, see Set and String Functions: The GENERATE() Function, within the same series. Finally, for optimization considerations surrounding TopCount() and BottomCount(), see Optimizing MDX: More on Location, and the Importance of Arrangement, found in my MDX in Analysis Services series. All series can be accessed from my Database Journal Index Page.

Parameterizing TopCount() and BottomCount()

I often parameterize the TopCount() and BottomCount() functions 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 we typically assemble 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 top and bottom counts to an OLAP report. To provide a report upon which we can practice our exercises, we will begin with the Foodmart Sales sample report that, among other samples, accompanies the installation of Reporting Services, and which is based upon the ubiquitous Sales sample cube that comes along with an installation of Analysis Services.

For purposes of our practice procedure, we will assume that information consumers within the Marketing office of the FoodMart organization have expressed the need for modifications of the existing Foodmart Sales report. The drilldown capabilities in the report meet most of their analysis needs, but recent requirements to perform "outlier analysis" with regard to the products that the organization sells, have 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 state that a particular analysis operation would be dramatically enhanced if they could simply specify, at report run time, that they wished to see a variable number of "top products" with regard to sales. Ideally, they would like to be prompted for the number of products each time they wished to run the report, as the number might vary with each information request. "Top ten" (of whatever number) analysis of this sort is quite common within OLAP, and we express confidence that we can meet the described need.

The consumers request some additional changes to the report body itself, primarily that we remove the existing parameter, which allows us to filter products by Product Family. The new report will also be simpler with regard to dimensional levels: The consumers state that the row axis (currently occupied by several levels of the Product dimension, which, in turn, drill down to Product Brand Name as the lowest level) needs only to display the Product Name, the lowest level of the Product dimension in the Sales cube. Because this will be a fixed report, designed for a limited use by analysts, we have no need of drilldown features in the row axis. Finally, the consumers ask that the report be modified to allow date selection via a cascading picklist - a feature that will replace the current date drilldown in the column headings.

As an added embellishment, we propose the extension of the requirement to include the capability to dictate, also at run time, our selection of "top" or "bottom" performers, it being our experience that analysis of bottom performers can be useful in many cases. The capability to focus on bottom performers is a natural extension of the need to analyze top performers, if not, perhaps, to support different actions by the organization once "bottom" status is ascertained. The information consumers receive our suggestion with enthusiasm, and agree that they can envision several uses for such a capability. We thus plan from the outset to provide a multi-purpose tool in a compact, efficient "package." These capabilities will also convert the existing Foodmart Sales report to a multi-purpose report whose presentation is dictated on the fly by the easy, ad hoc input of varying criteria, allowing the analysts to meet differing conditions and analysis needs rapidly. 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 FoodMart Sales report to satisfy the information consumers.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM