locked
BIDS reporting services IIF evaluates both branches RRS feed

  • Question

  • I am trying to create a report using a custom date format for a field which may be null so I have calculated field defined by the expression;

    =Switch(Fields!qubic_servicemodule_cleardatetimeValue.Value = "", "",
            true, CDate(Microsoft.Crm.Reporting.RdlHelper.DateTimeUtility.ConvertUtcToLocalTime(
                            Fields!qubic_servicemodule_cleardatetimeValue.Value, 
                            Parameters!CRM_UserTimeZoneName.Value)
                        )
           )

    ie if the field is defined replace it with the local time equivalent and if not then just put a blank there.

    In the report I format this with;

    =IIF(IsNothing(Fields!LocalClear.Value),
     "nowt1",
     IIF(Fields!LocalClear.Value="",
        "nowt2",
        Day(Fields!LocalClear.Value) & " "
        & MonthName(Month(Fields!LocalClear.Value), True) 
        & " " & Year(Fields!LocalClear.Value)
        & " " & Right("0" & Hour(Fields!LocalClear.Value), 2)
        & ":" & Right("0" & Minute(Fields!LocalClear.Value), 2)
        )
      )

    ie if there's no value show "nowt1", if it's an empty string show "nowt2" otherwise format the date as I want. "nowt1" and "nowt2" are obviously dummy values so I can see what is happening. I have no data in that field yet so all values should show as "nowt1" or "nowt2" but instead all values show as "#error". In the preview window it protests about an invalid conversion from "" to date.

    If I change the field definition to 

    =Switch(Fields!qubic_servicemodule_cleardatetimeValue.Value = "", "",
            true, "summat"
           )

    and the field to format the date with;

    =IIF(IsNothing(Fields!LocalClear.Value),
     "nowt1",
     IIF(Fields!LocalClear.Value="",
        "nowt2",
        "summat else"
        )
      )

    Then every field is show as "nowt2" which is as I would expect. The conditions have not changed so it appears as though BIDS / reporting services / somtheing is causing it to evaluate both branches of an if condition even though the whole purpose of the if condition is to prevent it crashing by trying to evaluate something meaningless.

    How do I overcome this?

    Monday, July 7, 2014 10:29 AM