CODEWITHSUNDEEP
reporting-servicessql-server-2008-r2ssrs-2008
whytheq
whytheq

Reputation: 35557

changing bar fill colours in ssrs chart

SO Post

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")

Upvotes: 3

Views: 24153

Answers (2)

Jair Morais
Jair Morais

Reputation: 41

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

Upvotes: 4

Dominic Goulet
Dominic Goulet

Reputation: 8113

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.

Upvotes: 6

Related Questions