How to Add Excel-like "color scale" red-yellow-green conditional formatting in SSRS report based on min and max values

0
votes

How can I create heat map in SSRS based on min and max values? Right now I'm using SWITCH function in order to differentiate colors. 

    =
    SWITCH 
        (Fields!.Value = 0, "Coral",
            Fields!.Value >=1  and Fields!.Value <= 5 , "Yellow", 
            Fields!.Value >=6  and Fields!.Value <= 10 , "Gold", 
            Fields!.Value >= 11 and Fields!.Value <= 16,"#bdff30",
             Fields!.Value >= 17 and Fields!.Value <= 22,"#7fda24",
Fields!.Value >= 55 ,"#0ca102")

But how can I just tell SSRS that, for example between 5 different values, choose heat map colors, lets say shades from red to green and assign those colors to each value in a cell? Should be like this: enter image description here

2
sqlreporting-servicesheatmap
Is this not working the way you expect? For similar things, I have used VB Code with a CASE to assign colors but it's similar to what you are doing now with the SWITCH. I don't see much advantage in one way over the other. – Hannover Fist
My data is dynamic. Values can be absolutely different. If I have 5 different values I want to break them down from min to max and assign color. It can be done in Excel, it should be also possible in SSRS – Serdia

2 Answers

2
votes

I don't think you need to make code. I was thinking more about something like this : 

 =SWITCH 
(Fields!.Value >= Min(Fields!myField.Value, "DataSetName") + (5 *((Max(Fields!myField.Value, "DataSetName")-Min(Fields!myField.Value, "DataSetName"))/6)) ,"#0ca102"
 Fields!.Value >= Min(Fields!myField.Value, "DataSetName") + (4 *((Max(Fields!myField.Value, "DataSetName")-Min(Fields!myField.Value, "DataSetName"))/6)) ,"#7fda24"
 Fields!.Value >= Min(Fields!myField.Value, "DataSetName") + (3 *((Max(Fields!myField.Value, "DataSetName")-Min(Fields!myField.Value, "DataSetName"))/6)),"#bdff30"
 Fields!.Value >= Min(Fields!myField.Value, "DataSetName") + (2 *((Max(Fields!myField.Value, "DataSetName")-Min(Fields!myField.Value, "DataSetName"))/6)),"Gold"
 Fields!.Value >= Min(Fields!myField.Value, "DataSetName") + ((Max(Fields!myField.Value, "DataSetName")-Min(Fields!myField.Value, "DataSetName"))/6),"Yellow"
 1=1,"Coral")

With values from 5 to 60 for exemple the test become :

     =SWITCH 
(Fields!.Value >= 50.83 ,"#0ca102"
 Fields!.Value >= 41.67,"#7fda24"
 Fields!.Value >= 32.5,"#bdff30"
 Fields!.Value >= 23.33,"Gold"
 Fields!.Value >= 14.17,"Yellow"
 1=1,"Coral")

I'm really not good in maths. There is maybe (definitely) better formulas... It's just to show what I would say ^^ Using percent to deduce ranges.

0
votes

For first : With the switch : it takes the first right value : so you don't need to write 2 conditions to test a value. Just take them from the biggest to the smallest. For exemple : 

 =
SWITCH 
    (Fields!.Value >= 55 ,"#0ca102"
     Fields!.Value >= 17 ,"#7fda24"
     Fields!.Value >= 11,"#bdff30"
     Fields!.Value >= 6,"Gold"
     Fields!.Value >= 1,"Yellow"
     1=1,"Coral")

To get the max of the values it's possible : with the function "MAX" (and the same for MIN). Don't forget here to references all the dataset (in case of groups). So when you use the max for all the dataset it's like that : 

=Max(Fields!myField.Value, "DataSetName")

You can then, make operations with min and max then to repace your fixed values. But I can't help you more for that because I'm really not good with maths.