MSSQL Server Reporting Services: Mastering OLAP Reporting: Extending Conditional Formatting: SWITCH and Drilldown Defaults - Page 8
August 15, 2005
Now that we have put the primary group, from which we will effect our default drill down presentation, in place and labeled, we will turn to the control of visibility in the next group level, BrandSales_Product_Department. We will do so from the Matrix Properties dialog Group tab, to which we will return once again.
17. From the Layout view, once again, 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, as we did earlier.
18. Right-click the upper left corner of the matrix.
19. Select Properties from the context menu that appears.
The Matrix Properties dialog opens, defaulted to the General tab, once again.
20. Click the Groups tab.
21. Click the BrandSales_Product_Department group in the Rows list box to select it.
22. Click Edit.
The Grouping and Sorting Properties dialog for the group opens.
23. Click the Visibility tab.
24. Under Initial Visibility, click the radio button to the immediate left of the Expression field, to select it.
25. Type the following expression into the Expression field:
(Note that the logic behind "visibility" seems reversed. It is as if we have established a drilled down state based upon the return of a "false" Boolean response. As we shall see, we receive a collapsed state for the Non-Consumable Product Family.)
26. Click the checkbox the immediate left of the label "Visibility can be toggled by another report item" below the Expression field.
A checkmark appears in the checkbox, and the Report Item selector becomes activated.
27. Select the Product_Family item in the selector.
This step allows ad hoc drilldown when the default state is collapsed (as will be the case for the Non-Consumable family.).
The Visibility tab - Grouping and Sorting Properties dialog appears as shown in Illustration 31.
28. Click OK to accept changes and to return to the Groups tab of the Matrix Properties dialog.
29. Click OK to accept modifications and close the Matrix Properties dialog.
Finally, let's modify the title of the report to represent its new use, with a caption similar to the one that appeared in the draft presented at the conclusion of business requirements gathering for the report. In addition, we will add a report total underneath the rolled up "reconciling" line item for added report utility.
30. Right-click the textbox containing the report title FoodMart Sales.
31. Select Properties from the context menu that appears, as we did in an earlier step for the Product Family textbox.
The Textbox Properties dialog appears.
32. Type the following into the Value box of the dialog:
FoodMart Consumables Analysis
The Textbox Properties dialog appears, with new Value, as depicted in Illustration 32.
33. Click OK to accept changes and close the dialog.
34. Right-click the Product Family (leftmost) label textbox, once again.
35. Select Subtotal from the context menu that appears, as shown in Illustration 33.
We return again to the Layout view in Report Designer, where we see the new Total in evidence. We are now ready to verify the results of our work.