Mastering OLAP Reports: Parameterized Grouping

Tuesday Sep 23rd 2008 by William Pearson
Share:

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.

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 MSSQL Server Reporting Services series here at Database Journal:

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:

Open the Sample Report Server Project

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:

Ascertain Connectivity of the Analysis Services Data Source

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:

Create a Copy of a Sample OLAP Report

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.

1.  Right-click DBJ_OLAP_Report.rdl (or your own choice of a similar report) in the Solution Explorer.

2.  Select Open from the context menu that appears, as shown in Illustration 1, as necessary.

Illustration 1:  Opening the New Report ...
Illustration 1: Opening the New Report ...

DBJ_OLAP_Report.rdl opens in Layout view.

We will start with the Data tab.

3.  Click 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.

Illustration 2:  The MDX Query Builder – Design Mode
Illustration 2: The MDX Query Builder – Design Mode

4.  With the Dataset selector, select the ProductList dataset, as depicted in Illustration 3.

Illustration 3:  Select the ProductList Dataset ...
Illustration 3: Select the ProductList Dataset ...

5.  Once the Dataset loads, click the Delete button, as shown in Illustration 4.

Illustration 4:  Deleting the Unneeded Dataset ...
Illustration 4: Deleting the Unneeded Dataset ...

6.  Click Yes on the Microsoft Report Designer warning message dialog that appears next, as depicted in Illustration 5.

Illustration 5:  Confirm Intention to Delete ...
Illustration 5: Confirm Intention to Delete ...

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

7.  Within the Query pane of the MDX Query Builder, right-click the column heading for Internet Total Product Cost.

8.  Click Delete Internet Total Product Cost from the context menu that appears, as shown in Illustration 6.

Illustration 6:  Deleting the Unwanted Measure from the Dataset
Illustration 6: Deleting the Unwanted Measure from the Dataset

9.  Within the Calculated Members pane (bottom left corner of the Data tab), right-click the sole Calculated Member, Profit.

10.  Click Delete on the context menu that appears, as depicted in Illustration 7.

Illustration 7:  Deleting the Calculated Member
Illustration 7: Deleting the Calculated Member

11.  Click “Yes,” when asked “Are you sure ....”

12.  Right-click the single entry in the Filter pane (directly atop the Query pane).

13.  Click Delete to eliminate the existing Product Category Parameter from the Filter pane, as shown in Illustration 8.

Illustration 8:  Deleting the Product Parameter from the Filter Pane
Illustration 8: Deleting the Product Parameter from the Filter Pane

We will make an addition to the Dataset next.

14.  Within the Metadata pane, expand the Date dimension, by clicking the “+” sign to its immediate left.

15.  Expand the Fiscal folder that appears within the Date dimension.

16.  Expand the Fiscal hierarchy (labeled “Date.Fiscal”) within the Fiscal folder.

17.  Drag the Month level into the Data pane, dropping it to the left of the Sales Reason column, as depicted in Illustration 9.

Illustration 9:  Adding Months to the Dataset
Illustration 9: Adding Months to the Dataset

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.

18.  Click the Layout tab, as shown in Illustration 10.

Illustration 10:  Click the Layout Tab
Illustration 10: Click the Layout Tab

19.  On the Layout tab, within the matrix data region, select the value appearing underneath the Internet Total column heading.

20.  Right-click the value in the text box (the value appears as =Sum(Fields!Internet_Total_Product_Cost.Value) ).

21.  Select Delete from the context menu that appears, as depicted in Illustration 11.

Illustration 11:  Delete the Value for Internet Total Product Cost
Illustration 11: Delete the Value for Internet Total Product Cost

22.  Right-click the upper left-hand corner of the Matrix Data Region (the gray square).

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

23.  Select Properties from the context menu, as shown in Illustration 12.

Illustration 12:  Select Properties from the Context Menu ...
Illustration 12: Select Properties from the Context Menu ...

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

24.  Click the Groups tab.

25.  In the Columns section of the Groups tab (in the lower half of the tab), select the middle group, named matrix1_Sales_Territory_Group.

26.  Click the Edit button, as depicted in Illustration 13.

Illustration 13:  Editing the Matrix1_Sales_Territory_Group Column Group ...
Illustration 13: Editing the Matrix1_Sales_Territory_Group Column Group ...

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

27.  Atop the General tab, change the existing Name to the following:

matrix1_Month

