Removing Spaces When an SSRS Report is Exported to PDF

Posted By 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.

 

ProjectStatusSummary

 

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.

 

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,”&#160;”, ” “)

                Text = Replace(Text,”&#39;”, “‘”)

                Text = Replace(Text,”&#58;”, “:”)

                Text = Replace(Text,”&amp;”, “&”)

                Text = Replace(Text,”&quot;”, “”””)

                Text = Replace(Text,”&lt;”, “<“)

                Text = Replace(Text,”&gt;”, “>”)

                Text = Replace(Text,”&copy;”, “”)

                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.

 

Remove 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.

 


Blog Posted In Blog Posted In: How to, Troubleshooting
Blog Posted In 

Leave a Reply

Your email address will not be published.