RSS

Formatting Phone Numbers In Crystal Reports

July 16, 2009

Development

I would think that formatting a phone number would be a pretty standard task in so I’m not sure why this bit of information was so hard to find on the Internets. But anyway, for my own reference and maybe to help out someone else, here is how you do it. For the sake of version differences note that I am using Version 10.

To format a raw data phone number:

Right click on Formula Fields option in the Field Explorer

Select Add New

Create a name for the Formula Field. I simply named mine Phone Number because there is only one phone number listed in the report, but as I better practice the name should have been more descriptive. Something like School Contact Phone Number would have been better.

Select User Editor

In the Formula Editor, paste the following code substituting table.field for your table and field.


/*

VB Syntax, Format Phone Number

*/
Picture (CStr ({table.field}), "(xxx) xxx-xxxx")

Save and close the Formula Editor

All that is left to do is drag the Phone Number Formula Field into your report in instead of the actual phone number field itself.

The Formula Fields Editor is a very powerful utility. While I’m on the topic, I’ve also found a great formula to page your reports in the same style we are all used to seeing in Microsoft Word. 


/*

Paging Numbers Formula

*/
NumberVar startNum := 0;
"Page " + ToText((PageNumber + startNum), 0) +
" of " + ToText((TotalPageCount + startNum), 0)
,

5 Responses to “Formatting Phone Numbers In Crystal Reports”

  1. Louise Says:
    May 5, 2010 at 3:22 pm

    I am trying to teach myself how to do some basic things in Crystal. I have created a table, but the phone numbers need to be formatted. I have tried the formula above, but it is not working for me. I have Crystal Reports server 2008.

    Reply

    • Paul Says:
      May 5, 2010 at 4:07 pm

      Hey Louise,

      That is odd that this formula wouldn’t format the phone number for you. It’s a pretty standard formula as far as I understand.

      The only potential issue I can think of off the top of me head would be that maybe the Picture method isn’t supported in Crystal Server 2008. I don’t know enough about the package to either confirm or deny this.

      I’m not trying to insult your intelligence, but did you swap out the ‘table.field’ with the name of the table and field that contain the phone number value? I’ve made simple oversights like this myself countless times.

      Reply

  2. Chris Says:
    August 24, 2010 at 3:08 pm

    Thanks for this formula, it really helped my reports! It’s weird that there isn’t a more straightforward way to do this in Crystal

    Reply

  3. bob4fit Says:
    March 26, 2011 at 10:38 am

    Good tip. It was as simple as can be. You saved me some time. I appreciate it.

    Reply

  4. Christina Says:
    December 15, 2011 at 10:58 am

    Thanks for posting this. I have been trying to teach myself Crystal and this was very helpful in pointing me in the right direction. I tried the formula above as well and it did not work for me. This is the formula I used:

    “Fax: (” + {location_mstr.fax}[1 to 3] + “) ” +
    {location_mstr.fax}[4 to 6] + “-” +
    {location_mstr.fax}[7 to 10]

    It even adds a nice label in there for ya!

    Reply

Leave a Reply