none
SSRS 2005 - Split multiline address field into multiple address columns

    Question

  • Hi all

    I have an address field called SiteAddress, which is multiline with carriage returns, and stores addresses as:

    SiteAddresss

    SiteABC
    1 Main Street
    Withington
    Manchester
    M20 1FT

    How do I split this address field across multiple new fields, eg:

    SiteName         AddressLine1      AddressLine2       AddressLine3     AddressLine4      Postcode
    SiteABS           1 Main Street      Withington           Manchester                                 M20 1FT


    Thanks for all help
    Naz


    • Edited by ANSA_Naz Monday, April 28, 2014 2:19 PM
    Monday, April 28, 2014 2:13 PM

Answers

  • It is simple using TSQL, refer below example,

    ;with cte as (
    	select 'site name' [address]
    	union
    	select 'address line 1' [address]
    	union
    	select 'address line 2' [address]
    	union
    	select 'address line 3' [address]
    )
    select * from (select [address], row_number() over (order by address desc) rn from cte) x
    pivot
    (max([address]) for rn in ([1],[2],[3],[4])) pvt

    However if you need in SSRS itself, you can try using custom code to split the address string by char(10) or char(13) and then insert into an array. If the input parameter is 1 return the first address from the array, 2 return the second address and so on. Custom code can be called from the textbox expression as,

    =code.getAddress(Fields!Address, 1)


    Regards, RSingh

    Monday, April 28, 2014 3:28 PM