Mastering OLAP Reports: Parameterized Grouping

The ability to choose grouping criteria at run time can mean the selection and delivery of enterprise data in a more focused and consumer-friendly manner. It also means doing more with fewer reports. BI Architect Bill Pearson leads a hands-on demonstration of a way to parameterize grouping with runtime sorting options.

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 in recent years, 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.

Overview

In recent articles within this series, we have focused 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.

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 several 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’s 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 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 practitioner 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.

• Mastering OLAP Reporting: Cascading Prompts
• Customize Automatically Created Parameter Support Objects
• Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets
• Support Parameterization from Analysis Services
• Parameterization from Analysis Services – Cascading Picklists
• Support Parameterization from Analysis Services – Parameter Defaults
• Mastering OLAP Reports: Parameterizing Number of “Look Back” Periods with the MDX LastPeriods() Function, Parts I and II
• Mastering OLAP Reports: Parameterizing Number of “Top” Items with the MDX TopCount() Function, Parts I and II
  
  
  

Throughout various articles of this series, 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 perform an examination of the straightforward mechanics behind the parameterization of grouping, and sorting of the members within the group we select. We will get hands-on exposure to parameterizing grouping within a preexisting sample OLAP report. Beginning with the general concepts, we will continue into a practice session where we set up a scenario, within which we work with a basic OLAP report, to expose the steps involved. In examining the rudiments of grouping parameterization within an OLAP report containing a matrix data region, we will:

• Open the sample Report Server project, AdventureWorks Sample Reports, and ascertain connectivity of its shared Analysis Services data source;
• Create a clone of an existing sample Analysis Services report, containing a matrix data region, with which to perform our practice exercise;
• Make structural modifications to the clone report, to prepare for our practice exercise session with parameters;
• Perform a brief overview of the concepts behind ad hoc grouping, which we will use to support the stated reporting needs of a hypothetical client;
• Modify the existing dataset within, and add a new dataset to, the sample report clone, to support group and group member sorting parameterization;
• Make modifications to the report layout to support the stated client reporting needs;
• Add Report Parameters to support ad hoc group and group member sorting selection;
• Modify matrix settings to fully enable the newly supported group and group member sorting parameterization;
• Modify the face of the report to dynamically reflect grouping parameter selections made at run time.
• Discuss the results obtained with the development techniques that we exploit throughout our practice session.

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.

Practice

Our first objective is to create a copy of the Sales Reason Comparisons sample report, within which we can implement the newly required parameters. The focus of our efforts will be the addition of the requested parameters into an OLAP report containing a matrix data region (the mechanics behind adding the capability, not the design of the report itself). To save time, we will be working with a simple, pre-existing sample report – in reality, the business environment will typically require more sophistication. The process of setting up the basic parameters is the same in real world scenarios, with perhaps a more complicated set of underlying considerations. (As I have often said, I virtually never encounter a client reporting requirement that does not involve at least basic parameterization.)

We will perform our practice session from inside the MSSQL Server Business Intelligence Development Studio. For more exposure to the Business Intelligence Development Studio itself, and the myriad design, development and other evolutions we can perform within this powerful interface, see other articles in this series, as well as within my Database Journal series Introduction to MSSQL Server Analysis Services. In this article, we will be commenting only on the features relevant to our immediate practice exercise, to allow us to keep to the focus of the article more efficiently.

Preparation: Create a Clone Report within the Reporting Services Development Environment

For purposes of our practice session, we will create a copy of the Sales Reason Comparisons report, one of several samples that are available with (and installable separately from) the MSSQL Server 2005 integrated business intelligence suite. Making preparatory modifications, and then making the enhancements to the report to add the functionality that forms the subject of our lesson, can be done easily within the Business Intelligence Studio environment. Working with a copy of the report will allow us the luxury of freely exploring our options, and will leave us with a working example of the specific approach we took, to which we can refer in our individual business environments.

Open the Sample Report Server Project

