changing bar fill colours in ssrs chart

Question

Current I've got 5 bars in my RS chart - in the future there might be 7 bars or 17 bars or 27 bars!

With a couple of bars I can have an expression like this:

=iif(Fields!Market.Value = "Spain"
,"Gold"
,iif (Fields!Market.Value = "Denmark"
    , "Gray"
    , iif(Fields!Market.Value = "Italy"
        , "Blue"
        , "Purple"
        )
    )
)

If I can't predict how many countries will be included + I'd rather not have to hard code in "Green", "Red" etc how do I change the expression?

I've tried this but it is erroring:

=Switch(Mod(Fields!Rank.Value/CDbl(2))=CDbl(0), "Gold", 
   Mod(Fields!Rank.Value/CDbl(3))=CDbl(0), "Gray", 
   Mod(Fields!Rank.Value/CDbl(2))>CDbl(0) "Blue")

Above is the totally incorrect syntax: This works:

=Switch(CDbl(Fields!Rank.Value Mod 2)=CDbl(0), "Gold", 
   CDbl(Fields!Rank.Value Mod 3)=CDbl(0), "Gray", 
   CDbl(Fields!Rank.Value Mod 2)>CDbl(0), "Blue")

Ok - the above runs (not sure how!) but the below is based on help from Dominic Goulet and is really easy to follow and nice and expandable to more colours; this is the solution for 5 colours:

=Switch(CDbl(Fields!Rank.Value Mod 5)=CDbl(0), "Gold", 
   CDbl(Fields!Rank.Value Mod 5)=CDbl(1), "Gray", 
   CDbl(Fields!Rank.Value Mod 5)=CDbl(2), "Green", 
   CDbl(Fields!Rank.Value Mod 5)=CDbl(3), "Red", 
   CDbl(Fields!Rank.Value Mod 5)=CDbl(4), "Pink")

Your last switch is not right. 12 will result in "Gold", where it should be "Gray". You should use a (Fields!Rank.Value Mod 3 = 0), (Fields!Rank.Value Mod 3 = 1) and (Fields!Rank.Value Mod 3 = 2) conditions. — Dominic Goulet, Jun 21 '12 at 14:27

score 6 · Accepted Answer · answered Jun 21 '12 at 12:18

6

First of all, instead of using many "IIF"s, you should use "Switch", it's leaner that way.

Switch(Fields!Market.Value = "Spain", "Gold",
       Fields!Market.Value = "Denmark", "Gray",
       Fields!Market.Value = "Italy", "Blue")

Now if you want a color per coutry, you should defenitely store that in your database and pull it out when you need it. That way, a country will always have the same color on every report you have.

answered Jun 21 '12 at 12:18

Dominic Goulet

7,983
7
28
56

think I've had a better idea - rank the countries (or whatever dimension is in the report) and add the rank into the data - then see if the number is divisible by two and if so give it one colour - otherwise a second colour. – whytheq Jun 21 '12 at 14:02
That's called "alternating colors". It is a valid technique, mostly used within tables to get alternating row colors. – Dominic Goulet Jun 21 '12 at 14:06
i've just added my attempt to the OP ...can you see what is wrong with it: I'm hoping to get 3 alternating colours – whytheq Jun 21 '12 at 14:13
I have similar issue but my problem is I don't know what values I will get... I will get integers but I need to set a color for every integer I get... can you guide me ? – Renascent Jul 27 '18 at 08:59

score 4 · Answer 2 · edited Jul 26 '17 at 00:12

It would be better to create a function. For that, go to Report Properties, choose code and type this example :

Public Function Color(ByVal Index as Integer) as String

Select Case Index
Case = 1        
    return "#a6cee3"    
Case = 2        
    return "#1f78b4"    
Case = 3        
    return "#b2df8a"    
Case = 4        
    return "#33a02c"    
Case = 5        
    return "#fb9a99"    
Case = 6        
    return "#e31a1c"    
Case = 7        
    return "#fdbf6f"    
Case = 8        
    return "#ff7f00"    
Case = 9        
    return "#cab2d6"    
Case = 10       
    return "#6a3d9a"    

End Select

End Function

On the Fill option from "Series Properties->Pick color-> Color choose fx put this code

=Code.Color(rownumber(nothing))

Each bar will have a color.

For the HEX colors I took from the website : http://colorbrewer2.org/#type=qualitative&scheme=Paired&n=10 It shows the best colors that match with each other, so you don't need to think of that. And you can add as many colors as you want

This was perfect for me. Thank you for giving such a clear answer. The updated path is to right-click on a bar in the graph then "Series Properties -> Fill -> Pick Color -> [fx] button". The last change I did was to change the "Select Case Index" to "Select Case (Index MOD 4)" and change the case values to 0 to 3. This allowed me to have an unlimited number of rows. The colors rotated through those 4 colors. — Ted O'Connor, Dec 29 '21 at 16:04

changing bar fill colours in ssrs chart

2 Answers2

Linked