Nahid Sultana Tithi Nahid Sultana Tithi - 4 years ago 90
SQL Question

Conditional Expression in SSRS

I wrote this expression and getting errors, not sure why.

I am trying to get if

Fields!Started.Value
is in between 3-6 days old, and
Fields!NumSuccess.Value = 4
, then I should get "Yellow".

If
Fields!Started.Value
is more than 7 days older than today, and
Fields!NumSuccess.Value
not equal to 4, then I should get "Red".

And finally, if none of those works, then I should have "DarkGreen"

But this code is giving me error message..can you help please?

=IIF((DateDiff("d",Today(),Fields!Started.Value)>=3 and (Fields!Started.Value) <= 6), AND (=Fields!NumSuccess.Value)=4 "Yellow",
IIF(DateDiff("d",Today(),Fields!Started.Value)>= 7, AND (=Fields!NumSuccess.Value)!=4, "Red",
true,"DarkGreen"))

Answer Source

Try Switch function which is cleaner and readable:

=Switch(
  DateDiff(DateInterval.Day,CDate(Fields!Started.Value),Today())<=6 and
  DateDiff(DateInterval.Day,CDate(Fields!Started.Value),Today())>=3 and
  Fields!NumSuccess.Value=4, "Yellow",
  DateDiff(DateInterval.Day,CDate(Fields!Started.Value),Today())>7 and
  Fields!NumSuccess.Value<>4, "Red",
  true,"DarkGreen"
)

Let me know if this helps.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download