Mastering OLAP Reports: Parameterized Grouping - Page 2
September 23, 2008
Parameterizing Grouping in an Analysis Services Report
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 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 resulting 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 a relatively straightforward 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 row group that we add to a report that already contains one row group, although the procedure we undertake will work for column groups, as well. As a part of meeting a hypothetical client need that we will detail below, we will concentrate upon the steps we will need to take, and the objects we will need to modify / add to the functional OLAP report to support parameterization of the newly added group, as well as parameterizing the sorting of members within that group. We will also cover, in passing, 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).
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 (which is available to anyone installing Analysis Services 2005).
The Business Need
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.
In discussing their requirements in detail, representatives of the Marketing department inform us that the current report contains a filter that limits the data presented to a couple of operating years. In addition to the focus requirements of this lesson, they request that we open the available periods for reporting - to the full range of operating data that is physically available within the cube. 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.
They also specify that they would like to see more simplicity in the new report 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 (column) group the measures by Month, instead.
More to the primary focus of this article, our client colleagues state that ongoing analysis operations would be dramatically enhanced if they could simply specify, at report run time, a subgrouping of the information displayed. Once the report is modified to comply with the general requirements above, they say, they would like it to generate a subgroup below the Sales Reason group. Moreover, they tell us, they would like to be able to select, at run time, among multiple group choices: to meet present needs, they want to be able to choose between Product, Product Category, and Product Subcategory; Product would be the most frequent selection, made by the largest number of information consumers, in any given month, but the option to select, instead, Category or Subcategory, would be useful, as a minority of consumers require totals among these Product levels on a regular basis to perform various analytical pursuits. Finally, the capability to perform choices at run time to sort upon the members of any of the subgroupings (regardless of the actual choice of subgroup itself) would, according to the client representatives, be of utility to a couple of analysts, as well.
These changes will result in a multi-purpose report, the subgrouping (and its respective sort order) presented by which can be dictated on the fly by the easy, ad hoc selection of the Product level upon which to group, as well as to sort, each time the report is executed. This will, of course, allow the 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.
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.