28.  Click the row containing the Expression value (currently the expression is “Fields!Sales_Territory_Group.Value”), within the Group on list, to enable the selector.

29.  Select =Fields!Month.Value within the selector, as shown in Illustration 14.

Illustration 14: Replacing the Existing Group Expression ...
Illustration 14: Replacing the Existing Group Expression ...

30.  Click OK, to accept our modifications, and to close the Grouping and Sorting Properties dialog.

31.  Click OK on the Groups tab, to close the Matrix Properties dialog.

32.  Once again on the Layout tab, within the Matrix Data Region, right-click the value appearing underneath the Internet Orders column heading (the leftmost of the two remaining value cells).

33.  Select Properties on the context menu that appears, as depicted in Illustration 15.

Illustration 15:  Modifying Properties for the Count Value ...
Illustration 15: Modifying Properties for the Count Value ...

34.  On the Textbox Properties dialog that next appears, click the Format tab.

35.  Replace the existing Format code setting (in the upper left corner of the Format tab) with the following string:

#,###

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.

Illustration 16:  Replacing the Existing Format Code ...
Illustration 16: Replacing the Existing Format Code ...

36.  Click OK to accept our modifications, and to dismiss the Textbox Properties dialog.

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.

37.  Right-click the text box containing the column label (immediately above the two measure values’ column labels) in the Layout tab (the text box currently contains the expression =Fields!Sales_Territory_Group.Value).

38.  Select Expression... from the context menu that appears, as depicted in Illustration 17.

Illustration 17: Modifying the Primary Column Label
Illustration 17: Modifying the Primary Column Label

The Expression Editor opens.

39.  Replace the expression in the upper portion of the Editor with the following:

=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.

Illustration 18:  The Expression Editor with Our Substitution in Place
Illustration 18: The Expression Editor with Our Substitution in Place

40.  Click OK to accept our modification, and to dismiss the Expression Editor.

41.  Click the column heading for which we have just changed the expression, if necessary, simply to select the textbox.

42.  Change the Font Size, in the Format toolbar atop the design environment, from 12pt to 10pt, to make the presentation more compact, as depicted in Illustration 19.

Illustration 19:  Change the Font to 10pt ...
Illustration 19: Change the Font to 10pt ...

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.

43.  From the main menu, select Report -> Report Parameters..., as shown in Illustration 20.

Illustration 20:  Select Report -> Report Parameters...
Illustration 20: Select Report -> Report Parameters...

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

44.  Ensure that the ProductCategory Parameter is selected.

45.  Click the Remove button, as depicted in Illustration 21.

Illustration 21:  Removing the Residual Report Parameter ...
Illustration 21: Removing the Residual Report Parameter ...

46.  Click OK to accept our modifications, and to dismiss the (now empty) Report Parameters dialog.

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.

47.  Click the Preview tab, as shown in Illustration 22.

Illustration 22:  Click the Preview Tab to Execute the Report
Illustration 22: Click the Preview Tab to Execute the Report

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

Illustration 23:  The Modified Report (Partial View)
Illustration 23: The Modified Report (Partial View)

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.

1.  Click the Data tab, once again

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.

2.  Within the Metadata pane, expand the Product dimension, by clicking the “+” sign to its immediate left.

3.  Expand the Product Categories hierarchy that appears within the Product dimension (beneath the folders and attribute hierarchies that appeared when we expanded the Product dimension).

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

Illustration 24:  The Targeted Levels Appear ...
Illustration 24: The Targeted Levels Appear ...

4.  Click the Product Categories hierarchy to select it.

5.  Drag the Product Categories hierarchy icon into the Data pane, dropping it to the right of the Sales Territory Groups column, as depicted in Illustration 25.

Illustration 25:  Adding the Levels the of the Product Categories Hierarchy to the Dataset
Illustration 25: Adding the Levels the of the Product Categories Hierarchy to the Dataset

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.

6.  Click the Layout tab.

7.  Drag the Category data field from the Datasets pane, across the single existing row cell (containing “=Fields!Sales_Reason.Value”) within the matrix data region, dropping it to the right of the cell (the “I” beam appears at the drop point), as shown in Illustration 26.

Illustration 26:  Dragging the Category Data Field to the Matrix Data Region
Illustration 26: Dragging the Category Data Field to the Matrix Data Region

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

Illustration 27:  Category Appears in the Rows Axis ...
Illustration 27: Category Appears in the Rows Axis ...

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).

1.  Click the Data tab, once again.