For purposes of our practice session, we will open the AdventureWorks Sample Reports project, which contains the sample reports that ship with the Reporting Services component of the MSSQL Server 2005 suite. We will complete our practice session within the sample project so as to save the time required to set up a development environment from scratch within the Business Intelligence Development Studio.

To open the AdventureWorks Sample Reports project, please see the following procedure in the References section of my articles index:

Ascertain Connectivity of the Shared Data Source

Let’s ensure we have a working data source. Many of us will be running “side-by-side” installations of MSSQL Server 2000 and MSSQL Server 2005. This means that our installation of the latter will need to be referenced as a server / instance combination, versus a server name alone. (The default for the Adventure Works DW project sample’s connection is localhost, which will not work correctly in such a side-by-side installation, as MSSQL Server 2000 will have assumed the identity of the local PC by default.)

If you do not know how to ascertain or modify connectivity of the Analysis Services data source, please perform the steps of the following procedure in the References section of my articles index:

Create a Copy of the Sales Reason Comparisons Report

We will begin with a copy of the Reporting Services 2005 Sales Reason Comparisons OLAP report, which we will use for our practice exercise. Creating a “clone” of the project means we can make changes to select contents (perhaps as a part of later exploration with our independent solution), while retaining the original sample in a pristine state for other purposes, such as using it to accompany relevant sections of the Books Online, and other documentation. Such uses may form a part of learning more about Reporting Services (particularly an OLAP report using an Analysis Services data source), and other components of the Microsoft integrated business intelligence solution in general.

If you do not know how to create a copy of an existing report, please perform the steps of the following procedure in the References section of my articles index:

We now have a clone OLAP report file within our Reporting Services 2005 Project, with which we can proceed in the next section to make modifications for our subsequent practice session.

Preparation: Modify the OLAP Report for Use within Our Practice Session

We will next make a few modifications to prepare the report for our practice session. Our objective will be to begin the session with a simple OLAP report that contains no parameters. Let’s open the report in Layout view (for those of us not already there) and make the necessary settings to place it into a state upon which we can commence our practice steps.

DBJ_OLAP_Report.rdl opens in Layout view.

We will start with the Data tab.

We enter the Data tab, where we will remove a handful of components that we do not need for our practice session. We will accomplish this from the perspective of the MDX Query Builder, the main components of which (in Design mode) are labeled in Illustration 2 below.

The primary (and sole remaining) dataset, ProductData opens.

We will make an addition to the Dataset next.

The new Month column appears, as desired. Having made the necessary changes on the Data tab, we are ready to move to the Layout tab, where we can conclude our preparatory modifications to the report file.

The gray column and row bars disappear, as a light, patterned outline forms around the matrix data region, and the context menu appears.

The Matrix Properties dialog appears, defaulted to the General tab.

The Grouping and Sorting Properties dialog appears, defaulted to its General tab.

matrix1_Month

#,###

Here we are simply changing the existing format to one more appropriate for a count value. The Format code appears on the Format tab of the Textbox Properties dialog as shown in Illustration 16.

We next need to change the column heading associated with the Group we modified earlier, so that it reflects Months, versus the previous grouping criteria of Sales Territory Group.

The Expression Editor opens.

=Fields!Month.Value

Recall that the intent is to label the column to reflect the nature of the Group we have put in place: Months instead of Sales Territory Group.

The Expression Editor appears, with our modification, as shown in Illustration 18.

All that remains to complete our preparatory steps is to remove the pre-existing Report Parameter. Recall that one of our earlier steps was to remove the Product entry from the Filter pane (the sample report from which our report was cloned came equipped with a parameterized Product filter). This entry had been established with the Parameter checkbox selected, a common way to create a parameterized filter, as we have seen in various other articles of the MSSQL Server Reporting Services series. Because parameterization was selected for the Product filter, it follows that a Report Parameter might well have been created at the time the entry was made. We will now remove the remaining Report Parameter.

The Report Parameters dialog opens, and we see a single Report Parameter listed.

We will execute the report to ascertain that our modifications are complete, and that we have a working report for the practice session that follows.

