SSRS Color Gradient

Question

I've been able to figure out how to make certain values the certain colors I would like. However, I'd really like to be able to create a color gradient so that it's more of a gradual change between each value.

0 = white

from white to green between 1 and 15,

gradient from green to yellow between 16 and 25,

and gradient from yellow to red between 26 and 35,

anything above 35 is red.

This is the code I have in the background fill expression:

=SWITCH(
(Sum(Fields!Total_Transaction_Count.Value) / CountDistinct(Fields!TransUserNumber.Value)) = 0, "White",
((Sum(Fields!Total_Transaction_Count.Value) / CountDistinct(Fields!TransUserNumber.Value)) >= 1 and 
(Sum(Fields!Total_Transaction_Count.Value) / CountDistinct(Fields!TransUserNumber.Value)) <= 15), "Green", 
((Sum(Fields!Total_Transaction_Count.Value) / CountDistinct(Fields!TransUserNumber.Value)) >= 16 and 
(Sum(Fields!Total_Transaction_Count.Value) / CountDistinct(Fields!TransUserNumber.Value)) <= 25), "Yellow", 
((Sum(Fields!Total_Transaction_Count.Value) / CountDistinct(Fields!TransUserNumber.Value)) >= 26 and 
(Sum(Fields!Total_Transaction_Count.Value) / CountDistinct(Fields!TransUserNumber.Value))) <= 35, "Orange", 
(Sum(Fields!Total_Transaction_Count.Value) / CountDistinct(Fields!TransUserNumber.Value)) > 35, "Red")

This is the matrix I have so far

Alan Schofield Alan Schofield · Accepted Answer · 2020-07-01T16:36:36

Take a look at this answer I wrote a while back. It's for a chart but the principle should be the same.

the basic idea is to calculate the colour in SQL and then use that to set the color properties in SSRS

Applying Custom Gradient to Chart Based on Count of Value

Keeping it all in SSRS

If you want to keep this within the report you could write a function to do the calculation.

For a very simple red gradient, it might look something like this..

Public Function CalcRGB (minVal as double, maxVal as double, actualVal as double) as String

Dim RedValue as integer
Dim GreenValue as integer
Dim BlueValue as integer

RedValue = ((actualVal - minVal) / (maxVal - minVal)) * 256
GreenValue = 0
BlueValue = 0

dim result as string

result = "#" & RedValue.ToString("X2") & greenValue.ToString("X2") & BlueValue.ToString("X2")

Return result

End Function

In this function I have set green and blue to 0 but these could be calculated too based on requirements.

To use this function as a background colour, set the Background Color property to something like

=Code.CalcRGB(
              MIN(Fields!myColumn.Value),
              MAX(Fields!myColumn.Value),
              Fields!myColumn.Value
              )

SSRS Color Gradient

1 Answers