Sunday, April 25, 2010

SSRS : Dynamically Changing Cell Colour

SSRS : Dynamically Changing Cell Colour

Reporting Services dynamic formatting is controlled by expressions. The SWITCH function is one method of implementing logic inside an expression (like TSQL's CASE statement).

To quote Books Online , SWITCH -
' Evaluates a list of expressions, and returns the value associated with the first condition that returns TRUE. Switch can have one or more condition/value pairs. '
So applying this in an expression to the BackgroundColor property of a cell  >

=Switch(First(Fields!Elapsed_Minutes.Value) < 30, "Green", First(Fields!Elapsed_Minutes.Value) < 60, "Yellow", First(Fields!Elapsed_Minutes.Value) >= 60, "Red", 1=1, "Transparent")

Breaking this example down, it uses the Elapsed_Minutes column and uses GREEN for values less than 30 minutes, YELLOW for values less than 60 minutes and RED for values over 60.
The 1=1 argument becomes true if none of the previous arguments are satisfied i.e Elapsed_Minutes is NULL / not present.

No comments: