Mastering OLAP Reports: Parameterized Grouping - Page 6
September 23, 2008
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:
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.
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.
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.
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.