Posted by: EPM Partners on March 12, 2014
At EPM Partners we come across this issue quite frequently and so we would like to share the solution with you.
Problem:
When a SQL Server Reporting Services 2005, 2008 or 2008 R2 report gets exported to PDF, the spaces of a Project Server Multiline text field appear so condensed, that it looks like a long line with out any spaces. The same report looks fine on screen and exports with proper spaces to Microsoft Word. This is apparently an SSRS bug. Follow the solution below to fix this space problem.
Solution:
1) Add the following code to your report by right clicking outside the report body and then selecting Report Properties. Click code section to add the code.
Public FUNCTION RemoveHtml(ByVal Text As String) AS String
IF Text IsNot Nothing AND Text <> “” Then
Dim objRegExp AS NEW System.Text.RegularExpressions.Regex(“<(.|\n)+?>”)
Text = Replace(Text,” ”, ” “)
Text = Replace(Text,”'”, “‘”)
Text = Replace(Text,”:”, “:”)
Text = Replace(Text,”&”, “&”)
Text = Replace(Text,”"”, “”””)
Text = Replace(Text,”<”, “<“)
Text = Replace(Text,”>”, “>”)
Text = Replace(Text,”©”, “”)
Text = Replace(Text,”<ul>”, “[[ul]]”)
Text = Replace(Text,”<li>”, “[[li]]”)
Text = Replace(Text,”</ul>”, “[[/ul]]”)
Text = Replace(Text,”</li>”, “[[/li]]”)
Text = Replace(Text,”</p>”, vbcrlf)
Text= objRegExp.Replace(Text, “”)
Text = Replace(Text,”[[ul]]”,”<ul>”)
Text = Replace(Text,”[[/ul]]”,”</ul>”)
Text = Replace(Text,”[[li]]”,”<li>”)
Text = Replace(Text,”[[/li]]”,”</li>”)
Text = Replace (Text,vbcrlf,”<BR>”)
Return Text
ELSE
Return “”
END IF
END Function
2) In your desired textbox in the report, use the following expression to display your multiline field:
=Code.RemoveHTML(Fields!Project_Description.Value)
(Use your Multiline text field name instead of Project_Description)
3) In report designer, select the text <<expr>> in your text box and set MarkupType property as HTML.
Your report now should export to PDF. The removeHTML function strips all the HTML codes except for new line so that your text doesn’t appear all on the same line. As an added bonus, it also preserves bullet points related HTML tags as Project Server users typically like to enter their comments in bullet points.
See more at: http://epmadvice.blogspot.com.au/2014/01/solution-spaces-removed-when-ssrs.html#sthash.uhkq9AGw.dpuf
Was this information helpful?
To get more news, events and blogs from EPM Partners sign up to our mailing list today by Clicking Here.
