MSSQL Server Reporting Services: Mastering OLAP Reporting: Extending Conditional Formatting: SWITCH and Drilldown Defaults - Page 7
August 15, 2005
Establish "Conditional Drilldown Defaults" for Presentation Purposes
Having established conditional formatting of the Store Profit value, we are now ready to enhance the report further to meet the second of the business requirements. We recall that the information consumers, who intend to use this report to focus upon the Drink and Food categories of the FoodMart product offerings, have asked that the report present these two Product Families with a "drilled down" state as the default. Moreover, they want the Non-Consumable products to appear, by default, in a "rolled up" state, providing a single line item for Non-Consumables on the face of the report. This drill down presentation will allow emphasis upon the analysis upon the Product Families, Drink and Food, while at the same time providing the Non-Consumables total to allow them to agree total product sales to corresponding summary values in other system reports. The consumers have agreed that the flexibility of ad hoc drilldown on the summary Non-Consumables line would add value to the report, as well.
Providing the "conditional default drill downs" as the information consumers have requested will afford us an opportunity to extend the concept of conditional formatting, once again, to meet a business need in a relatively sophisticated way. Such opportunities are legion, as many of us are learning, within Reporting Services and the integrated Microsoft BI Solution in general. We will undertake the requirement in the steps that follow, beginning with the establishment of Product Family as a group within the existing matrix data region.
1. Return to the Layout view by clicking the Layout tab.
2. Click at some point within the title textbox of the report (containing the label Foodmart Sales), to make the row and column headers of the matrix data region visible.
3. Right-click the upper left corner of the matrix, as we did in the earlier section.
4. Select Properties from the context menu that appears.
The Matrix Properties dialog opens, defaulted to the General tab.
5. Click the Groups tab.
Four groups appear in the Rows list box, and two added groups, along with the default Static Group, appear in the Columns list box. Here we will add a Product Family group, upon which we will base the default drilldown attributes that the consumers have requested.
6. Click the Add button to the right of the Rows list box.
The Grouping and Sorting Properties dialog for the new group, named BrandSales_RowGroup5 (or similar) by default, opens to the General tab.
7. Type the following into the Name box on the General tab, replacing the existing name:
8. Select Fields!Product_Family.Value within the dropdown selector of the Expression list, in the Group on section.
The Grouping and Sorting Properties dialog appears as shown in Illustration 27.
9. Click OK to accept changes and close the Grouping and Sorting Properties dialog for the BrandSales_Product_Family group.
We are returned to the Matrix Properties dialog - Group tab. We need, at this point, to arrange the new group to the left of the report and thus to move it to the top in the Rows list box.
10. Click the new group, BrandSales_Product_Family, in the Rows list box to select it, if necessary.
11. Click the Up button to the right of the Rows list box enough times to raise the BrandSales_Product_Family to the top of the list box.
The BrandSales_Product_Family group appears in the Rows list box as depicted in Illustration 28 (relevant portion of the Matrix Properties dialog Group tab).
12. Click the OK button to accept the rearrangement of the groups, and to close the Matrix Properties dialog.
We are leaving the groups momentarily, and returning to the Layout view, to name the textbox that was created by our addition of the Product Family group.
13. Right-click the new Product Family textbox (the leftmost of the label textboxes).
14. Select Properties from the context menu that appears, as shown in Illustration 29.
The Textbox Properties dialog appears.
15. Type the following into the Name box of the dialog.
The Textbox Properties dialog appears, with new Name, as depicted in Illustration 30.
16. Click OK to accept changes and close the dialog.