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