SSRS Bar chart, change the fill color of the top 3 Bars

Question

In SSRS I have a bar chart. I want to dynamically change the color of the bar for the top 3 bars.

Is this possible with out writing a new sql dataset?

Any ideas would be great.

Thanks,

Answer 1

You can use a expression to change the color of any chart item. If you want to change the color of the top 3 records from your dataset, I'd use a ranking in the SQL to find them. You can even replace the rank 3 from the case statement below with a parameter to let the user select the top number.

WITH
your_chart_data
AS
(
    SELECT tbl.* FROM (VALUES
      ( 'Group1', 100)
    , ( 'Group2', 90)
    , ( 'Group3', 80)
    , ( 'Group4', 70)
    , ( 'Group5', 60)
    , ( 'Group6', 50)
    , ( 'Group7', 40)
    , ( 'Group8', 30)
    , ( 'Group9', 20)
    , ( 'Group10', 10)
    , ( 'Group11', 0)
    ) tbl ([YourColumnGroupHere], [YourColumnValueHere]) 
)
SELECT 
      [YourColumnGroupHere]
    , [YourColumnValueHere]
    , [YourColumnRank] = 
        CASE 
            WHEN RANK() OVER (ORDER BY [YourColumnValueHere] DESC) <= 3 THEN 1
            ELSE 0
        END 
FROM 
    your_chart_data

Then you can use an expression like this.

=IIF(Fields!YourColumnRank.Value = 1, "Red", "Green")

Select one of the series properties you want to change.

Then select Fill and next to Color click fx and you can create an expression to change the color.