Format phone number and hide #ERROR when return is null SSRS

Question

I'm having an issue and everything i've tried doesn't work. I have a phone number datafield that returns numbers with no formatting '3055558798' but i want it to look like this '(305)555-8798'. I can get that done with this expression:

= Format(Convert.ToDouble(Fields!MyFieldName.Value), "(###)###-####")

The only issue is that when the return is null i get #ERROR in the space. I found an expression that got rid of the #ERROR but still no luck putting them both together. I would have to dig through my reports to find the expression but hopefully someone can help me. I've been doing reports for a couple of months but i'm still not very good with all the expressions that there are. I just need to format the phone number and if the return is null then not show anything. There's also this on the same site that i found the expression but it doesn't work so i dont know why the guy said it worked for him.

=Iif (Fields!MyFieldName.Value Is Nothing, Nothing, 
Format(Convert.ToDouble(Fields!MyFieldName.Value), "(###)###-####"))

That just doesn't work for me, I believe the syntax is wrong but i don't know what to change to fix it. Thanks.

Answer 1

The error you have got is nothing to do with formatting, it is the conversion to Double that is failing. So your expression works perfectly as long as your field consists entirely of numeric characters. However, you have some data with non-numeric characters in it, causing the Convert.ToDouble() function to throw an error.

Unfortunately this can not be solved with an IIF expression because IIF is a function, not a language construct so both the true and false parameters get evaluated before being passed to the function regardless of the value of the boolean condition parameter. This means that:

=IIF(IsNumeric(Fields!Phone.Value), Format(Convert.ToDouble(Fields!Phone.Value), "(###)###-####"), Fields!Phone.Value)

will always attempt the conversion to double regardless of the result of the IsNumeric function. There are two ways to solve this:

Use Val instead of ToDouble

The problem with ToDouble is it errors when the string to be converted is an inappropriate form; Val doesn't have that problem - it simply grabs whatever numbers it can. So now we can use the expression:

=IIF(Fields!Phone.Value Is Nothing, 
  Nothing, 
  IIF(IsNumeric(Fields!Phone.Value), 
    Format(Val(Fields!Phone.Value), "(000)000-0000"), 
    Fields!Phone.Value)
)

(Use 0 rather than # so that leading zeroes aren't suppressed)

This expression returns Nothing if the field is Null, checks to see if it is numeric and if so converts it to a number and formats it, otherwise it just returns whatever is in the field.

Note that the Val function is still being run even when the field is not numeric but this expression succeeds because the Val function doesn't raise errors like ToDouble. We simply make the calculation and discard the result.

Custom code

On the Report menu, click Report Properties... and go to the Code tab. Insert the following code:

Function FormatPhone(Phone AS String) AS String
   IF (Phone Is Nothing) Then
    Return Nothing
  Else If (IsNumeric(Phone)) Then
    Return Format(Convert.ToDouble(Phone), "(000)000-0000")
  Else
    Return Phone
  End If
End Function

Use the following expression in your phone number cell:

=Code.FormatPhone(Fields!Phone.Value)