Carriage return in sql is not working when exporting to SSRS

Question

Carriage return in sql is not working when exporting to SSRS

Newbi

SSCertifiable

Points: 6546
More actions
April 21, 2017 at 1:04 am

#342533

This is just a part of my sql function. I am inserting carriage return for getting next line behaviour. But it is failed when the data are exported in SSRS design layer. How can I fix it?
select isnull(case when isnull(a.BlkAptHseNo,'') ='' then '' else isnull(a.BlkAptHseNo,'') + ' ' end + case when isnull(a.street,'') ='' then '' else isnull(a.street,'') + CHAR(13) + CHAR(10)+ ' ' end + case when isnull(a.Country,'') ='' then '' else isnull(a.Country,'') + ' ' end + case when isnull(a.zip,'') ='' then '' else isnull(a.zip,'') + ' ' end, '') as FullAddress, isnull(a.zip,'') as PostalCode

Viewing 5 posts - 1 through 4 (of 4 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

You don't really say where you are doing this on your report but if you are doing this in something like a textbox, seems you would want to do this with references to the fields in the dataset itself using VBCRLF. You can find plenty of examples of that type of thing if you search on mailing labels and SSRS - you would do something similar with the labels.

Sue

Newbi SSCertifiable Points: 6546 More actions · Answer 2

Sue_H - Friday, April 21, 2017 3:07 PM
You don't really say where you are doing this on your report but if you are doing this in something like a textbox, seems you would want to do this with references to the fields in the dataset itself using VBCRLF. You can find plenty of examples of that type of thing if you search on mailing labels and SSRS - you would do something similar with the labels.
Sue

Thank you for your reply.
I am trying to do in SQL side. I tried adding CHAR(13)+CHAR(10) where I want have next line behaviour. I can see the result in SSMS(text view) but when I export it to the SSRS side, the effect are gone and I am just getting the whole line of text string depending on the length of my text box. Do you happen to have any idea why it that so? Is it a limitation of it that we just simply can't use this method at all?

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 3

SSRS returns HTML that is rendered by the web browser. If you're putting the CR/LF into an HTML string that is to be rendered/displayed, then unfortunately, the browser will ignore it.

You need to either send the line break HTML tag <br> or wrap your text in <p> and </p> tags. Or start playing with <div> tags, but that gets quite heavy quite quickly...

Thomas Rushton
blog: https://thelonedba.wordpress.com

Sue_H SSC Guru Points: 90860 More actions · Answer 4

Newbi - Tuesday, April 25, 2017 12:49 AM
Sue_H - Friday, April 21, 2017 3:07 PM
You don't really say where you are doing this on your report but if you are doing this in something like a textbox, seems you would want to do this with references to the fields in the dataset itself using VBCRLF. You can find plenty of examples of that type of thing if you search on mailing labels and SSRS - you would do something similar with the labels.
Sue
Thank you for your reply.
I am trying to do in SQL side. I tried adding CHAR(13)+CHAR(10) where I want have next line behaviour. I can see the result in SSMS(text view) but when I export it to the SSRS side, the effect are gone and I am just getting the whole line of text string depending on the length of my text box. Do you happen to have any idea why it that so? Is it a limitation of it that we just simply can't use this method at all?

Because of different displays from different clients - SSMS to text is not like SSMS to grid which is not like SSRS, etc. They are all different. Some of it in SSRS is controlled though the functions, expressions using VB and some of it is in HTML. Usually the formatting will (should) be done on the client side anyway.
In SSRS, the data formatting like your example is often done with expressions. Where or how you are using it, I don't know. But it's not going to work doing it in the SQL statement. The data sets, queries are just data in SSRS.

Sue