Mastering OLAP Reports: Parameterizing Number of "Top" Items with the MDX TopCount() Function, Part II
August 18, 2008
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.
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:
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.
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.