Add Parameterized Conditional Formatting to the Report Structure
Parameterized
conditional formatting
forms the nucleus of our session. There are several steps, and multiple report
components, involved in bringing about the capability that the information
consumers have requested. These steps can be done in more than one way, and in
more than one order. We will first perform standard conditional formatting, to
make the process clear, and then we will add parameterization as an independent
step, once we have seen the operation of conditional formatting in its simplest
form.
Add
Simple Conditional Formatting to the Report
First, we
will add conditional formatting in a way that might be adequate where
formatting is based upon fixed criteria, which is applied each time the report
is run. The values in the report change over time, in most cases, and the
application of the criteria to the report with each execution means that values
meeting the criteria we have designated will be brought to attention of the
user of the information.
Let's
return to the Layout tab of the Report Designer to get started.
1. Click the Layout tab.
2. Click the Profit Margin
textbox, within which we made the changes to the calculation in our last
section, to select it.
3. Leaving the Profit Margin
textbox selected, open the Properties window for the textbox.
4. In the Color property box, select
<Expression...> with the downward arrow, as shown in Illustration
40.
Illustration 40: Select
<Expression...> from the Color Property Selector
The Edit
Expression dialog appears.
5.
Change the
expression that appears in the Expression box, "Black," to the
following:
=IIF(Sum( Fields!Store_Profit.Value)/Sum(Fields!Store_Sales.Value) < .60 , "Red", "Black")
With
this expression, we are simply specifying "if the Store Margin evaluates
to less than .60 (sixty-percent), then make the font color red for the value;
otherwise leave it black." Remember that this is a simple use of
conditional formatting, and although the data values themselves may change with
each new report execution, the criteria itself will remain fixed.
The
Edit Expression dialog appears as depicted in Illustration 41.
Illustration 41: Simple
Conditional Formatting Logic in the Edit Expression Dialog
6.
Click OK to
accept the modification, and to return to the Layout tab.
Let's
execute the report, once again, to verify the effectiveness of our work.
7.
Click the Preview
tab.
8.
Select Food
from the parameter picklist that appears atop the report on the Preview
tab.
9.
Click the View
Report button to execute the report.
The
report runs and returns the data we have requested. We note the effect of the
conditional formatting expression we have installed, as partially shown in Illustration
42.
Illustration 42: Report (Partial View) with Simple Conditional
Formatting in Place
Our
expression appears to have been effective in rendering a simple conditional
formatting solution. In the next section, we will expand our solution to
include parameterization, and thus support the requirement of the
information consumers to be able to specify the criteria at runtime, rather
then having the conditional formatting apply to a fixed threshold (sixty
percent, in our example) that is "hard coded" into the expression.
» 