Home > SSRS > Line Breaks in SSRS

 

Line Breaks in SSRS

December 15th, 2010

There are a number of ways we can add a line break to a Reporting Services string and these have been described in multiple articles online. Doing this we can get multiple lines per cell. The following content is intended to be a quick reference rather than a description of a new or better way to do it.

SSRS Expressions

The easiest is to add a vbCrLf to an expression like this:

=”Line 1″ + vbCrLf + “Line 2″

An easy way to remember the syntax is vb for VB, Cr for Carriage Return and Lf for Line Feed.

Alternatively, you can replace any character (in example a pipe), which exists in your data with vbCrLf and are not bound to CHAR(10) only – in case your query gets simple because you already have a suitable line break string in it. If you have a string like: “A|B|C”, and you want to replace the “|” character with a line break, you can do the following:

=Replace(“A|B|C”,”|”,vbCrLf)

The result is A, B and C on a separate line.

SQL

A more flexible and many times desirable way to do it is to add the line break to the data query. In SQL this would be by adding a CHAR(10) for the same purpose since CHAR(10) is Line Feed in SQL. Have a look at this query:

SELECT ‘Line 1′ + CHAR(10) + ‘Line 2′

If you use this in SSRS you will get the same output as with vbCrLf. This way we can construct our reports with line breaks in the database, giving us some flexibility.

MDX

In MDX we can also do something like this:

WITH
MEMBER [Measures].[Split Members] AS
“Line 1″ + Chr(10) + “Line 2″
SELECT
{
[Measures].[Split Members]
} ON 0
FROM [Adventure Works]

This is much like in SQL – we just use the Chr() function to do the same.

Custom Code

We can also do the same with custom code. By using custom code we can split strings and create additional custom operations. For more info about using custom code for this purpose you can have a look at the following article:

http://www.kodyaz.com/articles/reporting-services-add-line-break-between-words-custom-code.aspx

Note that the operations that the author is performing can be done in SSRS natively with its Replace() function. Nevertheless, the article shows the fundamentals in a simple way.

Another interesting thing is that SSRS recognises CHAR(10) as both Carriage Return and Line Feed. In my testing (on SQL Server 2008 R2), CHAR(10) or Chr(10) does exactly the same as CHAR(10)+CHAR(13). Therefore we do not need to worry about adding a Carriage Return (CHAR(13)) in front of CHAR(10) in SSRS.

If for some reason you have troubles with CHAR(10) from SQL or other sources and you know it is in your character string, you can try replacing it with vbCrLf in SSRS.

If you enjoyed this post, make sure you subscribe to my RSS feed!

 

SSRS , ,

  1. | #1

    Thanks for the info. In a nutshell, SSRS is happy with Unix (LF line endings) or Windows (CRLF line endings) for text.

    One minor correction, your sentence
    Therefore we do not need to worry about adding a Line Feed (CHAR(13)) after CHAR(10) in SSRS.
    should actually read
    Therefore we do not need to worry about adding a Carriage Return (CHAR(13)) after CHAR(10) in SSRS.
    Sorry to be picky! :)

  2. | #2

    Thanks, Ian – picky is good! I just updated the post.

  3. ravi
    | #3

    Hey Thank u! Very much

  4. Gudiya
    | #4

    Thank you very much

  5. | #5

    To continue the pickiness started by Ian: it should be “in front of” instead of “after” – that’s why the VB constant is called vbCRLF and not vbLFCR :)

    So: “Therefore we do not need to worry about adding a Carriage Return (CHAR(13)) in front of CHAR(10) in SSRS.”

  6. | #6

    You guys picked me apart :) I must have drunk something before writing the post :) Thanks!

Comments are closed.