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.



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.






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


                Return ""


END Function


2) In your desired textbox in the report, Use the following expression to display your multiline field.
                (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.


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

Martin Laukkanen said...

Hey Hammad, I came across this issue again myself, however this fix didn't solve it this time! Have a look at this solution I came to with some help from MS:

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

Post a Comment