6

I am struggling to get an expression to work in SSRS.

I have an address block stored in an Oracle database with carriage return characters.

An example is the data stored in the database is:

123 Anywhere Street<CR><LF>
SOMETOWN-ON-SEA<CR><LF>
NARNIA<CR><LF>
AA1 1AA<CR><LF>

I have created an expression on the field in SSRS that looks like this:

=Replace(Fields.Address, vbCRLF, " ")

Seems like the right thing to do, but what I end up with is:

123 Anywhere StreetSOMETOWN-ON-SEANARNIAAA1 1AA

What did it do with the spaces I told it to add? Why are they not there?

Note: OK so yeah, I could just do this replace thing in PL/SQL and yeah that will solve my problem, but it's not really the point. I want to know why it isn't working in SSRS

Pedram
  • 6,256
  • 10
  • 65
  • 87
Jay P
  • 71
  • 1
  • 4
  • 2
    Not sure if it's the same thing but I have seen something similar once which turned out to be due to an Internet Explorer behaviour that stripped out trailing spaces from HTML. I know this doesn't appear to make sense because the lines should be appended but try using chr(160) instead of " "... – Nathan Griffiths Jan 11 '16 at 19:28
  • 1
    Is the ` – Jonnus Jan 12 '16 at 08:52
  • 1
    Sorry if it wasn't clear the '' part is me attempting to show that each line has those hidden characters at the end. It wasn't meant as a literal string. – Jay P Jan 12 '16 at 09:30
  • 1
    @Nathan Just tried the chr(160) thing... still no joy :( At the moment I'm just running it in the preview pane of Visual Studio. The weird thing is, the vbCRLF is getting correctly replaced, just the spaces at the end are not getting appended. Tried it in Chrome and IE too, does the same thing – Jay P Jan 12 '16 at 09:41

1 Answers1

6

The carriage return is usually represented by characters 10 and 13. This works for me:

=Replace(Replace(Fields!ADDRESS.Value, Chr(13), ""), Chr(10), " ")
StevenWhite
  • 5,907
  • 3
  • 21
  • 46