Solution: Spaces removed when SSRS Report exported to PDF

Me and some of my workmates have come across this issue quite a few times so thought about sharing the solution with everyone.

 

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 fields 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 disappeared spaces problem.

 

ProjectStatusSummary

 

Solution:

 

1) Add the following code to your report by right clicking outside 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)+?>") 

               'Replace Zero Width Space Character 
               Text = Replace(Text,ChrW(8203), "")

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

 

Update (28/07/2014) : An old colleague Martin Laukkanen got deeper into this issue and found out that a Zero Width Space Character (&#8203;) does also cause the same issue. I have updated the VB Code to eliminate this little bugger too.

4 comments:

CDG said...

Hi - I have applied this solution to a report we have in test - it works when selecting a single report and exporting to PDF however if more than one report (we have a parent report containing a sub report) is selected the logic seems to be ignored - some of the text is concatenated, the spaces are stripped and we get one long line of text. Any help appreciated..

Hammad Arif said...

Hay thanks for this mate. Its always satisfying to find more cleaner explanation for the issues. isn't it :)
I have updated the blog with your findings.

Vivek said...

Here is the solution :

SELECT REPLACE(ColumnName, NCHAR(8203), '') AS ColumnName

Hammad Arif said...

Hi Vivek,

Yes this is the correct solution as described in Martin's blog http://nearbaseline.com/blog/2014/07/ssrs-report-export-to-pdf-removes-spaces-from-text/

Post a Comment