How to create heat map on a table in SSRS?

Question

How can I create like this in SSRS? The color will change from red to green based on a value in a row (Underwriter). And all that in a group:

Answer 1

You can do this by right clicking on the individual cells and setting the fill colour based on an expression:

In the Image below I've mistakingly put "==" where it should be "="

To give you an example the following:

was created using the following expressions for the ID30, ID60 and ID90 fields respectively:

ID30:

=IIF(Fields!ID30.Value>="0" And Fields!ID30.Value<="100" ,"#c6c626",IIF(Fields!ID30.Value>="100" And Fields!ID30.Value<="200" ,"#c6c627",IIF(Fields!ID30.Value>="200","#9e2424","red")))

ID60:

=IIF(Fields!ID60.Value>="0" And Fields!ID60.Value<="100" ,"#c6c626",IIF(Fields!ID60.Value>="100" And Fields!ID60.Value<="200" ,"#c6c627",IIF(Fields!ID60.Value>="200","#9e2424","red")))

ID90:

=IIF(Fields!ID90.Value>="0" And Fields!ID90.Value<="100" ,"#c6c626",IIF(Fields!ID90.Value>="100" And Fields!ID90.Value<="200" ,"#c6c627",IIF(Fields!ID90.Value>="200","#9e2424","red")))

Answer 2

I came up with a way to get the color to actually be gradient, rather then based on nested IF statements. This method uses theoretical min and max values (you could set actual min and max values as variables if it is important that these are precise) and rgb integer values and results in a color hex code for SSRS.

Go to Report Properties>Code and paste in this function:

Public Function HeatMap(ByVal Value As Double _
, ByVal MinVal As Double _
, ByVal MaxVal As Double _
, ByVal RValLo As Double _
, ByVal GValLo As Double _
, ByVal BValLo As Double _
, ByVal RValHi As Double _
, ByVal GValHi As Double _
, ByVal BValHi As Double _
) As String
Dim DiffPercent As Double
Dim RNew As Integer
Dim GNew As Integer
Dim BNew As Integer

Dim HeatMapColor As String
If Value = Nothing Then
    RNew = 255
    GNew = 255
    BNew = 255
ElseIf Value <= MinVal Then
    RNew = RValLo
    GNew = GValLo
    BNew = BValLo
ElseIf Value >= MaxVal Then
    RNew = RValHi
    GNew = GValHi
    BNew = BValHi
Else
    DiffPercent = (Value - MinVal) / (MaxVal - MinVal)
    RNew = RValLo - Round((RValLo - RValHi) * DiffPercent, 0)
    GNew = GValLo - Round((GValLo - GValHi) * DiffPercent, 0)
    BNew = BValLo - Round((BValLo - BValHi) * DiffPercent, 0)
End If

HeatMapColor = "#" & Hex(RNew) & Hex(GNew) & Hex(BNew)

   HeatMap = HeatMapColor
End Function

Then in the cell where the heat map values are calculated, use the function in the background format expression, for example:

=Code.HeatMap(Sum(Fields!Orders.Value) / Sum(Fields!Orders.Value, "Tablix1"), 0, .2, 255, 255, 255, 99, 190, 123)

In this example, there are theoretical low and high values of 0 and .2 (0% and 20%) and the color will go between white on the low side and a green shade on the high side. Anything less than the min gets the min color and anything greater than the max gets the max color. This would also work if you want to go between two colors on the color wheel and if you wanted to go with something like red for negative values, white for zero, and green for positive values, you just use an IF statement and use the function twice, once for negative values and once for >= 0. Just substitute rgb values as necessary.