Dealing with null values in Reporting Services
Monday, March 12th, 2007Using conditional statements in SQL Server Reporting Services 2005 (SSRS) or SQL Reporting Services 2000 (SRS) can be a problem if the values you are working with can be null. The statement evaluator is VisualBasic based and every part of a conditional statement is evaluated, so something like:
Visual Basic:
-
=IIF(
-
IsNothing(Fields!DayTimePhone.Value),
-
"",
-
IIF(
-
Fields!DayTimePhone.Value.Length=10,
-
Format(Convert.ToDouble(Code.SafeValue(Fields!DayTimePhone.Value)),"(###) ###-####"),
-
"etc"
-
)
-
)
will fail if the value is null. You can get around this easily by adding a bit of code:
Visual Basic:
-
Public Function SafeValue(ByVal strValue As String) As String
-
Dim retValue As String
-
If IsNothing(strValue) Then
-
retValue = "0"
-
Else
-
retValue = strValue
-
End If
-
Return retValue
-
End Function
and modifying the conditional statement to read:
Visual Basic:
-
=IIF(
-
Code.SafeValue(Fields!DayTimePhone.Value)="0",
-
"",
-
IIF(
-
Code.SafeValue(Fields!DayTimePhone.Value).Length=10,
-
Format(Convert.ToDouble(Code.SafeValue(Fields!DayTimePhone.Value)),"(###) ###-####"),
-
"etc"
-
)
-
)