Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

Heat maps in SQL Server Reporting Services 2005

Warm up your users this winter with heat maps in SQL Server 2005:


clip_image002


This report was created by one of our testing gurus, Patrice Brassard, and uses a  to show up a dll he has written in c#. The code was used to simplify the process of applying conditional formatting in every cell of the report.


The DLL contains a static public method:


public static string GetColor(decimal redStartVal, decimal yellowStartVal, decimal greenStartVal, decimal val)


where:



  • redStartVal represents the value where a pure red would be used

  • yellowStartVal represents the value where a pure yellow would be used

  • greenStartVal represents the value where a pure green would be used

  • val is the value where you need to get a heat map colour

The function returns a string corresponding to the Hex value of the colour.


The example report above represents test results so Patrice wanted only 100.00% to be shown green (i.e.99.99% should be yellow) so he used the following values:


WLX_SSRS_Utils.HeatMap.GetColor(70.0, 99.99, 100.00, (Sum(Fields!RawPass.Value) / (Sum(Fields!RawPass.Value) + Sum(Fields!RawFail.Value) + Sum(Fields!RawNotRun.Value))) * 100.0)).


Please note that the attached DLL that needs to be on both your dev machine and the SSRS server:



  • On the dev machine (say your PC), it needs to be deployed in %programfiles%\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies

  • On the reporting server, it needs to be deployed in %ProgramFiles%\Microsoft SQL Server\MSSQL.x\Reporting Services\ReportServer\bin


HeatMap.cs