Monday, September 10, 2012

Phone Formating in SSRS report


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!
 

4 comments:

  1. You can also overlay textbox having a monospace font like Consolas or Courier New on top of graphical boxes.

    ReplyDelete
  2. Why don't you just use Custom format in Text box properties. Like with phone number:
    ### ### ####

    select Text Box Properties, select Number, select Custom and put the tags(###) into the Custom format field.

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
  3. Yes we can format phone number when we have to show the phone number in single text field.

    Here we were needed to show parts of phone number in different text fields.
    Hence this says how to format phone number in order to place a single digit
    in a single text box.

    ReplyDelete