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!
3 comments:
You can use ""+vbnewline in fields also.
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.
Matt,
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
Post a Comment