0

Here is my issue, I have a table in SSRS that I've created that has a due date field (RegulatoryDateDecisionDue). Based on that date field, I need to change the colors of the rows if today's date is past the due date the field should be red. If the amount of hours are less than or equal to 72 hours from today's date till the due date (basically like a countdown till it's due), the rows should be orange, else rows should be white.

Here is the expression I wrote, but I am having an issue, I get error, and idea what I am doing wrong or if I should be doing this differently? Any and all help appreciated.

=IIF((now() > Fields!RegulatoryDateDecisionDue.Value), "Red", IIF(((DateDiff(DateInterval.Hour, Fields!RegulatoryDateDecisionDue.Value, Now()) <= "72"), "Orange", "White")))
ItalianStallion4215
  • 139
  • 2
  • 11

2 Answers2

1

Try:

=Switch(
DateDiff(DateInterval.Hour, Fields!RegulatoryDateDecisionDue.Value, Now())<=72,"Orange",
Fields!RegulatoryDateDecisionDue.Value<now(),"Red",
true,"White"
)

It is better use Switch for multiple conditions.

Let me know if this helps.

alejandro zuleta
  • 13,962
  • 3
  • 28
  • 48
  • It is better, now at least I am seeing the orange, but for some reason I am seeing orange on the fields that are more than 72 hours away from due date. – ItalianStallion4215 Aug 05 '16 at 13:13
  • 72hrs+ the rows should equal white, 72hrs - the rows should be orange, and anything past the due date should be red. – ItalianStallion4215 Aug 05 '16 at 13:14
  • @ItalianStallion4215, try the updated expression. I've changed the order of the conditions, so now-date <= 72 condition is evaluated first then now>date. – alejandro zuleta Aug 05 '16 at 13:18
  • Still not working, I am only receiving back red and orange, but no white. This is part of the issue I was having. – ItalianStallion4215 Aug 05 '16 at 13:37
  • @ItalianStallion4215, What is the condition for white color? Note the expression will return white color when any other condition is not evaluated to True. The expression says if now-date<=72 then Orange else if date – alejandro zuleta Aug 05 '16 at 13:45
  • so for white, if the amount of hours till due date is greater than 72 it should be white. So the amount of hours between now() and due date if greater than 72. – ItalianStallion4215 Aug 05 '16 at 13:52
  • Got it working, the logic was reversed. - Thanks for all the help. Much appreciated. – ItalianStallion4215 Aug 05 '16 at 13:53
  • =Switch( Fields!RegulatoryDateDecisionDue.Value < now(),"Red", DateDiff(DateInterval.Hour, Now(), Fields!RegulatoryDateDecisionDue.Value) <= 72,"Orange", true, "white" ) – ItalianStallion4215 Aug 05 '16 at 13:54
0

Some things to try :

  • Use a switch instead of iif to have a better reading
  • use "h" insted of DateInterval.Hour
  • write 72 instead of "72"
NdsAerith
  • 86
  • 3