SSRS - Complex date range field logic not working with nested IIF's

Question

There's a requirement to display a date range based on the following logic in a Reporting Services report like so:

If there's only a Started datetime, display like so:

on 12 December 2014

If there's a Started and Finished datetime on the same day, display like so:

on 12 December 2014 between 11:20 am and 1:10 pm

If there's a Started and Finished datetime on different days, display like so:

between 12 December 2014 11:20 am and 13 December 2014 1:10 pm

I know it's ugly, but I've got this expression to display the field:

=IIf(IsNothing(First(Fields!Finished.Value, "InspectionAdvice")), "on " & Day(First(Fields!Started.Value, "InspectionAdvice")) & " " & MonthName(Month(First(Fields!Started.Value, "InspectionAdvice"))) & " " & Year(First(Fields!Started.Value, "InspectionAdvice")) & " " & 
    IIf(
        Hour(First(Fields!Started.Value, "InspectionAdvice")) > 12, 
        Hour(First(Fields!Started.Value, "InspectionAdvice")) - 12, 
        Hour(First(Fields!Started.Value, "InspectionAdvice"))
    ) 
    & ":" & Minute(First(Fields!Started.Value, "InspectionAdvice")) & " " & 
    IIf(Hour(First(Fields!Started.Value, "InspectionAdvice")) > 12, "pm", "am")
,
IIf(First(Fields!Started.Value.Date, "InspectionAdvice") = First(Fields!Finished.Value.Date, "InspectionAdvice")
    , "on " & Day(First(Fields!Started.Value, "InspectionAdvice")) & " " & MonthName(Month(First(Fields!Started.Value, "InspectionAdvice"))) & " " & Year(First(Fields!Started.Value, "InspectionAdvice")) & " between " & 
    IIf(
        Hour(First(Fields!Started.Value, "InspectionAdvice")) > 12, 
        Hour(First(Fields!Started.Value, "InspectionAdvice")) - 12, 
        Hour(First(Fields!Started.Value, "InspectionAdvice"))
    ) 
    & ":" & Minute(First(Fields!Started.Value, "InspectionAdvice")) & " " & 
    IIf(Hour(First(Fields!Started.Value, "InspectionAdvice")) > 12, "pm", "am")
    & " and " & 
    IIf(
        Hour(First(Fields!Finished.Value, "InspectionAdvice")) > 12, 
        Hour(First(Fields!Finished.Value, "InspectionAdvice")) - 12, 
        Hour(First(Fields!Finished.Value, "InspectionAdvice"))
    ) 
    & ":" & Minute(First(Fields!Finished.Value, "InspectionAdvice")) & " " & 
    IIf(Hour(First(Fields!Finished.Value, "InspectionAdvice")) > 12, "pm", "am")

    , "between " & Day(First(Fields!Started.Value, "InspectionAdvice")) & " " & MonthName(Month(First(Fields!Started.Value, "InspectionAdvice"))) & " " & Year(First(Fields!Started.Value, "InspectionAdvice")) & " " & 
    IIf(
        Hour(First(Fields!Started.Value, "InspectionAdvice")) > 12, 
        Hour(First(Fields!Started.Value, "InspectionAdvice")) - 12, 
        Hour(First(Fields!Started.Value, "InspectionAdvice"))
    ) 
    & ":" & Minute(First(Fields!Started.Value, "InspectionAdvice")) & " " & 
    IIf(Hour(First(Fields!Started.Value, "InspectionAdvice")) > 12, "pm", "am") 
    & " and " & Day(First(Fields!Finished.Value, "InspectionAdvice")) & " " & MonthName(Month(First(Fields!Finished.Value, "InspectionAdvice"))) & " " & Year(First(Fields!Finished.Value, "InspectionAdvice")) & " " & 
    IIf(
        Hour(First(Fields!Finished.Value, "InspectionAdvice")) > 12, 
        Hour(First(Fields!Finished.Value, "InspectionAdvice")) - 12, 
        Hour(First(Fields!Finished.Value, "InspectionAdvice"))
    ) 
    & ":" & Minute(First(Fields!Finished.Value, "InspectionAdvice")) & " " & 
    IIf(Hour(First(Fields!Finished.Value, "InspectionAdvice")) > 12, "pm", "am")
)
)

Everything works fine if both Started and Finished are not null. However, if Finished is null then I always get #Error.

Now if I remove the second part of the IIF containing the nested IIf logic e.g.

=IIf(IsNothing(First(Fields!Finished.Value, "InspectionAdvice")), "on " & Day(First(Fields!Started.Value, "InspectionAdvice")) & " " & MonthName(Month(First(Fields!Started.Value, "InspectionAdvice"))) & " " & Year(First(Fields!Started.Value, "InspectionAdvice")) & " " & 
    IIf(
        Hour(First(Fields!Started.Value, "InspectionAdvice")) > 12, 
        Hour(First(Fields!Started.Value, "InspectionAdvice")) - 12, 
        Hour(First(Fields!Started.Value, "InspectionAdvice"))
    ) 
    & ":" & Minute(First(Fields!Started.Value, "InspectionAdvice")) & " " & 
    IIf(Hour(First(Fields!Started.Value, "InspectionAdvice")) > 12, "pm", "am")
,
"REMOVED"
)

Things work when Finished is null.

Any idea why I can't get both parts of the ugly statement working together? I'm guessing Reporting Services is trying to resolve some of the IIF's in the false condition which will hit the NULL Finished field and so result in errors?

*I'm guessing Reporting Services is trying to resolve some of the IIF's in the false condition which will hit the NULL Finished field and so result in errors?* => Your guess is right — Sébastien Sevrin, Jul 21 '15 at 07:33

score 2 · Accepted Answer · answered Jul 21 '15 at 08:23

Not sure why, but the part comparing the dates is causing the error:

IIf(First(Fields!Started.Value.Date, "InspectionAdvice") = First(Fields!Finished.Value.Date, "InspectionAdvice")

Like you said, for some reason RS is trying to evaluate Fields!Finished.Value.Date and that causes an error.

I simplified your code and found that this workaround is working fine:

=IIf(IsNothing(Fields!Finished.Value), 
    "finished is null", 
    IIf(DateDiff(DateInterval.Day, Fields!Finished.Value, Fields!Started.Value)=0,  
        "same day",
        "different day"
        )
    )

RESULT: result in report builder 3.0

Thanks OzW - it's very much appreciated. Works perfectly. I was tearing my hair out with this one so thank you! — Gavin, Jul 21 '15 at 21:09

score 2 · Answer 2 · answered Jul 21 '15 at 08:55

2

In SSRS, both the IIF branches are evaluated. See here. Hence the error, as you are rightly suspecting.

Try modifying your dataset InspectionAdvice to return a blank instead of null when their is no end date. You could use the SQL ISNULL function

SELECT Started, ISNULL(Finished, '') Finished
FROM [SomeTable]

answered Jul 21 '15 at 08:55

SouravA

5,147
2
24
49

Thanks for your answer - very much appreciated. A good solution, but I preferred the other solution as it didn't require any manipulation of the data returned. – Gavin Jul 21 '15 at 22:58

SSRS - Complex date range field logic not working with nested IIF's

2 Answers2