MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Conditional Formatting for OLAP Reports - Page 7

December 20, 2004

19.  Click the Edit button to the right of the highlighted group.

The Grouping and Sorting Properties dialog - General tab appears.

20.  Replace the contents of the Name box atop the tab, BrandSales_Brand_Name, with the following:

Product_Product_Name

21.  In the Group On list, use the selector button to the right of the top line in the list, to modify the existing expression, =Fields!Brand_Name.Value, to the following expression:

=Fields!Product_Product_Name.Value

Illustration 25 depicts scrolling down on the selector to select the replacement expression.

Click for larger image

Illustration 25: Select the New Row Group Expression ...

The Grouping and Sorting Properties dialog - General tab appears, as shown in Illustration 26.


Illustration 26: Grouping and Sorting Properties Dialog - General Tab

Within the Matrix Properties dialog - Group tab, we note that the bottom row group now reflects the name change we made in the Grouping and Sorting Properties dialog.

22.  Click OK to accept modifications, and to close the dialog.

23.  Click OK to close the Matrix Properties dialog.

We return to the Layout tab. We now need to make another modification to continue with the drill-down-related changes requested by the information consumer group.

24.  Right-click the textbox fourth column from the left / right (currently showing "= Fields!Brand_Name.Value" in the Layout view.

25.  Select Properties from the context menu that appears.

The Textbox Properties dialog appears.

26.  Replace "Brand_Name" in the Name box with the following:

Product_Name

27.  Select the following by clicking the selector for Value, just below Name, to replace the current "= Fields!Brand_Name.Value" :

=Fields!Product_Product_Name.Value

The Textbox Properties dialog appears (modifications circled) as depicted in Illustration 27.


Illustration 27: Textbox Properties Dialog - Modifications Circled

28.  Click OK to close the Textbox Properties dialog.

We return to the Layout tab. We have another set of minor modifications to make to complete the structural changes that the intended audience has requested: to remove all except the lowest two current drilldown levels, Product Subcategory and Product Name, which the intended audience wants to see visible together in the new report. For this, we will need to return to the Matrix Properties dialog, Group tab, where we were earlier.

29.  Click anywhere within the Matrix data region to activate the gray column and row headers.

30.  Right-click the upper right corner of the Matrix data region.

31.  Select Properties from the bottom of the context menu that appears, as we did earlier.

The Matrix Properties dialog - General tab appears.

32.  Click the Groups tab.

33.  Click the BrandSales_Product Category item that appears within the Rows group list, as shown in Illustration 28.


Illustration 28: Select BrandSales_Product Category Row Group ...

34.  Click the Delete button to the right of the highlighted group.

The BrandSales_Product Category group disappears.

35.  Click the BrandSales_Product Department item that appears atop the Rows group list.

36.  Click the Delete button to the right of the highlighted group.

The BrandSales_Product Department group disappears.

37.  Click the BrandSales_Product Subcategory item within the Rows group list.

38.  Click the Edit button to the right of the highlighted group.

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

39.  Click the Visibility tab to access it.

40.  Under Initial visibility, select the Visible option by clicking the radio button to its immediate left.

41.  Uncheck Visibility can be toggled by another report item by clicking the checkbox to its immediate left.

The Grouping and Sorting Properties dialog - Visibility tab appears, as shown in Illustration 29.


Illustration 29: Grouping and Sorting Properties Dialog - Visibility Tab

42.  Click OK.

We return to the Matrix Properties dialog - Groups tab.

43.  Click the Product_Product Name item within the Rows group list.

44.  Click the Edit button to the right of the highlighted group.

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

45.  Click the Visibility tab to access it.

46.  Under Initial visibility, select the Visible option by clicking the radio button to its immediate left.

47.  Uncheck Visibility can be toggled by another report item by clicking the checkbox to its immediate left.

48.  Click OK.

We return to the Matrix Properties dialog - Groups tab.

49.  Click OK, again.

We return to the Layout tab in Report Designer. We are now ready to test the report to preview the results of our structural changes.

NOTE: Ensure that only the Product Subcategory and Product Name columns remain, from what was previously the Product hierarchy drilldown, before proceeding.

50.  Click the Preview tab.

51.  Select Food from the parameter picklist that appears atop the report on the Preview tab.

52.  Click the View Report button to execute the report.

The report runs and returns the data associated with the Food Product Family that resides in the MSAS Sales cube. At this point, we see that the new lowest level of the row axis, Product Name, appears. We note, too, the appearance of Product Subcategory level, and that the other levels previously in place have disappeared, as the information consumers had requested.

A portion of our view of the report at this stage appears in Illustration 30.


Illustration 30: The AdHocCondFormat_Foodmart Sales Report (Partial View) with Layout Modifications








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers