New line based on fixed characters in a field

  • hi

    i have a field available in ssrs that has all of the address lines joined together split only by ~

    eg

    164 street~city~state~postcode

    i want this to post as

    164 street

    city

    state

    postcode

     

    i have tried using replace statements, with no luck, what is the most efficient way of doing what i require?

     

    cheers

     

    mal

  • Pretty sure that REPLACE is going to be the best way to approach that, without changing the underlying data structure and manipulating the data.  I prefer to store my addresses differently, but that is just me.  I would store it either in an address1, address2, address3, city, state, zip, country set of columns OR, if I only had 1 column to store it in, store it WITH the newlines and don't substitute them as I do the insert.

    But, without changing the underlying data, a REPLACE on the application side (SSRS) is the approach I would take.

    This stackoverflow link has tips on how to do it:

    https://stackoverflow.com/questions/18306328/ssrs-how-to-add-in-new-line

    In your case, it would be something like:

    =Replace(Field!Address.Value, "~", VbCrLf)

    NOTE - I am completely guessing on the field name.  Change that to match what you have.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • I would do this in the SQL code - instead of trying to do it in SSRS.  In SQL:

    Select Address = replace(OldAddress, '~', char(10))

    If it must be done in SSRS directly - then a similar approach should work.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • As a bit of a side bar, there's little chance that I'd leave such a delimited column in one piece.  I'd have split it into separate attribute columns on the way into the table.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • hi folks

     

    yes i agree, would be much cleaner split into separate fields in sql, but i am working with a reporting solution that actually takes the address fields (all of which are stored in their own db field) concatenates them together to send the data to ssrs, then limits me to using these fields in ssrs hence the need to explode them back out again in the report - makes sense to someone i am sure!

    thanks for input, been a while since i have been on here - but helpful as always guys

     

    mal

  • Do you have access to the SQL that is used to get the data from the database?  Or - is this a case where you only have access to execute the stored procedure?

    If you don't have access to the SQL code and are only able to do this in SSRS - then lookup the Chr function.  It can be used the same way as the CHAR function in SQL Server.

    What I would do is create a custom field on the dataset - using an expression to replace the ~ characters with Chr(10).  For example: =Replace(Fields!YourField.Value, "~", Chr(10)).

    Then use that new custom field as the source in the report.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • hi Jeffrey

    no unfortunately all i have access to is predefined data definitions from the software i am reporting on, it does not even allow me to see the code behind how the calculated fields are created

    ill try replace again, but i think i may need to update my ssrs skills also now that i can no longer get around these types of issues in a sql query

     

    mal

  • How are you defining the dataset for the report?

    Using Visual Studio (SSDT) - you can right-click on the Dataset and 'Add Calculated Field'.  That brings up the Dataset Properties dialog on the Fields where you can add a field.  At the bottom, define a new field using the replace function.

    Then in the report use this new field as the source for your text box - using Chr(10).

     

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply