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.
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:
The result is A, B and C on a separate line.
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.
In MDX we can also do something like this:
MEMBER [Measures].[Split Members] AS
“Line 1” + Chr(10) + “Line 2”
} ON 0
FROM [Adventure Works]
This is much like in SQL – we just use the Chr() function to do the same.
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:
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.