• Replies 14 replies
  • Subscribers 336 subscribers
  • Views 45 views
  • Users 0 members are here
Related

Zip Code Formats - SSRS

Former Member
Former Member $organization

Hello All,

I need to display zip codes in an SSRS report. It is complicated because the format is different for different countries. The only thing I have found is to deal with the formatting in the Excel spreadsheet when the data is exported. I can convert the cells to a Number format and then define the format as Special and Zip Code + 4. Is there a better way?

 

Gloria

  • $organization

    Hi Gloria,

    If you have access to the report in SQL, there is a handy built in function AF_FORMAT_STRING which should help you out. The function handles postal codes for whatever country the address is from.

    Here's a quick sample on how to use it:

    SELECT [dbo].AF_FORMAT_STRING(a.postal_code, c.zip_mask)
    FROM   [dbo].T_ADDRESS a
    LEFT JOIN [dbo].TR_COUNTRY c ON a.country = c.id 

    Hope that helps a bit!



    [edited by: Paul Kappel at 10:51 AM (GMT -6) on 11 Nov 2014]
  • $organization

    By the way, anyone interested in international postal address formatting, etc., should know of this fantastic site:

    http://www.columbia.edu/~fdc/postal/

  • Former Member
    Former Member $organization

    Excellent! Thank you Paul.

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Paul Kappel
    Sent: Tuesday, November 11, 2014 11:02 AM
    To: Gloria Ormsby
    Subject: Re: [Tessitura Technical Forum] Zip Code Formats - SSRS

     

    Hi Gloria,

    If you have access to the report in SQL, there is a handy built in function AF_FORMAT_STRING which should help you out. The function handles postal codes for whatever country the address is from.

    Here's a quick sample on how to use it:

    SELECT [dbo].AF_FORMAT_STRING(a.postal_code, c.zip_mask)
    FROM   [dbo].T_ADDRESS a
    LEFT JOIN [dbo].TR_COUNTRY c ON a.country = c.id 

    Hope that helps a bit!

    From: Gloria Ormsby <bounce-gloriaormsby5026@tessituranetwork.com>
    Sent: 11/11/2014 10:41:23 AM

    Hello All,

    I need to display zip codes in an SSRS report. It is complicated because the format is different for different countries. The only thing I have found is to deal with the formatting in the Excel spreadsheet when the data is exported. I can convert the cells to a Number format and then define the format as Special and Zip Code + 4. Is there a better way?

     

    Gloria




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

  • Former Member
    Former Member $organization

    I tried inserting this into my procedure but anything with zip +4 does not have a value. My zip mask is @@@@@-@@@@. This is what I have for that section of the procedure:

     

    --postal code

    CREATE TABLE #zip (

    postal_code VARCHAR(10),

    description VARCHAR(55))

     

    INSERT INTO #zip

    Select       

          postal_code = convert(varchar(10), 

          dbo.AF_FORMAT_STRING(a.postal_code, b.zip_mask)),

          country = b.description

    From  t_address a

          JOIN tr_country b ON a.country = b.id

     

     

    What shall I do to display those zips with +4?

     

    Gloria

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Paul Kappel
    Sent: Tuesday, November 11, 2014 11:02 AM
    To: Gloria Ormsby
    Subject: Re: [Tessitura Technical Forum] Zip Code Formats - SSRS

     

    Hi Gloria,

    If you have access to the report in SQL, there is a handy built in function AF_FORMAT_STRING which should help you out. The function handles postal codes for whatever country the address is from.

    Here's a quick sample on how to use it:

    SELECT [dbo].AF_FORMAT_STRING(a.postal_code, c.zip_mask)
    FROM   [dbo].T_ADDRESS a
    LEFT JOIN [dbo].TR_COUNTRY c ON a.country = c.id 

    Hope that helps a bit!

    From: Gloria Ormsby <bounce-gloriaormsby5026@tessituranetwork.com>
    Sent: 11/11/2014 10:41:23 AM

    Hello All,

    I need to display zip codes in an SSRS report. It is complicated because the format is different for different countries. The only thing I have found is to deal with the formatting in the Excel spreadsheet when the data is exported. I can convert the cells to a Number format and then define the format as Special and Zip Code + 4. Is there a better way?

     

    Gloria




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

  • Former Member
    Former Member $organization

    Excellent! Thank you Paul.

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Paul Kappel
    Sent: Tuesday, November 11, 2014 11:02 AM
    To: Gloria Ormsby
    Subject: Re: [Tessitura Technical Forum] Zip Code Formats - SSRS

     

    Hi Gloria,

    If you have access to the report in SQL, there is a handy built in function AF_FORMAT_STRING which should help you out. The function handles postal codes for whatever country the address is from.

    Here's a quick sample on how to use it:

    SELECT [dbo].AF_FORMAT_STRING(a.postal_code, c.zip_mask)
    FROM   [dbo].T_ADDRESS a
    LEFT JOIN [dbo].TR_COUNTRY c ON a.country = c.id 

    Hope that helps a bit!

    From: Gloria Ormsby <bounce-gloriaormsby5026@tessituranetwork.com>
    Sent: 11/11/2014 10:41:23 AM

    Hello All,

    I need to display zip codes in an SSRS report. It is complicated because the format is different for different countries. The only thing I have found is to deal with the formatting in the Excel spreadsheet when the data is exported. I can convert the cells to a Number format and then define the format as Special and Zip Code + 4. Is there a better way?

     

    Gloria




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

  • Former Member
    Former Member $organization

    I tried inserting this into my procedure but anything with zip +4 does not have a value. My zip mask is @@@@@-@@@@. This is what I have for that section of the procedure:

     

    --postal code

    CREATE TABLE #zip (

    postal_code VARCHAR(10),

    description VARCHAR(55))

     

    INSERT INTO #zip

    Select       

          postal_code = convert(varchar(10), 

          dbo.AF_FORMAT_STRING(a.postal_code, b.zip_mask)),

          country = b.description

    From  t_address a

          JOIN tr_country b ON a.country = b.id

     

     

    What shall I do to display those zips with +4?

     

    Gloria

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Paul Kappel
    Sent: Tuesday, November 11, 2014 11:02 AM
    To: Gloria Ormsby
    Subject: Re: [Tessitura Technical Forum] Zip Code Formats - SSRS

     

    Hi Gloria,

    If you have access to the report in SQL, there is a handy built in function AF_FORMAT_STRING which should help you out. The function handles postal codes for whatever country the address is from.

    Here's a quick sample on how to use it:

    SELECT [dbo].AF_FORMAT_STRING(a.postal_code, c.zip_mask)
    FROM   [dbo].T_ADDRESS a
    LEFT JOIN [dbo].TR_COUNTRY c ON a.country = c.id 

    Hope that helps a bit!

    From: Gloria Ormsby <bounce-gloriaormsby5026@tessituranetwork.com>
    Sent: 11/11/2014 10:41:23 AM

    Hello All,

    I need to display zip codes in an SSRS report. It is complicated because the format is different for different countries. The only thing I have found is to deal with the formatting in the Excel spreadsheet when the data is exported. I can convert the cells to a Number format and then define the format as Special and Zip Code + 4. Is there a better way?

     

    Gloria




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

  • Former Member
    Former Member $organization

    Never mind folks. I got it to work by eliminating the temp table and including the function in the select. A much better and faster way!

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Paul Kappel
    Sent: Tuesday, November 11, 2014 11:02 AM
    To: Gloria Ormsby
    Subject: Re: [Tessitura Technical Forum] Zip Code Formats - SSRS

     

    Hi Gloria,

    If you have access to the report in SQL, there is a handy built in function AF_FORMAT_STRING which should help you out. The function handles postal codes for whatever country the address is from.

    Here's a quick sample on how to use it:

    SELECT [dbo].AF_FORMAT_STRING(a.postal_code, c.zip_mask)
    FROM   [dbo].T_ADDRESS a
    LEFT JOIN [dbo].TR_COUNTRY c ON a.country = c.id 

    Hope that helps a bit!

    From: Gloria Ormsby <bounce-gloriaormsby5026@tessituranetwork.com>
    Sent: 11/11/2014 10:41:23 AM

    Hello All,

    I need to display zip codes in an SSRS report. It is complicated because the format is different for different countries. The only thing I have found is to deal with the formatting in the Excel spreadsheet when the data is exported. I can convert the cells to a Number format and then define the format as Special and Zip Code + 4. Is there a better way?

     

    Gloria




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

  • Former Member
    Former Member $organization

    Excellent! Thank you Paul.

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Paul Kappel
    Sent: Tuesday, November 11, 2014 11:02 AM
    To: Gloria Ormsby
    Subject: Re: [Tessitura Technical Forum] Zip Code Formats - SSRS

     

    Hi Gloria,

    If you have access to the report in SQL, there is a handy built in function AF_FORMAT_STRING which should help you out. The function handles postal codes for whatever country the address is from.

    Here's a quick sample on how to use it:

    SELECT [dbo].AF_FORMAT_STRING(a.postal_code, c.zip_mask)
    FROM   [dbo].T_ADDRESS a
    LEFT JOIN [dbo].TR_COUNTRY c ON a.country = c.id 

    Hope that helps a bit!

    From: Gloria Ormsby <bounce-gloriaormsby5026@tessituranetwork.com>
    Sent: 11/11/2014 10:41:23 AM

    Hello All,

    I need to display zip codes in an SSRS report. It is complicated because the format is different for different countries. The only thing I have found is to deal with the formatting in the Excel spreadsheet when the data is exported. I can convert the cells to a Number format and then define the format as Special and Zip Code + 4. Is there a better way?

     

    Gloria




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

  • Former Member
    Former Member $organization

    Never mind folks. I got it to work by eliminating the temp table and including the function in the select. A much better and faster way!

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Paul Kappel
    Sent: Tuesday, November 11, 2014 11:02 AM
    To: Gloria Ormsby
    Subject: Re: [Tessitura Technical Forum] Zip Code Formats - SSRS

     

    Hi Gloria,

    If you have access to the report in SQL, there is a handy built in function AF_FORMAT_STRING which should help you out. The function handles postal codes for whatever country the address is from.

    Here's a quick sample on how to use it:

    SELECT [dbo].AF_FORMAT_STRING(a.postal_code, c.zip_mask)
    FROM   [dbo].T_ADDRESS a
    LEFT JOIN [dbo].TR_COUNTRY c ON a.country = c.id 

    Hope that helps a bit!

    From: Gloria Ormsby <bounce-gloriaormsby5026@tessituranetwork.com>
    Sent: 11/11/2014 10:41:23 AM

    Hello All,

    I need to display zip codes in an SSRS report. It is complicated because the format is different for different countries. The only thing I have found is to deal with the formatting in the Excel spreadsheet when the data is exported. I can convert the cells to a Number format and then define the format as Special and Zip Code + 4. Is there a better way?

     

    Gloria




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

  • Former Member
    Former Member $organization

    Excellent! Thank you Paul.

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Paul Kappel
    Sent: Tuesday, November 11, 2014 11:02 AM
    To: Gloria Ormsby
    Subject: Re: [Tessitura Technical Forum] Zip Code Formats - SSRS

     

    Hi Gloria,

    If you have access to the report in SQL, there is a handy built in function AF_FORMAT_STRING which should help you out. The function handles postal codes for whatever country the address is from.

    Here's a quick sample on how to use it:

    SELECT [dbo].AF_FORMAT_STRING(a.postal_code, c.zip_mask)
    FROM   [dbo].T_ADDRESS a
    LEFT JOIN [dbo].TR_COUNTRY c ON a.country = c.id 

    Hope that helps a bit!

    From: Gloria Ormsby <bounce-gloriaormsby5026@tessituranetwork.com>
    Sent: 11/11/2014 10:41:23 AM

    Hello All,

    I need to display zip codes in an SSRS report. It is complicated because the format is different for different countries. The only thing I have found is to deal with the formatting in the Excel spreadsheet when the data is exported. I can convert the cells to a Number format and then define the format as Special and Zip Code + 4. Is there a better way?

     

    Gloria




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

  • Former Member
    Former Member $organization

    I tried inserting this into my procedure but anything with zip +4 does not have a value. My zip mask is @@@@@-@@@@. This is what I have for that section of the procedure:

     

    --postal code

    CREATE TABLE #zip (

    postal_code VARCHAR(10),

    description VARCHAR(55))

     

    INSERT INTO #zip

    Select       

          postal_code = convert(varchar(10), 

          dbo.AF_FORMAT_STRING(a.postal_code, b.zip_mask)),

          country = b.description

    From  t_address a

          JOIN tr_country b ON a.country = b.id

     

     

    What shall I do to display those zips with +4?

     

    Gloria

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Paul Kappel
    Sent: Tuesday, November 11, 2014 11:02 AM
    To: Gloria Ormsby
    Subject: Re: [Tessitura Technical Forum] Zip Code Formats - SSRS

     

    Hi Gloria,

    If you have access to the report in SQL, there is a handy built in function AF_FORMAT_STRING which should help you out. The function handles postal codes for whatever country the address is from.

    Here's a quick sample on how to use it:

    SELECT [dbo].AF_FORMAT_STRING(a.postal_code, c.zip_mask)
    FROM   [dbo].T_ADDRESS a
    LEFT JOIN [dbo].TR_COUNTRY c ON a.country = c.id 

    Hope that helps a bit!

    From: Gloria Ormsby <bounce-gloriaormsby5026@tessituranetwork.com>
    Sent: 11/11/2014 10:41:23 AM

    Hello All,

    I need to display zip codes in an SSRS report. It is complicated because the format is different for different countries. The only thing I have found is to deal with the formatting in the Excel spreadsheet when the data is exported. I can convert the cells to a Number format and then define the format as Special and Zip Code + 4. Is there a better way?

     

    Gloria




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

  • Former Member
    Former Member $organization

    Never mind folks. I got it to work by eliminating the temp table and including the function in the select. A much better and faster way!

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Paul Kappel
    Sent: Tuesday, November 11, 2014 11:02 AM
    To: Gloria Ormsby
    Subject: Re: [Tessitura Technical Forum] Zip Code Formats - SSRS

     

    Hi Gloria,

    If you have access to the report in SQL, there is a handy built in function AF_FORMAT_STRING which should help you out. The function handles postal codes for whatever country the address is from.

    Here's a quick sample on how to use it:

    SELECT [dbo].AF_FORMAT_STRING(a.postal_code, c.zip_mask)
    FROM   [dbo].T_ADDRESS a
    LEFT JOIN [dbo].TR_COUNTRY c ON a.country = c.id 

    Hope that helps a bit!

    From: Gloria Ormsby <bounce-gloriaormsby5026@tessituranetwork.com>
    Sent: 11/11/2014 10:41:23 AM

    Hello All,

    I need to display zip codes in an SSRS report. It is complicated because the format is different for different countries. The only thing I have found is to deal with the formatting in the Excel spreadsheet when the data is exported. I can convert the cells to a Number format and then define the format as Special and Zip Code + 4. Is there a better way?

     

    Gloria




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

  • Former Member
    Former Member $organization

    I tried inserting this into my procedure but anything with zip +4 does not have a value. My zip mask is @@@@@-@@@@. This is what I have for that section of the procedure:

     

    --postal code

    CREATE TABLE #zip (

    postal_code VARCHAR(10),

    description VARCHAR(55))

     

    INSERT INTO #zip

    Select       

          postal_code = convert(varchar(10), 

          dbo.AF_FORMAT_STRING(a.postal_code, b.zip_mask)),

          country = b.description

    From  t_address a

          JOIN tr_country b ON a.country = b.id

     

     

    What shall I do to display those zips with +4?

     

    Gloria

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Paul Kappel
    Sent: Tuesday, November 11, 2014 11:02 AM
    To: Gloria Ormsby
    Subject: Re: [Tessitura Technical Forum] Zip Code Formats - SSRS

     

    Hi Gloria,

    If you have access to the report in SQL, there is a handy built in function AF_FORMAT_STRING which should help you out. The function handles postal codes for whatever country the address is from.

    Here's a quick sample on how to use it:

    SELECT [dbo].AF_FORMAT_STRING(a.postal_code, c.zip_mask)
    FROM   [dbo].T_ADDRESS a
    LEFT JOIN [dbo].TR_COUNTRY c ON a.country = c.id 

    Hope that helps a bit!

    From: Gloria Ormsby <bounce-gloriaormsby5026@tessituranetwork.com>
    Sent: 11/11/2014 10:41:23 AM

    Hello All,

    I need to display zip codes in an SSRS report. It is complicated because the format is different for different countries. The only thing I have found is to deal with the formatting in the Excel spreadsheet when the data is exported. I can convert the cells to a Number format and then define the format as Special and Zip Code + 4. Is there a better way?

     

    Gloria




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

  • Former Member
    Former Member $organization

    Never mind folks. I got it to work by eliminating the temp table and including the function in the select. A much better and faster way!

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Paul Kappel
    Sent: Tuesday, November 11, 2014 11:02 AM
    To: Gloria Ormsby
    Subject: Re: [Tessitura Technical Forum] Zip Code Formats - SSRS

     

    Hi Gloria,

    If you have access to the report in SQL, there is a handy built in function AF_FORMAT_STRING which should help you out. The function handles postal codes for whatever country the address is from.

    Here's a quick sample on how to use it:

    SELECT [dbo].AF_FORMAT_STRING(a.postal_code, c.zip_mask)
    FROM   [dbo].T_ADDRESS a
    LEFT JOIN [dbo].TR_COUNTRY c ON a.country = c.id 

    Hope that helps a bit!

    From: Gloria Ormsby <bounce-gloriaormsby5026@tessituranetwork.com>
    Sent: 11/11/2014 10:41:23 AM

    Hello All,

    I need to display zip codes in an SSRS report. It is complicated because the format is different for different countries. The only thing I have found is to deal with the formatting in the Excel spreadsheet when the data is exported. I can convert the cells to a Number format and then define the format as Special and Zip Code + 4. Is there a better way?

     

    Gloria




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!