Add Excel-like "color scale" conditional formatting to your reports
I’ve been meaning to do this for a long time, and it looks like David Lean beat me to it earlier this year by posting an incredibly thorough four-part discussion of how to do conditional formatting in Reporting Services. I’ve played with his sample code a bit and put together a variation on his theme that meets some additional requirements, which I have found helpful.
The attached sample code is designed to provide the following functionality (the usual disclaimers apply):
· Convenient support for the standard color scales available in Excel 2007
· Support for arbitrary colors, including named colors (“MintCream”)
· Robust handling of null, error, and out-of-range values
· Robust handling of all integral and floating point numeric types
To use them in a report, do the following:
1. Open the Report Properties dialog (right-click on the space around the report body, choose Report Properties)
2. On the Code page, paste in the attached code
3. On the References page, add a reference, click Browse, locate and select System.Drawing.dll, usually in C:\Windows\Microsoft.NET\Framework\v2.0.50727 (this references allows the code to handle named colors)
The simplest and most common usage of these functions is to create a red-yellow-green “heat map” based on the value being displayed in the text box. This is done by setting the text box’s Fill color to an expression similar to the following:
=Code.ColorScaleRYG(Sum(Fields!SalesAmount.Value), 0, 100000)
The arguments 0 and 100000 are the min and max values for the color scale. In this case, the ColorScaleRYG function is used, which assigns red to the low value, green to the high value, and yellow to the value half-way in between.
The min and max values can also be calculated, like this:
=Code.ColorScaleRYG(Sum(Fields!SalesAmount.Value), Min(Fields!SalesAmount.Value, “Dataset1”), Max(Fields!SalesAmount.Value), “Dataset1”))
The argument “Dataset1” defines the scope in which the min or max value is calculated, which must be a parent scope of the current scope.
Note that these colors are not the pure “Red”, “Green”, and “Yellow” colors defined in HTML/.NET/SSRS, but rather the default values used in Excel which are intended to provide a reasonable background for black text. If you don’t like them, it’s easy to specify your own colors instead.
Here’s a full list of the functions provided:
‘ Convenience functions for standard 3-color scales
public function ColorScaleRYG(value, minValue, maxValue) as string
public function ColorScaleGYR(value, minValue, maxValue) as string
public function ColorScaleRYB(value, minValue, maxValue) as string
public function ColorScaleBYR(value, minValue, maxValue) as string
public function ColorScaleRWB(value, minValue, maxValue) as string
public function ColorScaleBWR(value, minValue, maxValue) as string
‘ Convenience functions for standard 2-color scales
public function ColorScaleRY(value, minValue, maxValue) as string
public function ColorScaleYR(value, minValue, maxValue) as string
public function ColorScaleGY(value, minValue, maxValue) as string
public function ColorScaleYG(value, minValue, maxValue) as string
‘ Arbitrary 3-color scale with interpolated midpoint value
public function ColorScale3(value as object, minValue as object, minColor as string, midColor as string, maxValue as object, maxColor as string) as string
‘ Arbitrary 3-color scale with explicit midpoint value
public function ColorScale3(value as object, minValue as object, minColor as string, midValue as object, midColor as string, maxValue as object, maxColor as string) as string
‘ Arbitrary 2-color scale with optional error color (the core function)
public function ColorScale(value as object, minValue as object, minColor as string, maxValue as object, maxColor as string, optional errorColor as string = "Transparent") as string
Enjoy!