SSRS Conditional Expression

Question

In SSRS report, I want to perform conditional color formatting where highest rank should be Green and lowest rank should be Red within a Regional Manager group as shown below

Note: Couple of options, I was thinking of includes

1. I am using custom code function, for deriving Min and Max value, and somehow if I can include grouping filter on Regional Manger then it could work, but don't know if that's possible

2. In dataset, I create extra columns for each column and store Min\max value in it. But less keen towards this option, since I have 24 different ranks and which would mean, I will need 24 different columns along with current 40 attributes

Any help would be appreciated

Answer 1

I know you don't want to do this for each column, but despite your misgivings it is probably the best approach. Based on my previous answer to your earlier related question you can colour the min and max for each group as follows.

1. Create a table with fields store, atvrank, and btvrank

2. Right click the row header, and select Add Group -> Row Group - Parent Group, and choose Regional Manager. Set the Group name to RegionalManagerGroup

3. Then set the background colour for your cells to

   =iif(Fields!atvRank.Value = min(Fields!atvRank.Value, "RegionalManagerGroup"),
    "Green",
    iif(Fields!atvRank.Value = max(Fields!atvRank.Value, "RegionalManagerGroup"),
        "Red",
        "White"
        )
    )
   

This now finds the maximum and minimum within the current group instead of the whole dataset. You will need to set this expression for each field individually, but this is probably less effort than returning new rows from the database to determine the maximum and minimum for each field.

This approach will give the following output

Please seriously consider this solution. If you have further questions, please just ask.