Phone Formating in SSRS report

By | September 10, 2012

Many a times there had been requirements to format the CRM phone number in the SSRS report in following format.

We tried to achieve this by creating 12 text boxes and in each textbox we applied the below logic in the expression.

= IIF(Fields!telephone1.Value.toString().Length>=1, GetChar(Fields!telephone1.Value.toString(),1), “”)

It works successfully when the length of phone number is equal to 10. But it fails when the length of phone number is less than 10 and you will get an error while running this report.

To resolve this issue we found a work around for this.

First you need to format the data using the SQL query and PAD the spaces into the field. As given below.

LEFT(ISNULL(telephone1, ”) + ‘          ‘, 10)

The above statement will format the telephone1 attribute. If the user enters errorneuos or junk data into the telephone1 field like “99”, then the above statement will return the value “99        ”, i.e. 99 + additional 8 spaces . And in the textboxes expressions you just need to write the following expressions without checking the IIF condition.

= GetChar(Fields!telephone1.Value.toString(),1)

= GetChar(Fields!telephone1.Value.toString(),2)

= GetChar(Fields!telephone1.Value.toString(),3)

= GetChar(Fields!telephone1.Value.toString(),4)

Hope this helps!


Leave a Reply

Your email address will not be published. Required fields are marked *