Nesting IIF in SSRS 2008

Question

I'm writing an expression which needs to calculate the number of days between current report rundate and the date value in field (named cmp_lastap), then return a value of:

• "delinquent" if the number of elapsed days is >= 60,
• "past due" if >= 45 and <60,
• "due" if >30 and <45 and
• "current" if <= 30.

It seems that a nested IIF statement is what I need to use, but something is off and I can't figure out what I've done wrong:

=IIF(DateDiff(DateInterval.Day, Fields!cmp_lastap.Value, Today) >= 60,"DELINQUENT", IIF(DateDiff(DateInterval.Day, Fields!cmp_lastap.Value,Today) > 45 AND <60, "PAST DUE", IIF(DateDiff(DateInterval.Day, Fields!cmp_lastap.Value, Today) > 30 AND <=45, "DUE", IIF(DateDiff(DateInterval.Day, Fields!cmp_lastap.Value, Today) <= 30, "CURRENT")

Any help or suggestions for other expressions to accomplish this would be greatly appreciated!

Answer 1

You don't have to test for less than. Short-circuit bool evaluation is being used.

=IIF(DateDiff(DateInterval.Day, Fields!cmp_lastap.Value, Today) >= 60, "DELINQUENT",
    IIF(DateDiff(DateInterval.Day, Fields!cmp_lastap.Value, Today) > 45, "PAST DUE",
        IIF(DateDiff(DateInterval.Day, Fields!cmp_lastap.Value, Today) > 30, "DUE",
            "CURENT")
    )
)   

You can also use a CASE statement which may prove more readable.

Answer 2

You can't use AND like that, you would have to say [date comparison logic] > X AND [date comparison logic] < Y etc...

However... I find nested IIF's get messy quickly especially if you have more than one condition to test so I prefer to use SWITCH

Using switch you could write it like this.

=SWITCH (
    DateDiff(DateInterval.Day, Fields!cmp_lastap.Value, Today) >= 60,"DELINQUENT",
    DateDiff(DateInterval.Day, Fields!cmp_lastap.Value, Today) > 45,"PAST DUE",
    DateDiff(DateInterval.Day, Fields!cmp_lastap.Value, Today) > 30,"DUE",
    True, "CURRENT"
)

Another nice side effect is that SWITCH stops at the first expression that evaluates to True so we don't need to test ranges. The final True acts like an ELSE

Note I've not tested or checked you date logic but assuming that is sound then the above will work.