MSSQL Server Reporting Services : Mastering OLAP Reporting: Drilling Through Using MDX - Page 8
October 20, 2005
The information consumers have also requested that the Product Name appear in the report; we will now add another group, subordinate to the Product Brand Name group, while we are "under the hood" with regard to the matrix properties.
42. Click the Add button to the right of the rows list on the Groups tab.
The Grouping and Sorting Properties dialog appears, once again.
43. Type the following into the Name box of the dialog.
44. Click the top row in the Group on list, just beneath the Expression label, to expose the selector.
45. Using the selector, select the following from the picklist provided to populate the top row of the list:
The selector list, with our selection circled, appears as depicted in Illustration 42.
Click for larger image
The Grouping and Sorting Properties dialog, General tab, with our input, appears as shown in Illustration 43.
46. Click the Sorting tab.
47. Click the top row in the Sort on list, just beneath the Expression label, to expose the selector.
48. Using the selector, select the following from the picklist provided to populate the top row of the list:
The Grouping and Sorting Properties dialog, Sorting tab, with our input, appears as depicted in Illustration 44.
49. Click OK to accept our input.
The Grouping and Sorting Properties dialog closes, returning us to the Groups tab of the Matrix Properties dialog, which now appears as shown in Illustration 45.
50. Click OK to accept changes and to close the Matrix Properties dialog.
We return to the Layout tab, where our report appears similar to that depicted in Illustration 46.
Let's rename the report, and eliminate all except the specified measure, Store Sales.
51. Right-click the upper left textbox in the matrix, where we currently see the title FoodMart Sales in a white background.
52. Select Expression... from the context menu that appears, as shown in Illustration 47.
The Edit Expression dialog appears.
53. Replace FoodMart Sales, within the Expression pane, with the following new title:
Product Summary - by Brand
The Edit Expression dialog appears as depicted in Illustration 48.
54. Click OK to accept the new expression and to close the Edit Expression dialog.
55. With the textbox containing our new title still selected, replace the current font size with "16," via the selector on the Report Formatting toolbar, located between the Report Designer and the main menu, as shown in Illustration 49.
NOTE: If the Report Formatting toolbar does not appear, summon it by selecting View --> Toolbars --> Report Formatting from the main menu, as partially depicted in Illustration 50.
56. Click the Store Profit measure expression, under the Store Profit label, once to select it, as shown in Illustration 51.
57. Press the Delete key on the PC to delete the Store Profit measure column entirely.
58. Perform the same select and delete process for the Store Cost measure.
The Layout tab for the report appears similar to that depicted in Illustration 52.
59. Open the Fields list for the ProductData Dataset we have defined. (If it is not docked in the development environment, open it by simply selecting View --> Fields from the main menu, as shown in Illustration 53).
60. Right-click the Store Profit calculated field (it appears at the bottom of the fields list).
61. Select Delete from the context menu that appears, as depicted in Illustration 54.
Let's take a look at the results of our handiwork so far.
62. Click the Preview tab to execute the report.
RS022_MDX_DRILLTHROUGH executes briefly, and then returns data. Our report appears similar to that shown in Illustration 55.
NOTE: Widen the columns as desired to prevent crowding / word wrapping in the data fields.
63. Select File --> Save All to save all work to the present point.
We have now completed preparation of the primary report, which we will enhance in the section that follows to allow for drillthrough to a targeted detail report, which we will also create and align with the primary report.