Mastering OLAP Reports: Parameterized Grouping - Page 9

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

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

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

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

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

17.
Leaving the Direction setting at its default of Ascending,
click OK to accept our input and to dismiss the Grouping and SortingProperties 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 ...

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

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