Displaying SharePoint RichText field as HTML interpreted in SSRS

SSRS Reports have a placeholder property HTML – Interpret HTML tags as styles. That property allows you to use HTML tags while displaying report.

devenv_2016-01-19_14-28-40

We have to face the problem when we want to display SharePoint`s RichText field content is SSRS Report. RichText field may contain some characters that are stored different in SharePoint DB and cannot be interpreted by SSRS as HTML (eg. :, &, {, <, >,  list elements, etc.)

Solution for this issue is to use some custom code in report definition and use is in problematical fields. You have to do it as follows:

1. Right-click outside report body and choose Report properties

2. Select Code and paste custom code

devenv_2016-01-19_13-53-44

Here’s full 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= objRegExp.Replace(Text, "")
 'Replace Zero Width Space Character
 Text = Replace(Text,ChrW(8203), "")
 Text = Replace(Text," ", " ")
 Text = Replace(Text,"'", "'")
 Text = Replace(Text,":", ":")
 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 = Replace(Text,"[[ul]]","<ul>")
 Text = Replace(Text,"[[/ul]]","</ul>")
 Text = Replace(Text,"[[li]]","<li>")
 Text = Replace(Text,"[[/li]]","</li>")
 Text = Replace (Text,vbcrlf,"<BR>")
 Text = Replace (Text, "{","{" )
 Text = Replace (Text, "}", "}")
 Return Text
 ELSE
 Return ""
 END IF
 END Function

You can modify code by deleting some Replace… rows or by adding (if you have problems with different characters.

3. Then you must use this code (as a function – we called it RemoveHtml) in placeholders that use HTML Interpret feature and replacing is required.

devenv_2016-01-19_13-56-44

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s