New line based on fixed characters in a field

Question

New line based on fixed characters in a field

dopydb

SSCertifiable

Points: 6687
More actions
June 1, 2021 at 8:27 am

#3890439

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

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

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

Mr. Brian Gale SSC-Insane Points: 24675 More actions · Answer 1

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.

Jeffrey Williams SSC Guru Points: 90245 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

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)

dopydb SSCertifiable Points: 6687 More actions · Answer 4

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

Jeffrey Williams SSC Guru Points: 90245 More actions · Answer 5

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

dopydb SSCertifiable Points: 6687 More actions · Answer 6

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

Jeffrey Williams SSC Guru Points: 90245 More actions · Answer 7

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