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.

 

 

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

 

 

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.

 

 

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.