Line Breaks for nvarchar TextArea fields in SQL Reports

This one had me stumped for a while. I was writing a report for a CRM client who had a number of nvarchar textarea fields on an entity in CRM. The fields had lots of free-form text with line breaks in them. For example:

This is line 1.
This is line 2.
This is line 3.

When I was creating the report in Visual Studio/SQL Reporting Services, the fields rendered correctly, but when I deployed it to CRM and ran the report, all the lines ran together, like this:

This is line 1. This is line 2. This is line 3.

I tried everything I could think of to format the field in the report layout, but nothing worked. After some googling around, I realized I needed to do some manipulation in my query. So I changed the relevant part of my select statement to something like this:

REPLACE(CAST(CRMAF_FilteredEntity.new_CustomField AS nvarchar(MAX)), CHAR(10),
CHAR(13) + CHAR(10)) AS CustomField

What this does is renders the field and replaces the line feed ("CHAR(10)") with both a Carriage Return and a Line Feed ("CHAR(13) + CHAR(10)"). And now my reports render correctly!


Anonymous said...

You can use ""+vbnewline in fields also.

Matt Wittemann said...

Yes, you can use +vbnewline if you want a new line after the contents of the field are displayed, but I don't think that helps if the actual content of the field's data value includes line breaks. So for example, if the content of the field is "Employer" and you want a new line displayed after that string is rendered, +vbnewline will do that, but if your single field contains multiple lines of text with line breaks, you might need to use the method described in this post - at least that's what I've found.

Michael Cross said...


This is great! I've had a need for this and couldn't quite get to the solution you did. Thanks for sharing this!

Mike Cross

ICU MSCRM © 2004-2009