MSSQL Server Reporting Services: Mastering OLAP Reporting: Extending Conditional Formatting: SWITCH and Drilldown Defaults - Page 6
August 15, 2005
Establish Conditional Formatting for the Store Profit Value
Having created a copy of the functional report, we are now ready to make the enhancements requested by the FoodMart information consumers. Our first modification will establish conditional formatting of the Store Profit value, to meet the requirement that, when negative, it 1) is contained in parentheses and 2) appears in a red (versus the standard black) font. (We will go one step further, as well, and color the value green, if it equals zero, to establish a scenario with yet an additional possible outcome). The purpose, again, is to attract reader attention to line items that did not render a profit. Obviously, the procedures that we undertake to conditionally format the Store Profit value could be applied to other values in the report, as well.
While there are multiple approaches to bringing about the conditional formatting we desire, we will accomplish it using modifications within the basic and advanced textbox properties settings for the Store Profit value. We will begin our procedure from our current position on the Layout tab, within the Report Designer. Here we will apply expressions to properties of the Store Profit value.
1. Right-click the textbox underneath the Store Value column title, currently displaying =Sum(Fields!Store_Profit.Value).
2. Select Properties from the context menu that appears, as shown in Illustration 20.
The Textbox Properties dialog for the Store_Profit textbox opens,
3. In the right, lower portion of the dialog, below the Format Standard list box, and to the immediate right of the Custom box (whose radio button is selected) replace the current format code (C0) with the following expression:
The Textbox Properties dialog appears, with our modification, as depicted in Illustration 21.
4. Click OK to accept our changes, and to close the Textbox Properties dialog.
We are returned to the Layout tab.
The format pattern we have entered into the Custom Format box of the Textbox Properties dialog will, as we shall see, handle the enclosing of negative numbers in parentheses, the first half of the formatting requirement we face. Next, we will use an expression, within the font Color property, to make negative values appear red.
5. With the Store Profit value textbox still selected, select View --> Properties Window from the main menu (if required), as shown in Illustration 22.
The Properties Window appears (I typically "dock" mine to the right side of the Report Designer environment, for easy access).
6. Ensure that the selector atop the Properties Window displays Store_Profit Textbox, as depicted in Illustration 23.
7. Click the Color box in the upper half of the Properties Window, to activate its downward pointing selector button.
8. Click the selector button to enable the Color selection list (the color currently in place is Black).
9. Select Expression from the partially expanded colors list, as shown in Illustration 24.
The Expression Editor appears. Here, as with myriad other object properties throughout Reporting Services, we can use an expression to enact conditional formatting, as we shall see next.
10. Replace "Black" in the Expression box (right half of the Expression Editor)with the following expression:
=Switch(Sum(Fields!Store_Profit.Value) > 0, "Black", Sum(Fields!Store_Profit.Value) = 0, "Green", Sum(Fields!Store_Profit.Value) < 0, "Red")
The Expression box of the Expression Editor appears, with our expression, as depicted in Illustration 25.
11. Click OK to accept our changes, and to close the Textbox Properties dialog.
We are returned to the Layout tab, where we can preview the effects of our handiwork surrounding the Store Profit value.
12. Click the Preview tab, atop the design environment.
The report executes. The values section of the report matrix appears as partially shown in Illustration 26.
We now see that the ("cosmetically calculated") negative values of the Store Profit column appear to have been successfully formatted based upon the conditions we have supplied. This meets the expressed needs of the information consumers in that the negative numbers now appear in red fonts, and enclosed by parentheses, for easy identification.