CODEWITHSUNDEEP
reporting-servicesssrs-2008-r2calculated-fieldssrs-expression
Will F
Will F

Reputation: 437

SSRS: expression in a calculated field

I am trying to write an expression in a calculated field that shows if one date value is greater than another:

=(IIF(fields!date1.value>fields!date2.value,"late","on time")

This works fine when there is a value in fields!date2.

However, if fields!date2.value is blank, the expression returns "on time". I don't want it to do this, as date1 may be 1/4/15 - if date2 is blank, that mean's it's overdue and I therefore want the calculated field to say "late".

Therefore I want the expression to say "late" if fields!date1.value is < today's date (basically, if it's blank and fields!date1.value is before today's date, then it means it's overdue).

Can anyone advise on how to amend the expression to accommodate this?

Upvotes: 1

Views: 1832

Answers (1)

S&#233;bastien Sevrin
S&#233;bastien Sevrin

Reputation: 5405

Let's focus on "on time"

Here is what's define "on time" (everything else will be considered as "late"):

  • Date2 is not null and Date1 >= Date2
  • Date2 is null and Date1 >= today

Let's convert it into expressions:

  • Not(Fields!date1.Value Is Nothing) And (Fields!date1.Value >= Fields!date2.Value
  • Fields!date2.Value Is Nothing And Fields!date1.Value >= Today()

Here is the full expression:

=Iif((Not(Fields!date2.Value Is Nothing) And (Fields!date1.Value >= Fields!date2.Value) Or (Fields!date2.Value Is Nothing And Fields!date1.Value >= Today())), "on time", "late")

Upvotes: 1

Related Questions