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!
Comments
- Anonymous
August 10, 2009
The comment has been removed - Anonymous
December 22, 2009
HiWe have Report Builder 2 installed on our Sharepoint farm, but when you go to edit a report in Sharepoint using Reporting Services 2008 SP 1 Integrated mode unless you are sharepoint farm admin, it will only open RB1.The user we are testing against normally has full control at the site where the reports reside.We have tiple checked MS doco on how to configure so RB2 is the default RB to start etc etc but no joy.Any thoughts appreciated. - Anonymous
February 08, 2010
Finally one good resource for SSRS! Already following you. - Anonymous
August 18, 2010
Man, you made my day. Was a tid bit of work to convert to c# but works perfectly. You are a genius. - Anonymous
February 24, 2011
Hi Bob,I came across this article when searching for a way to "preset" the excel colar palatte. As you know, there is a coloring-issue related to SSRS 2008 Excel rendering. Reports render ok but all colors go hay wires when copying from a working book to another.I have tried using the same color scheme(Hex format) for all reports. But when rendered in excel, they all carry different color palatte in Excel. I was wondering if you know a way to setup a standard color palattle in ssrs report so that they all have the same color palette?Thanks,Jane - Anonymous
June 28, 2011
How could you set a heat map per row?if you haveDECLARE @LogTable TABLE (WebServer VARCHAR(10),[Hour] TINYINT, NumberOfUsers decimal)and then you haveINSERT INTO @LogTable VALUES ('WebServer1',0, -1806.58)INSERT INTO @LogTable VALUES ('WebServer1',7,-2022.7)INSERT INTO @LogTable VALUES ('WebServer1',8,-2166.55)INSERT INTO @LogTable VALUES ('WebServer1',9,-2352)INSERT INTO @LogTable VALUES ('WebServer1',10,-1982.55)INSERT INTO @LogTable VALUES ('WebServer1',11,-1436.53)INSERT INTO @LogTable VALUES ('WebServer1',20,-1114.53)INSERT INTO @LogTable VALUES ('WebServer2',0,-1310)INSERT INTO @LogTable VALUES ('WebServer2',7,-1270)INSERT INTO @LogTable VALUES ('WebServer2',8,-1357)INSERT INTO @LogTable VALUES ('WebServer2',9,-1531)INSERT INTO @LogTable VALUES ('WebServer2',10,-1608)INSERT INTO @LogTable VALUES ('WebServer2',11,-1673)INSERT INTO @LogTable VALUES ('WebServer2',20,-1724)How can you create a heat map for WebServer1 and Webserver2 , per row in a matrix - Anonymous
September 19, 2012
The comment has been removed - Anonymous
November 05, 2012
I am using this code since a while, but now for one report it is not working correctly.Some colums have a correct coloring, others show no colors at all !What could be the reason ??? - Anonymous
March 21, 2013
HiI'm trying to get this to work with dates ,do you know if it's possible?=Code.ColorScaleRYG(SUM(Fields!Active.Value), MIN(Fields!Active.Value, "DataSet1"), MAX(Fields!Active.Value, "DataSet1"))Active is the name of a field containing the time and date when a client last was in contact with the server(SCCM 2012) - Anonymous
June 15, 2013
Good one !! - Anonymous
November 05, 2013
I think that the word 'function' is missing from the end of the 'ColorScales.vb.txt' file. - Anonymous
December 10, 2013
How do I change the scale of the colours i.e. my numbers range from 0 to about 1200 and at the moment a number "300" and a number "1100" has the same shade of green.Thanks - Anonymous
March 06, 2014
Maybe you are helped by this workshop which contains a video www.excel-aid.com/excel-color-scales-highlighting-cells-with-color-scales.html - Anonymous
June 03, 2014
The comment has been removed - Anonymous
November 28, 2014
I find that calling a colour function right from SQL makes report writing easier with heat-maps. In your grid or table you can just select your background to be 'colour' which is a hex code the DB can return. In my mind, less of a headache to implement.However, if SSRS is doing aggregating and SQL doesn't know the actual summed values then you're stuck. - Anonymous
August 13, 2015
Using ColorScale3 with the first range over 0.0 to 7.5 from "#FFFFFF" to "#00FFFF" results in everything less than 7.5 being white, and only 7.5+ being cyan. If I change the colour to e.g. "#0080FF", it gradates properly. What is the problem here? Is it all the strange integer math falling over for some obscure reason? Thanks.