The Report is being generated message briefly appears, and then the report displays. The modified report appears as partially depicted in Illustration 23.

We note that the fiscal months July 2001 through July 2004 appear (representing a total of 37 months, the earliest of which are relatively sparse with regard to data).

Our report is now ready for the practice exercise, which we will begin in the next section.

Procedure: Add Group and Group Member Sort Parameterization in Reporting Services 2005

Modify the Existing Dataset to Make Grouping Options Available to the Report

As we noted earlier, our intent within this article is to examine the detailed mechanics involved in adding – and parameterizing – a group, together with the ad hoc capability to sort the members of the selected group, within our report. First, we will need to add the supporting data elements to our report. We will do this by modifying the composition of the pre-existing ProductData dataset via adjustments to the underlying dataset query. We will accomplish these steps from the Data tab, to which we will return next.

We return to the Data tab, where this time we will need to add three data elements to the pre-existing ProductData dataset. As most of us know, when we create a dataset from an Analysis Services data source, Report Designer initially offers us the MDX query designer in Design mode. We can work within Design mode to interactively build an MDX query using graphical elements. Design mode affords us the capability to select a cube and to drag numerous objects to the Data pane, including:

• Dimensions
• Dimension Attributes
• Levels
• Hierarchies
• Measures
• Key Performance Indicators (KPIs).

We can also add calculated members, set default values for variables, and automatically see result sets returned for the queries we build (particularly useful as we make changes within the Query pane) to the Data pane. Moreover, we can set filters (like the one we removed in our earlier steps to prepare our OLAP report clone for use within our practice session), to limit the data retrieved from the data source by the query, and define parameters.

To provide support for grouping upon each, we will add the Category, Subcategory and Product levels of the Product dimension (Product Categories hierarchy) to the Dataset next.

Within the newly expanded Product Categories hierarchy, we see the Category, Subcategory and Product levels, as shown in Illustration 24.

The new Category, Subcategory and Product columns simultaneously appear, populating as desired. Having made the necessary changes on the Data tab, we are ready to move to the Layout tab, where we can conclude our preparatory modifications to the report file.

Category (“=Fields!Category.Value”) appears in the row axis of the report, to the right of Sales Reason, as depicted in Illustration 27.

NOTE: We will use this single cell for grouping any of the Category, Subcategory and Product data elements, as we shall see, through the use of an expression with which we replace the “=Fields!Category.Value” that currently occupies the cell. For now, we will leave the current occupant here as a placeholder.

Add a New Dataset to Support the Proposed Report Parameters

Now we will add a new dataset to support the proposed parameter picklists (the parameter selections that the information consumers see at report run time).

The Dataset dialog opens, defaulted to the Query tab.

RowGroup

SELECT 
   'Product Category' AS ParameterCaption, 
   'Category' AS ParameterValue
UNION
SELECT 'Product Subcategory'  AS ParameterCaption, 
 'Subcategory' AS ParameterValue
UNION
SELECT 'Product'  AS ParameterCaption, 
 'Product' AS ParameterValue

The Dataset dialog appears, with our input, as depicted in Illustration 29.

We are taken to the Query pane, where our newly crafted query appears as shown in Illustration 30.

Data is returned, and appears in the Data Pane as depicted in Illustration 31.

We will see how the ParameterCaption and ParameterValue fields are employed when we set up the two report parameters in the steps that follow.

Set Up the Grouping and Group Member Sorting Report Parameters

We are ready at this point to add a couple of new report parameters. We will reference these parameters later in our procedure.

The now empty Report Parameters dialog opens.

RowGroupBy

Group Rows By:

The Dataset, Value field and Label field selectors appear.

The Report Parameters dialog for the new RowGroupBy parameter appears, with our input, as depicted in Illustration 33.

We will next create a report parameter to for use in sorting the members of the group we select at runtime.

RowSortBy

Sort Rows By:

The Dataset, Value field and Label field selectors appear, as we saw earlier.

The Report Parameters dialog for the new RowSortBy parameter appears, with our input, as shown in Illustration 34.

