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!

ColorScales.vb.txt

Comments (16)

  1. mjhillman says:

    Great tip!  Solved a problem I was having in pointing out high and low values in a matrix.

  2. sqlguy7777 says:

    Hi

    We 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.

  3. gomiunik says:

    Finally one good resource for SSRS! Already following you.

  4. Levi Page says:

    Man, you made my day. Was a tid bit of work to convert to c# but works perfectly. You are a genius.

  5. Jane says:

    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

  6. Xomaly says:

    How could you set a heat map per row?

    if you have

    DECLARE @LogTable TABLE (WebServer VARCHAR(10),[Hour] TINYINT, NumberOfUsers decimal)

    and then you have

    INSERT 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

  7. Simon Johnson says:

    I have used a parameter linked to a text box button to toggle the heat map on a report, but it still shows Red for those 0 values. I just put If Report.Parameters!pShowHideHeatMap.value = "Hide" Then

        ColorScale = "White"
    
    End If 
    

    …at the end of the ColorScale function. Does anyone know how I can fix the reds / the right place to put the code?

  8. Frank Schollaert says:

    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 ???

  9. Thomas Larsen says:

    Hi

    I'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)

  10. M Kemp says:

    I think that the word 'function' is missing from the end of the 'ColorScales.vb.txt' file.

  11. Newby says:

    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

  12. EmiliaEstelle says:

    Maybe you are helped by this workshop which contains a video  http://www.excel-aid.com/excel-color-scales-highlighting-cells-with-color-scales.html

  13. JSDragonslayer says:

    Fantastic resource, thank you. M Kemp is right that users need to add "Function" to the last line of the code.

  14. Liam says:

    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.

  15. DB says:

    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.