{"id":54,"date":"2012-09-10T08:33:00","date_gmt":"2012-09-10T03:03:00","guid":{"rendered":"https:\/\/www.inogic.com\/blog\/?p=54"},"modified":"2012-09-10T08:33:00","modified_gmt":"2012-09-10T03:03:00","slug":"phone-formating-in-ssrs-report","status":"publish","type":"post","link":"https:\/\/www.inogic.com\/blog\/2012\/09\/phone-formating-in-ssrs-report\/","title":{"rendered":"Phone Formating in SSRS report"},"content":{"rendered":"
<\/span><\/p>\n
Many a times\u00a0there\u00a0had been requirements\u00a0to format the CRM phone number in the SSRS report in following format.<\/span><\/span><\/div>\n

<\/span><\/p>\n

<\/span><\/a><\/div>\n

<\/span><\/p>\n

We tried to achieve this by creating 12 text boxes and in each textbox we applied the below logic in the expression.<\/p>\n

<\/span><\/span><\/span><\/div>\n

<\/span><\/p>\n

= IIF(Fields!telephone1.Value.toString().Length>=1, GetChar(Fields!telephone1.Value.toString(),1), \u201c\u201d)<\/p>\n

<\/span><\/span><\/span><\/i><\/b><\/div>\n

<\/span><\/p>\n

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\u00a0and you will get an error while running this report.<\/p>\n

<\/span><\/span><\/span><\/div>\n

<\/span><\/p>\n

To resolve this issue we found\u00a0a work around for this.<\/p>\n

<\/span><\/span><\/span><\/div>\n

<\/span><\/p>\n

First you need to format the data using the SQL query and PAD the spaces into the field. As given below.<\/p>\n

<\/span><\/span><\/span><\/div>\n

<\/span><\/p>\n

LEFT(ISNULL(telephone1, ”) + ‘\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ‘, 10)<\/p>\n

<\/span><\/span><\/em><\/span><\/b><\/div>\n

<\/span><\/p>\n

The above statement will format the telephone1 attribute. If the user enters errorneuos or junk data into the telephone1 field like \u201c99<\/b>\u201d, then the above statement will return the value \u201c99<\/b>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u201d, i.e. 99 + additional 8 spaces . And in the textboxes expressions you just need to write the following expressions without checking the IIF condition.<\/p>\n

<\/span><\/span><\/span><\/div>\n

<\/span>
<\/p>\n

= GetChar(Fields!telephone1.Value.toString(),1)<\/p>\n

<\/span><\/b><\/div>\n

<\/span><\/p>\n

= GetChar(Fields!telephone1.Value.toString(),2)<\/span><\/b><\/p>\n

<\/span><\/b><\/div>\n

<\/span><\/p>\n

= GetChar(Fields!telephone1.Value.toString(),3)<\/span><\/b><\/p>\n

<\/span><\/b><\/div>\n

<\/span><\/p>\n

= GetChar(Fields!telephone1.Value.toString(),4)<\/span><\/b><\/p>\n

<\/span><\/b><\/div>\n

<\/span><\/span><\/p>\n

<\/span><\/p>\n

<\/span>
<\/span><\/p>\n

Hope this helps!<\/p>\n

<\/span><\/span><\/span><\/div>\n

<\/span>\u00a0<\/div>\n","protected":false},"excerpt":{"rendered":"

Many a times\u00a0there\u00a0had been requirements\u00a0to 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), \u201c\u201d) It works successfully when the length of phone number is equal to\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":13,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[19,58],"tags":[829,1306,1701],"class_list":["post-54","post","type-post","status-publish","format-standard","hentry","category-dynamics-crm","category-ssrs-reports","tag-formatiing","tag-phone-number","tag-telephone1"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts\/54","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/users\/13"}],"replies":[{"embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/comments?post=54"}],"version-history":[{"count":0,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts\/54\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/media?parent=54"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/categories?post=54"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/tags?post=54"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}