Let’s preview the report at this stage, simply to ascertain that the new parameters appear to work properly (we will have to do some further work on the Layout tab before they actually affect the report display – we just want to determine that the picklists appear appropriate, etc.).

The two new report parameters appear atop the report - actually execution is held up until we make a selection here. We can click the selector arrows and see that the three grouping / sorting criteria appear in each parameter picklist, as depicted (for the Group Rows By parameter) in Illustration 35.

We are now ready to “hook things up” within the Layout tab of the report so that the new parameters actually work, and so that the report reflects our selections in a user-friendly manner.

Set Up the Grouping and Group Member Sorting Report Parameters

We will move now to the Layout tab, where we have several adjustments to make to leverage our new report parameters.

The Matrix Properties dialog appears, once again, defaulted to the General tab.

The Grouping and Sorting Properties dialog appears, as before, defaulted to its General tab.

matrix1_RowGroupBy

The Expression Editor opens.

=Fields(Parameters!RowGroupBy.Value).Value

Recall that the intent is to enforce grouping upon our selection via the runtime report parameter (RowGroupBy). Reporting Services once again allows us to answer the need via an expression.

The Expression Editor appears, with our modification, as depicted in Illustration 37.

The Expression Editor opens.

=Fields(Parameters!RowSortBy.Value).Value 

The Expression Editor appears, with our addition, as depicted in Illustration 39.

We return to the Sorting tab, where we see, in the Direction setting adjacent to the Expression setting, a default of Ascending, as shown in Illustration 40.

We are returned to the Layout tab, where we will perform a couple of remaining modifications to make the new report more consumer-friendly at runtime. First, let’s modify the subgroup’s label (we left it at “=Fields!Category.Value” when we created the column in earlier steps).

The Expression Editor opens.

=Fields(Parameters!RowGroupBy.Value).Value

The Expression Editor appears, with our substitution, as shown in Illustration 42.

Next, we will add a simple column heading, which we will insert atop the new subgroup label, to make our layout a little more self-explanatory.

A second copy of the Sales Reason textbox appears near the drop point.

The Expression Editor opens, once again.

=Parameters!RowGroupBy.Label

The Expression Editor appears, with our substituted syntax, as shown in Illustration 48.

Let’s preview the report at this juncture to confirm that we have taken the steps needed to meet the requirements specified by our client colleagues.

The two new report parameters appear atop the report, as we noted earlier. This time we will actually make parameter selections and then execute the report.

Our selection criteria appear in the parameter selectors as depicted in Illustration 49.

The Report is being generated message briefly appears, and then we see the report display, similar to that partially shown in Illustration 50.

As we can see, our Product group members appear, in ascending order, as requested. Moreover, our new column label dynamically reflects the selected group, as well. The client representatives express satisfaction with our efforts, and state that, with a few cosmetic changes, the report will be ready for deployment to the targeted information consumer group.

Conclusion ...

In this article, we explored a rudimentary approach to the parameterization of grouping within an Analysis Services report containing a matrix data region. Beginning with a discussion surrounding the general concepts of parameterization, we continued into a practice session where we set up a scenario within which we work with a basic OLAP report, to expose the steps involved. In examining the concepts underlying runtime grouping parameterization (along with ad hoc sorting of the selected group’s members) we first described a stated reporting need of our hypothetical client – a need that we would set out to support in our practice session.

We next prepared a copy of a sample report sourced from Analysis Services, making structural modifications to the clone report, to meet peripheral client requests, as well as to prepare for our practice exercise session with parameters surrounding a matrix data region. We modified the existing dataset, and added a new dataset, to support group and group member sorting parameterization, and then made modifications to the report layout to support the stated client reporting needs.

We next added report parameters to support group and group member sorting parameterization, and modified matrix settings to fully enable the newly supported parameterization. We then modified the face of the report to dynamically reflect group and group member sorting parameter selections made at run time. Finally, we discussed the results obtained with the development techniques that we exploited throughout our practice session.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Reporting Services Forum.