MDX in Analysis Services: Create a Cube-Based Hierarchical Picklist - Page 9

July 26, 2004

Designing the Report Layout

We have put core and parameter picklist generation queries into place, to designate the dataset from which the report will select the data it presents, as well as to facilitate filtering upon specific location within the Store hierarchy of our cube. We now need to turn to an abbreviated physical Layout of the report.

1.  Click the Layout tab.

The Layout tab presents the Design Surface, as depicted in Illustration 37. We will draft a quick blank report, simply to illustrate the operation of our MSAS-based parameter picklist, and associated components.

Click for larger image

Illustration 37: The Layout Tab and Design Surface

2.  Click the Toolbox icon, atop the Report Designer window, (see Illustration 38) to open the Toolbox, as necessary.

Illustration 38: The Toolbox Icon

The Toolbox appears to the left of the Layout tab.

3.  Click the Matrix data region icon, within the Toolbox, as depicted in Illustration 39.

Illustration 39: Toolbox, with Matrix Icon Selected

4.  Click on the design surface (a small matrix icon will appear in place of the usual mouse pointer).

The Report Designer renders a two-columned matrix, straddling the Design Surface, as shown in Illustration 40.

Illustration 40: The Matrix Appears on the Design Surface

5.  Right-click the upper left corner (within the grey "header" area) of the matrix.

6.  Select Properties from the context menu that appears, as depicted in Illustration 41.

Illustration 41: Select Properties from the Context Menu

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

7.  Marry the dataset we created earlier to the matrix data region by selecting FoodMart 2000 in the selector, as shown (circled) in Illustration 42.

Illustration 42: Select FoodMart 2000 in the Dataset Selector - General Tab

8.  Click the Group tab on the Matrix Properties dialog.

9.  Ensuring that the matrix1_RowGroup1 line is highlighted, click the Edit button to the right of the Rows box, as depicted in Illustration 43.

Illustration 43: Editing the Row Group

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

10.  Change the row group title in the Name box to the following:


11.  In the Group On: section, in the top field, select =Fields!Measures_RS_PX_StoreGeogMSASName_Hier.Value, as shown in Illustration 44.

Illustration 44: Selecting the Value for Row Grouping