0

I have a column of data brought in from SQL that is sometimes populated, other times null. In an SSRS report, I have two columns: one for the actual value, one for a calculation on that value.

The column for the value is blank when it is null, which is what I want. However, the calculation column returns "#Error" even when I try and catch this with an IfNothing() clause.

Below is the expression on the calculated column, with the "Ready to Discharge" field being the column of sql data that is intermittently populated.

In the picture below are the two columns. The second column finds the time difference between the "Ready Disch" time and another field "Signup Time", which is a timestamp.

=IIF(IsNothing(Fields!ReadyForDischarge.Value) = True, Nothing, (IIF(Fields!DoctorSignUp.Value > Fields!ReadyForDischarge.Value, "-"& Format(Dateadd("s", abs(datediff("s", Fields!DoctorSignUp.Value,Fields!ReadyForDischarge.Value)),  "00:00"), "mm:ss"), Format(Dateadd("s", abs(datediff("s", Fields!DoctorSignUp.Value,Fields!ReadyForDischarge.Value)),  "00:00"), "mm:ss"))))

Error Example

Dom Vito
  • 507
  • 8
  • 38
  • `IIF()` does not perform a short-circuit boolean evaluation. You have to also guard against nulls in the false portion. abs(datediff(null)) is probably not a valid statement. – Ross Bush May 17 '18 at 14:32

1 Answers1

0

A better way may be to use custom code to perform elaborate formatting.

<CellValue>=Code.GetSignupDisplayValue(Fields!ReadyForDischarge.Value,Fields!DoctorSignUp.Value)

And add the custom code similar to:

Public Function GetSignupDisplayValue(dischargeDate As DateTime, doctorSignup As DateTime)As String
Begin
    Dim result As String = ""
    If Not dischargeDate IsNothing Begin
       --Do you formating here
    End
    Return result
End Function
Ross Bush
  • 14,648
  • 2
  • 32
  • 55