-1

The question is based on SQL Server not about ssrs NEW Line

i had Already go through the stackoverflow below links related to this topic

1)New line in Sql Query

2)New line in sql server

3)New line in Sql Query

But i didn't get any answer for my situation

My problem is that i need a newline character in select query results

Example : i have a string like below 

Today is Friday Yesterday is Thursday

and when I select this varchar value from my table I need it to to appear like this in my SSRS report:

Today is Friday
Yesterday is Thursday

I have tried many ways like 

select 'Today is Friday' +char(13)+'Yesterday is Thursday'

but it gives me a result Today is Friday Yesterday is Thursday

but when i use print insted of select then i got the results what i want But i cannot use print in my scenario because i need this result in aother query and that query i have used for ssrs reporting purpose

I know i can split this query and show it in two rows but in my case i can't do something like that too because the results i have used in many select querys.

i don't know it is possible in Sqlserver, if it is possible then please help me to solve my problem

Edit : please help me if it is possible in Grid Mode rather than text mode

Community
  • 1
  • 1
Arunprasanth K V
  • 20,733
  • 8
  • 41
  • 71
  • 1
    What about char(13)+char(10)? – xQbert May 22 '15 at 12:28
  • @xQbert same result i had tried that too but no use it works with print but does not work with select – Arunprasanth K V May 22 '15 at 12:30
  • 1
    char(13)+char(10) works if you display the results as text (`Ctrl+T`) instead of in a grid (`Ctrl+D`) – amcdermott May 22 '15 at 12:32
  • Where is the display occurring? – xQbert May 22 '15 at 12:33
  • @xQbert I need to display it in a ssrs report – Arunprasanth K V May 22 '15 at 12:36
  • So maybe... Double click on the filed value to open place holder property window and under general tab Change Markup type from "none-plain text only" to "HTML -Interpret HTML tags as Style" or... http://www.sqlchick.com/entries/2010/11/12/creating-a-line-break-within-an-ssrs-expression.html – xQbert May 22 '15 at 12:40
  • possible duplicate of [SSRS how to add in New line](http://stackoverflow.com/questions/18306328/ssrs-how-to-add-in-new-line) – Tab Alleman May 22 '15 at 13:44
  • @TabAlleman who told you my question is about ssrs new line ? i had already mention my question clearly then how it would be ssrs new line ?? – Arunprasanth K V May 22 '15 at 13:57
  • Your comment above to xQbert: "I need to display it in a ssrs report" Did I misinterpret that? I believe you have a basic misunderstanding. There is no such thing as a newline in SQL Server. There are only characters which will render as a newline in the program that you use to view them. If you are interested in how the text displays in SSRS, then you have an SSRS question. Not a SQL Server question. SQL Server is only storing information, not displaying it. – Tab Alleman May 22 '15 at 14:00
  • @TabAlleman it is my final purpose what in ssrs i can't do anything because i had already written an algoritham for generating string based on some cases any only inside that algorithm i can understand which one will be come under newline . so i can't do anything in ssrs. so i need a solution in sql server it self – Arunprasanth K V May 22 '15 at 14:06
  • I think you're saying you've coded yourself into a corner and are unwilling to consider starting over with a different approach. I'm afraid you have to. There is NOTHING you can do in SQL Server to return data that won't have to do something special with on the SSRS side to make it display as a new line. There IS NO "solution in sql server itself". Sorry. Both Gabor and Scott have given you answers that will work. – Tab Alleman May 22 '15 at 14:10
  • @TabAlleman yes i am ready to take different approach. But without knowing which part need to be in new line how i can proceed ? – Arunprasanth K V May 22 '15 at 14:14

4 Answers4

0

Since when you use print you do see the line break, it seems to me like the problem is that you execute this query in SSMS, while the result is displayed as a grid.
Try changing the display result to text and you will see the line break just like you see it when you use print instead of select.
Btw, I think you better use char(13)+char(10) as xQbert suggested in the comments.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • yes in text mode it will be works fine but how can i use this text mode in ssrs reporting when i return the query results i think it will be return as grid – Arunprasanth K V May 22 '15 at 12:35
  • I've actually never had to deal with SSRS in 10 years of working with sql server, so I have no idea. – Zohar Peled May 22 '15 at 12:36
0

If you are doing it in Managment Studio this works fine:

select 'Today is Friday' +char(13)+'Yesterday is Thursday'

If result to grid is selected in Managment Studio then it is printed in one line becouse this works in that way. If you copy and paste it from grid to notepad you will see it works. Try checking result to Text. It works to.

Arkadiusz
  • 489
  • 2
  • 10
  • yes in text mode it will be works fine but how can i use this text mode in ssrs reporting when i return the query results i think it will be return as grid – Arunprasanth K V May 22 '15 at 12:35
0

I have no experience with SSRS, but maybe this link is helpful for You: SSRS how to add in New line

This suggest to use expression, like this:

=Replace(Field!Names.Value, ",", VbCrLf)
Community
  • 1
  • 1
Gabor Rajczi
  • 471
  • 2
  • 9
  • yes it is possible but i can't split it using any special characters because sometimes the string contains special characters so when i split it in ssrs then may be the logic fails so i need to split it inside the sql function for generating the string – Arunprasanth K V May 22 '15 at 12:53
  • Can't You use some special delimiter? Then You can replace this special string with VbCrLf. – Gabor Rajczi May 22 '15 at 13:01
  • what will do if string contain special delimiters – Arunprasanth K V May 22 '15 at 13:40
0

If you need to display the new line in an SSRS report, try using <br /> instead of CHAR(10) - and make sure to set the control that contains this text to allow HTML.

enter image description here

Scott Ivey
  • 40,768
  • 21
  • 80
  • 118
  • Please read my question it is not related to ssrs. i need an sql server solution – Arunprasanth K V May 22 '15 at 14:04
  • your comment an hour ago says - "yes in text mode it will be works fine but how can i use this text mode in ssrs reporting when i return the query results i think it will be return as grid" - my example shows how to make it work for SSRS. How things display in SSMS is completely separate from SSRS. – Scott Ivey May 22 '15 at 14:06
  • yes i had commented like that . and still the problem remains same because only inside my string generating function i can find which all strings are come under newline. i know i can show strings in new line in ssrs but how can i know which one will be come under new line ? my string contain special characters letters anything so i can't split it – Arunprasanth K V May 22 '15 at 14:09