One of my favourite activities is playing around with the charts in SSRS and trying to tweak their properties. Knowing my penchant for this, one of my colleagues asked my help in verifying whether it is possible to have charts where the columns will have a different colour based on a certain threshold value. From the moment I heard it, I knew it should be possible but then I didn’t want to end up in a “You-told-me, now-you-solve-it” situation at the end of implementation. And also, I didn’t want to end up in the bad books of a pretty lady (Oh, did I forget to mention that my colleague is a gorgeous woman? 😀 ). So I thought of giving a quick try to confirm. For implementing this functionality, I used the Adventure Works R2 analysis services database to create a column chart with Order Count measure on data and Category from product dimension on the categories part. Now, click on the column and select the dropdown in the Color property as shown below
Now, click on the Expression and then give the conditions for which the threshold value should be highlighted, lets say the maximum value for Category should be Green while all others should be Maroon.
=iif(Sum(Fields!Order_Count.Value)=max(Fields!Order_Count.Value, “DataSet1″),”Green”,”Maroon”)
And now, when you preview it, the Category with the highest value would be highlighted in Green.
You can also modify your expressions to highlight both the max & min value, or just highlight all columns above or below a particular value.
=iif(Sum(Fields!Order_Count.Value)=max(Fields!Order_Count.Value, “DataSet1″),”Green”,iif(Sum(Fields!Order_Count.Value)=min(Fields!Order_Count.Value, “DataSet1″),”Red”,”Maroon”))
=iif(Sum(Fields!Order_Count.Value)>10000,”Green”,”Maroon”)
