SQL SSRS Color Code / Create a heatmap out of a tablix in SSRS (color in tablix rows based on value)

Question

so I created this simple tablix that looks like this

Is there any way to add an expression to this tablix so that the rows are color coded based on a base value.

So the way that I am envisioning it is, the base value is the column Avg Value and I will set the background color of that column grey.

Then I would want every Running AOV to be color coded according to its difference from that base value. So for example, in that tablix row 5, the base value would be $88.

Then the RunningAOV1st is $77 so it would be a red color. RunningAOVTwo is $84 so it would be a very slightly light red. Next, RunningAOVThree is $87 so it is even more lighter version of red. If it was something like $90, it would be a very light shade of green.

Is this possible? I'm just assuming I need to put some expression for the background color of the cell, but I am not sure what the expression would be.

Answer 1

This answer take a slightly different approach to yours. I had a similar scenario and opted to do the colour calculation in SQL. I just found it easier that way, although you could convert this to a custom code function in your report quite easily I think.

Here's a sample of a dataset from a report that coloured cells based on the age of some data. The age has already bee precaluclated so the AGe column referenced here is just a number. If the age was over 90 days then it would always show as solid red.

There's part of the dataset query

SELECT 
     lp.*
    , 255 as Red-- Red: always 255
    , 255 - (Age/3) AS Green -- Green: gives range from 255 - 225 for 0 to 90 days. Anything over 90 will be set to 255 in next statement
    , 254 - (Age * (254/90)) AS Blue -- Blue: Give range of 254 - 0 for 0 to 90 days
    , cast(NULL as varchar (7)) AS HexColour
INTO #t 
 FROM #d lp
    JOIN Dim.Geography g ON lp.CountryID = g.CountryID


UPDATE #t SET HexColour =  [fn].[ConvertRGBValuesToHexColour] (Red, CASE WHEN Green <225 THEN 225 ELSE Green END , Blue) 

SELECT * FROM #t

The above calculated values for red green and blue based on the age column, we also add a column to stored the final hex value and update that based on a function (below). The idea is that the HexColor column can be referenced directly as the color property in the report design.

Below is the function to convert the RGB values into a hex value for consumption in the report.

CREATE FUNCTION [fn].[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

Answer 2

You could right click on the text-box for that data and open the Properties. Then on the Fill tab click the "fx" and open the Expression box. Then set the background color based on an IIF statement for the value you wish.

Answer 3

I would suggest to have a table to define the color coding based on a min and max value. Created a temp table to show how it could be done, i didn't get all the combinations you have, but using a sample dataset

SQL used

        select * INTO #Data
        from (
        Select 1 AS Counts,75 AS Value,'Total Orders' AS Category UNION ALL
        Select 1 AS Counts,250 AS Value,'Avg Orders' AS Category UNION ALL
        Select 1 AS Counts,10 AS Value,'Avg Value' AS Category UNION ALL
        Select 1 AS Counts,13 AS Value,'Running 1st' AS Category UNION ALL
        Select 1 AS Counts,'' AS Value,'Running 2nd' AS Category UNION ALL
        Select 1 AS Counts,'' AS Value,'Running 3rd' AS Category UNION ALL
        Select 2 AS Counts,23 AS Value,'Total Orders' AS Category UNION ALL
        Select 2 AS Counts,46 AS Value,'Avg Orders' AS Category UNION ALL
        Select 2 AS Counts,30 AS Value,'Avg Value' AS Category UNION ALL
        Select 2 AS Counts,34 AS Value,'Running 1st' AS Category UNION ALL
        Select 2 AS Counts,'' AS Value,'Running 2nd' AS Category UNION ALL
        Select 2 AS Counts,'' AS Value,'Running 3rd' AS Category UNION ALL
        Select 3 AS Counts,'' AS Value,'Total Orders' AS Category UNION ALL
        Select 3 AS Counts,23 AS Value,'Avg Orders' AS Category UNION ALL
        Select 3 AS Counts,55 AS Value,'Avg Value' AS Category UNION ALL
        Select 3 AS Counts,67 AS Value,'Running 1st' AS Category UNION ALL
        Select 3 AS Counts,77 AS Value,'Running 2nd' AS Category UNION ALL
        Select 3 AS Counts,'' AS Value,'Running 3rd' AS Category  ) a

        select * INTO #Colors
        from (
        select 'Green' color, 1 min_value , 50 max_value  UNION ALL
        select 'Yellow' color, 51 min_value , 100 max_value   UNION ALL
        select 'Red' color, 101 min_value , 1000 max_value 
        ) b

        select  a.Category, a.Counts, a.Value, b.color
        from    #Data a
        left join   #Colors b
                on a.Value between b.min_value and b.max_value

        drop table #Colors
        drop table #Data

This will give the output with the color you want to have

input dataset is

output would look like this

if you have a physical table you would have the flexibility to change color using the table.

Design window

background expression added on the color value

Answer 4

So I don't see any answers here based on an actual expression you can use to do this. I'll say up front, this is going to be somewhat complex and will probably require some tweaking on your end to get the values correct. You'll want to use a SWITCH to make this as simple as possible and simply specify a range that you want for each color. Assuming your AOVRunning values are all populated from the same column in the DB, the following expression should work in the background color property.

=SWITCH(Fields!AOV.Value > (Fields!Avg.Value + 10), "Green",
Fields!AOV.Value >= (Fields!Avg.Value + 5) AND Fields!AOV.Value <= (Fields!Avg.Value + 10), "LightGreen",
Fields!AOV.Value = Fields!Avg.Value, "Yellow",
Fields!AOV.Value <= (Fields!Avg.Value - 5) AND Fields!AOV.Value >= (Fields!Avg.Value -10), "LightRed",
Fields!AOV.Value < (Fields!Avg.Value - 10), "Red",
true, "White")

This is just comparing each AOV value to the average with ranges associated to that average. Obviously, you can add as many colors as you like to this based on the spread you choose -- this was just an example. The final true, "White" just catches any leftovers that don't meet any of the other conditional comparisons. You could also just add an IIF outside the SWITCH to eliminate those results before entering the SWITCH...

=IIF(Fields!AOV.Value Is Nothing, "White", SWITCH(....))`