Share via

SSRS: Change Fill Colour Depending on Cell Values

  • Article

I find, sometimes it helps to draw out where you want the colours in a range. Then work from outer most conditions inward.

https://i.stack.imgur.com/NHCll.png

Here's an example of how I would test with a T-SQL CASE expression.

SQL Example:

WITH
source_data
AS
(
    SELECT tbl.* FROM (VALUES
      ( -11, 'Red')
    , ( 11, 'Red')
    , ( -6, 'DarkOrange')
    , ( 6, 'DarkOrange')
    , ( 1, 'Green')
    , ( 4.98, 'Green')
    , ( -0.11, 'Green')
    ) tbl ([Field_Name], [FillColourWanted]) 
)
SELECT
      [Field_Name] = CAST([Field_Name] AS FLOAT)
    , [FillColourWanted]
    , [FillColour] = 
        CASE 
            WHEN [Field_Name] <= -10 OR [Field_Name] >= 10 THEN 'Red'
            WHEN [Field_Name] <= -5 OR Field_Name >= 5 THEN 'DarkOrange'
            WHEN [Field_Name] < 5 OR [Field_Name] > -5 THEN 'Green'
        END 
FROM
    source_data

SQL Results:

https://i.stack.imgur.com/ahdEW.png

Expression Example:

=Switch(
  Fields!Field_Name.Value <= -10 OR Fields!Field_Name.Value >=10, "Red"
, Fields!Field_Name.Value <= -5 OR Fields!Field_Name.Value >= 5, "DarkOrange"
, Fields!Field_Name.Value > -5 OR Fields!Field_Name.Value < 5, "Green"
)

SSRS Results

The background colour for [FillColourWanted] and [FillColor] are set by the values from the SQL columns. The background colour for [Expression] is set by the expression example.

https://i.stack.imgur.com/KonbG.png