Applying Custom Gradient to Chart Based on Count of Value

Question

I am taking the hours in a day (1-24) and for each hour I am counting the number of requests that are made. I have an SSRS report that I'd like to color a chart based on the count of that value. This should be dynamic so that the highest count will be darker than the rest and it will decrease the gradient down to the lowest value which would be the lightest color.

Because the count is variable, can change, and has the potential for any number, I can't simply say "if count > 1 then '#ffffff', if count > 2 then '#fffffb', etc."

Standard SSRS color palettes seems to randomize what color gets applied to where in the chart so that is off the table.

Hoping someone else has encountered this and can walk me through what I need to do.

Thank you in advance

Alan Schofield Alan Schofield · Accepted Answer · 2020-01-20T13:38:10

I've done this in the past, without knowing what your data looks like or what type of chart you are using I can't give a complete answer. The following should however, give you a good start and some idea on how to approach the problem.

The process goes as follows.

Get summary data for you chart (aggregated ready to display)
Find the highest count across all hours the range of data to be displayed
Calculate the RGB values for the desired colour
Convert these RGB Values to Hex so we can use them directly in the chart

Almost the entire process is done in SQL, the chart simply uses the HexColour field that we generate in step 4 above, as the series color.

Before we start, we'll need a function to do the RGB to HEX conversion, here the code

CREATE FUNCTION [ConvertRGBValuesToHexColour] (@R  int, @G int, @B int)
RETURNS varchar(7)
AS
BEGIN
    RETURN '#' + RIGHT(CONVERT(VARCHAR(11), CONVERT(BINARY(1),@R,0) + CONVERT(BINARY(1),@G,0) +CONVERT(BINARY(1),@B,0) , 1),6)
END

Here's the complete code with a few comments to explain

-- Generate some test data
DECLARE @t TABLE (HourNumber int, myCount int)
INSERT INTO @t VALUES
    (1, 3), (2,4), (3,10), (20,2), (21,15), (24,1)

-- some variables to help calculate the range of values we have
DECLARE @MaxCount float
DECLARE @Multiplier float 

-- get the highest count in our data (15 in hour 20 in this case)
SELECT @MaxCount = MAX(myCount) FROM @t 
SET @Multiplier = 100/@MaxCount

-- Get the base data plus a column continaing the percentage of the maximum (pc)
select * , myCount * @Multiplier as pc into #c from @t

-- Use the pc column to set the green and blue values (adjust as you want)
-- include a null column to store the HexColor
SELECT 
    HourNumber, myCount
    , Red = 255 -- Red: always 255
    , Green = 255 - ((30.00/100.00)*pc) -- Green: gives range from 255 - 225 for 0 to 100%. Anything over 100% will be set to 255 in next statement
    , Blue = 254 - ((254.00/100.00)*pc) -- Blue: Give range of 254 - 0 for 0 to 100%
    , CAST(NULL AS char(7)) as HexColour
    , pc
into #r
 FROM #c 

-- update the HexColour column
UPDATE #r SET HexColour =  dbo.ConvertRGBValuesToHexColour (Red, CASE WHEN Green <225 THEN 225 ELSE Green END , Blue) 

-- select the final results
SELECT * FROM #r

The final output will look like this. (we don't need the RGB values or the pc column in the final output but I left in for clarity)

HourNumber  myCount Red Green   Blue    HexColour   pc
1           3       255 249     203.2   #FFF9CB     20
2           4       255 247     186.26  #FFF7BA     26.66
3           10      255 235     84.66   #FFEB54     66.66
20          2       255 251     220.13  #FFFBDC     13.33
21          15      255 225     0       #FFE100     100
24          1       255 253     237.06  #FFFDED     6.66

If you follow this example, add a column chart to your report, set the values to myCount and add a Category Group for HourNumber

Note (I also added a table in the image below for testing purposes)

Now right-slick on one of the columns in the chart - choose series properties and set the fill color to =Fields!HexColour.Value

The final output looks like this. note This uses a yellow range, just adjust the three RGB column calculations to get a range that suits your needs. Experiment with different range values too (e.g. the 30 in the Green column calculation) to make the range of colours larger or smaller.

Hopefully this gives you enough to progress.

Applying Custom Gradient to Chart Based on Count of Value

1 Answers