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 ...
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
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
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 ...
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.
» 
Comment and Contribute
