MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Conditional Formatting for OLAP Reports - Page 9
December 20, 2004
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.
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.
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.
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.