MSSQL Server Reporting Services : Mastering OLAP Reporting: Ad Hoc TopCount and BottomCount Parameters - Page 12

May 16, 2005

Modifying the Report's Row Axis to Align to Business Requirements

Recall that the information consumers requested, among other changes, that we modify the report to display Product Name in the rows of the report. After removing the drilldown levels that previously existed, we left the report's Matrix Properties dialog with a single group, BrandSales_Brand_Name, although we knew that this was not the field that we ultimately wished to group upon, nor to display. My reasoning here was to leave the group in place, and then return to modify it, and re-point it to the Product Name field (which did not exist in the Dataset, anyway, until several steps after our modification of the groups).

Let's return to the Groups tab of the Matrix Properties dialog to make the first of two final modifications to the report.

1.  Click the Layout tab to return to the Layout of the report.

2.  Click at some point within the label textbox of the report (containing the label Foodmart Sales), as we did earlier, to make the row and column headers of the matrix data region visible.

3.  Right-click the upper left corner of the matrix, once again.

4.  Select Properties from the context menu that appears.

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

5.  Click the Groups tab.

6.  Click the single entry in the Rows list, BrandSales_Brand_Name, to select it.

7.  Click Edit.

The Grouping and Sorting dialog opens.

8.  Replace the current text in the Name box, BrandSales_Brand_Name, to the following:


9.  In the top row of the Group on list, select the following expression:


The Grouping and Sorting dialog, containing our modified input, appears as depicted in Illustration 52.

Illustration 52: The Modified Row Group

10.  Click OK to accept changes, and to return to the Groups tab of the Matrix Properties dialog.

The Groups tab appears as shown in Illustration 53.

Illustration 53: The Renamed Row Group Appears

11.  Click OK to close the Groups tab, together with the Matrix Properties dialog, and to return to the Layout tab.

12.  Right-click the textbox containing the current row definition, currently shown as =Fields!Brand_Name.Value,

13.  Select Expression from the context menu that appears, as depicted in Illustration 54.

Illustration 54: Selecting the Textbox Defining the Row Display for Modification

The Edit Expression dialog opens.

14.  Replace the existing expression in the Expression pane of the dialog with the following (you might also select it from the tree on the left):

= Fields!Product_Product_Name.Value

The Edit Expression dialog appears as shown in Illustration 55.

Illustration 55: Edit Expression Dialog, with our Replacement

15.  Click OK to accept our replacement, and to close the Edit Expression dialog.