2.  With the Dataset selector, select <New Dataset ...>, as shown in Illustration 28.

Illustration 28:  Select <New Dataset ...>
Illustration 28: Select <New Dataset ...>

The Dataset dialog opens, defaulted to the Query tab.

3.  Type the following into the Name box :

RowGroup

4.  In the Data source box just below, select AdventureWorks (shared).

5.  In the Query string box, type (or cut and paste) the following query:

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.

Illustration 29:  The Dataset Dialog with Our Input ...
Illustration 29: The Dataset Dialog with Our Input ...

6.  Click OK to accept our input and to dismiss the Dataset dialog.

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

Illustration 30:  Our Query in the (Partially Depicted) Query Pane
Illustration 30: Our Query in the (Partially Depicted) Query Pane

7.  Click the Run (!) button to test the query.

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

Illustration 31: Data is Returned and Appears in the Data Pane
Illustration 31: Data is Returned and Appears in the Data Pane

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.

1.  Select Report -> Report Parameters ..., from the main menu, as shown in Illustration 32.

Illustration 32:  Select Report --> Report Parameters ...
Illustration 32: Select Report --> Report Parameters ...

The now empty Report Parameters dialog opens.

2.  Click Add to begin adding our first report parameter.

3.  Type the following into the Name box in the Properties section (at the top of the right side of the dialog):

RowGroupBy

4.  Type the following into the Prompt box in the Properties section (the second box down from the Name box ):

Group Rows By:

5.  Clear any check marks in the lower part of the Properties section.

6.  In the Available values section (just below the Properties section on the right side of the dialog), click the From query radio button to select it.

The Dataset, Value field and Label field selectors appear.

7.  Click the downward pointing arrow on the Dataset selector (currently displaying the ProductData selection) within the Data tab.

8.  Select the new RowGroup dataset.

9.  In like manner, select ParameterValue within the Value field.

10.  Select ParameterCaption within the Label field.

11.  Leave all other settings at default.

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

Illustration 33:  Settings for the New RowGroupBy Parameter
Illustration 33: Settings for the New RowGroupBy Parameter

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

12.  Click Add, once again, in the bottom left corner of the still open Report Parameters dialog.

13.  Type the following into the Name box in the Properties section (at the top of the right side of the dialog):

RowSortBy

14.  Type the following into the Prompt box in the Properties section (the second box down from the Name box ):

Sort Rows By:

15.  Clear any check marks in the lower part of the Properties section, as we did with the first report parameter above.

16.  In the Available values section (just below the Properties section on the right side of the dialog), click the From query radio button to select it.

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

17.  Ensure that the new RowGroup dataset is selected within the Dataset selector.

18.  In like manner, select ParameterValue within the Value field.

19.  Select ParameterCaption within the Label field.

20.  Leave all other settings at default.

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

Illustration 34: Settings for the New RowSortBy Parameter
Illustration 34: Settings for the New RowSortBy Parameter

21.  Click the OK button on the Report Parameters dialog to accept the new parameters and to dismiss the dialog.

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.).

22.  Click the Preview tab, once again.

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.

Illustration 35:  The Parameter Picklists (Only the Group Rows By Parameter is Shown Here) Appear to Work Properly
Illustration 35: The Parameter Picklists (Only the Group Rows By Parameter is Shown Here) Appear to Work Properly

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.

1.  Click the Layout tab.

2.  Click within the Matrix data region, to cause the gray header bars to appear on top and to the left.

3.  Right-click the upper left corner, where the gray bars meet, as we did in the preparation steps above.

4.  Select Properties from the context menu that appears, as we did earlier.

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

5.  Click the Groups tab.

6.  In the Rows section of the Groups tab (in the upper half of the tab), select the second group, named matrix1_Category.

7.  Click the Edit button, once again.

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

8.  Atop the General tab, change the existing Name to the following:

matrix1_RowGroupBy

9.  Click the row containing the Expression value (currently the expression is “=Fields!Category.Value”), within the Group on list, to enable the selector.

10.  Select <Expression...> within the selector (the top entry), as shown in Illustration 36.

Illustration 36: Replacing the Existing Group Expression ...
Illustration 36: Replacing the Existing Group Expression ...

The Expression Editor opens.

11.  Replace the expression in the upper portion of the Editor with the following:

=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.

Illustration 37:  The Expression Editor with Our Substitution in Place
Illustration 37: The Expression Editor with Our Substitution in Place

