reporting-servicespowerbissrs-2012reportbuilder

Setting conditional colors per task age


pic of table row I'm developing a report using ssrs and report builder. My query and table are built and working. I have a dataset with a field called Task_Age_Days - that calculates the number of days that have gone by since a part was created. My query line for this function is, DATEDIFF(day,aa.CREATED_ON,getdate()) 'Task Age (Days)'.

My table currently shows each part with the date is was created on and the number of days since in a long row format. I want it to highlight the days in red if its been over 60 days, yellow between 30-60 and maybe green if its been under 30.

I tried doing this in the color formatting but am not having luck. Here's what I did below. Any tips?

=IIf(Sum(Fields!Task_Age__Days_.Value >= 60, "Red")


Solution

  • It looks like you have a missing bracket for the Sum function. You also need to provide a colour value for the false part of the IIf function.

    =IIf(Sum(Fields!Task_Age__Days_.Value) >= 60, "Red", "Yellow")
    

    If you want to produce three colours you could either use nested IIf functions like so:

    =IIf(Sum(Fields!Task_Age__Days_.Value) >= 60, "Red", IIf(Sum(Fields!Task_Age__Days_.Value) >= 30, "Yellow", "Green")
    

    But a Switch function might be easier to read:

    =Switch(
        Sum(Fields!Task_Age__Days_.Value) >= 60, "Red",
        Sum(Fields!Task_Age__Days_.Value) >= 30, "Yellow",
        True, "Green"
    )
    

    The True value in the above example acts as an Else clause - the Switch function stops evaluating conditions when it reaches the first expression which returns True. This avoids having to write an explicit logical test for the final condition, such as:

    Sum(Fields!Task_Age__Days_.Value) < 30, "Green"
    

    (thanks @Alan Schofield for the recommended edit)