Procedure
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.
Illustration 20:
Accessing the Field Properties
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.
Illustration 21: Custom
Formatting Syntax in the Textbox Properties Dialog
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.
Illustration 22: Select View
--> Properties Window from the Main Menu (Partial View) ...
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.
Illustration 23:
Properties Window for the Store_Profit Textbox (Partial View) ...
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.
Illustration 24: Select
Expression from Atop the Colors List (Partially Expanded)
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.
Illustration 25:
Expression Appears in the Expression Box ...
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.
Illustration 26: The
Values Section of the Matrix (Partial View)
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.
» 
Comment and Contribute