12.  Click OK to accept our modification, and to dismiss the Expression Editor.

13.  Click the Sorting tab.

14.  In the Expression box of the section labeled Sort on, select <Expression...> within the selector (the top entry), as shown in Illustration 38.

Illustration 38: Replacing the Existing Sorting Expression ...
Illustration 38: Replacing the Existing Sorting Expression ...

The Expression Editor opens.

15.  Type, or cut and paste, the following expression into the upper portion of the Editor with the following:

=Fields(Parameters!RowSortBy.Value).Value 

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

Illustration 39:  The Expression Editor with Our Newly Added Syntax
Illustration 39: The Expression Editor with Our Newly Added Syntax

16.  Click OK to accept our modification, and to dismiss the Expression Editor.

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.

Illustration 40:  The Sorting Tab with Our Input ...
Illustration 40: The Sorting Tab with Our Input ...

17.  Leaving the Direction setting at its default of Ascending, click OK to accept our input and to dismiss the Grouping and Sorting Properties dialog.

18.  Click OK on the Matrix Properties dialog next, to accept all changes and dismiss the dialog.

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).

19.  Right-click the cell containing “=Fields!Category.Value”.

20.  Select Expression ... from the context menu that appears, as depicted in Illustration 41.

Illustration 41: Replacing the Existing Label Expression ...
Illustration 41: Replacing the Existing Label Expression ...

The Expression Editor opens.

21.  Replace the expression within the upper pane (currently “=Fields!Category.Value”) with the following:

=Fields(Parameters!RowGroupBy.Value).Value

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

Illustration 42:  The Expression Editor with Our Substituted Syntax
Illustration 42: The Expression Editor with Our Substituted Syntax

22.  Click OK to accept our modification, and to dismiss the Expression Editor.

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.

23.  Click the Sales Reason textbox to select it.

24.  Right-click the outline that appears, surrounding the box.

25.  Select Copy from the context menu that appears, as depicted in Illustration 43.

Illustration 43:  Copying the Sales Reason Textbox ...
Illustration 43: Copying the Sales Reason Textbox ...

26.  Click into the open space to the right of the Sales Reason label, positioning the cursor approximately as shown with the “X” in Illustration 44.

Illustration 44:  Placing the Cursor for the Paste Operation ...
Illustration 44: Placing the Cursor for the Paste Operation ...

27.  Right-click the canvas where the cursor rests, and select Paste from the context menu that appears, as depicted in Illustration 45.

Illustration 45:  Pasting the Sales Reason Textbox ...
Illustration 45: Pasting the Sales Reason Textbox ...

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

28.  Align the bottom left corner of the new Sales Reason textbox to rest at the upper left corner of the second row group cell (containing our new input of “=Fields(Parameters!RowGroupBy.Value).Value”), as shown in Illustration 46.

Illustration 46:  Aligning the Copied Sales Reason Textbox as a Label for the Second Row Group
Illustration 46: Aligning the Copied Sales Reason Textbox as a Label for the Second Row Group

29.  Right-click the new Sales Reason textbox copy.

30.  Select Expression from the context menu that appears, as depicted in Illustration 47.

Illustration 47:  Select Expression ... from the Context Menu ...
Illustration 47: Select Expression ... from the Context Menu ...

The Expression Editor opens, once again.

31.  Replace the expression within the upper pane (currently “Sales Reason”) with the following:

=Parameters!RowGroupBy.Label

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

Illustration 48:  The Expression Editor with Our Substituted Syntax
Illustration 48: The Expression Editor with Our Substituted Syntax

32.  Click OK to accept our modification, and to dismiss the Expression Editor.

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.

33.  Click the Preview tab, once again.

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.

34.  Select Product within the Group Rows By parameter picklist.

35.  Select Product within the Sort Rows By parameter picklist.

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

Illustration 49:  Our Test Selections Appear ...
Illustration 49: Our Test Selections Appear ...

36.  Click the View Report button to execute the report.

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

Illustration 50:  The New Report (Partial View) Generates for the Chosen Parameter Selections
Illustration 50: The New Report (Partial View) Generates for the Chosen Parameter Selections

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.

37.  Experiment further with the report, if desired.

38.  When finished with the report, click the Layout tab.

39.  Select File -> Save DBJ_OLAP_Report.rdl As ... to save our work, up to this point, to a location where it can be easily accessed for later reference.

40.  Select File -> Exit to leave the design environment, when ready.

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.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved