Adding Conditional Formatting (Reporting Services)

You can design a report so that different styles are applied to report items or properties based on the data in the report. You can build conditional expressions using any feature supported by expressions, including built-in report and aggregate functions, built-in collections, and custom code. For example, you can display negative numbers in red or change the background colors on a table. To make styles conditional, use an expression instead of a static value for the style properties of the item. At run time, the report processor evaluates the expression and substitutes the result for the property value, just as if you had set the property to a static value at design time. When the report is rendered, the run-time value is used.

For more information about what you can include in an expression, see Understanding Expression References (Reporting Services).


Changing Text Color Based on an Expression

To make the contents of a text box render negative values in red for a field called Profit, open the Properties pane and use the following expression in the Color property:

=IIF(Fields!Profit.Value < 0, "Red", "Black")

For more information about the Properties pane, see Working with Report Designer in Business Intelligence Development Studio.

Creating a Green-Bar Report

To alternate the background color for every other row in a table, you can set the BackgroundColor property for each text box in the row to the same conditional expression. This technique works for detail rows only in a table with no groups.

=IIF(RowNumber(Nothing) MOD 2, "PaleGreen", "White")

Controlling Colors in a Pie Chart

To specify colors for a Shape chart, you can use custom code to control the order that colors are mapped to data point values. This helps you use consistent colors for multiple charts that have the same category groups. For more information, see How to: Specify Consistent Colors across Multiple Shape Charts.