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.
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:
BrandSales_Product_Family
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.
Illustration 27: The
Grouping and Sorting Properties Dialog – New Product Family Group
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).
Illustration 28: The New
Product Family Group Appears Atop the Rows List Box
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.
Illustration 29: The
Select Properties for the New Product Family Label Textbox
The Textbox
Properties dialog appears.
15.
Type the
following into the Name box of the dialog.
Product_Family
The Textbox
Properties dialog appears, with new Name, as depicted in Illustration
30.
Illustration 30: The
Textbox Properties Dialog for Product_Family
16.
Click OK
to accept changes and close the dialog.
» 
Comment and Contribute
