Stripping HTML tags from SSRS text fields

I have worked extensively with SSRS. One of the common requirements is to strip HTML from text values displayed in reports and apply standard formatting to the text.
HTML have many encoded characters which end users don’t want to see.

The function below replaces encoded characters to the correct string
http://www.w3schools.com/tags/ref_urlencode.asp

The requirement was to add a carriage return whenever a “<br>” is found. Because in some text the “<br>” was “<BR>” and “<br/>”, additional code has been added to cater for them.

The <li>tag needed to be preserve somehow explaining why it’s been converted to “-”
Finally the function uses the Regex expression “<(.|\n)+?>” to strip any html


Public Function StripHTML(ByVal sInput As String) As String
IF sInput IsNot Nothing Then
IF(sInput <>"") Then
Dim objRegExp AS NEW System.Text.RegularExpressions.Regex("<(.|\n)+?>")

sInput = Replace(sInput, "<BR>", Chr(10))
sInput = Replace(sInput, "<br>", Chr(10))
sInput = Replace(sInput, "<br/>", Chr(10))
sInput = Replace(sInput," ", " ")
sInput = Replace(sInput, "<li>", "-")
sInput = Replace(sInput, "–", "-")
sInput = Replace(sInput, "—", "-")
sInput = Replace(sInput, "¡", "¡")
sInput = Replace(sInput, "¿", "¿")
sInput = Replace(sInput, """, "")

sInput = Replace(sInput, "‘", "‘")
sInput = Replace(sInput, "’", "’")
sInput = Replace(sInput, "«", "«")
sInput = Replace(sInput, "»", "»")
sInput = Replace(sInput, " ", " ")
sInput = Replace(sInput, "&", "&")
sInput = Replace(sInput, "¢", "¢")
sInput = Replace(sInput, "©", "©")
sInput = Replace(sInput, "÷", "÷")
sInput = Replace(sInput, ">", ">")
sInput = Replace(sInput, "<", "<")
sInput = Replace(sInput, "µ", "µ")
sInput = Replace(sInput, "·", "·")
sInput = Replace(sInput, "¶", "¶")
sInput = Replace(sInput, "±", "±")
sInput = Replace(sInput, "€", "€")
sInput = Replace(sInput, "£", "£")
sInput = Replace(sInput, "®", "®")
sInput = Replace(sInput, "§", "§")
sInput = Replace(sInput, "™", "™")
sInput = Replace(sInput, "¥", "¥")
sInput = Replace(sInput, "á", "á")
sInput = Replace(sInput, "Á", "Á")
sInput = Replace(sInput, "à", "à")
sInput = Replace(sInput, "À", "À")
sInput = Replace(sInput, "â", "â")
sInput = Replace(sInput, "Â", "Â")
sInput = Replace(sInput, "å", "å")
sInput = Replace(sInput, "Å", "Å")
sInput = Replace(sInput, "ã", "ã")
sInput = Replace(sInput, "Ã", "Ã")
sInput = Replace(sInput, "ä", "ä")
sInput = Replace(sInput, "Ä", "Ä")
sInput = Replace(sInput, "æ", "æ")
sInput = Replace(sInput, "Æ", "Æ")
sInput = Replace(sInput, "ç", "ç")
sInput = Replace(sInput, "Ç", "Ç")
sInput = Replace(sInput, "é", "é")
sInput = Replace(sInput, "É", "É")
sInput = Replace(sInput, "è", "è")
sInput = Replace(sInput, "È", "È")
sInput = Replace(sInput, "ê", "ê")
sInput = Replace(sInput, "Ê", "Ê")
sInput = Replace(sInput, "ë", "ë")
sInput = Replace(sInput, "Ë", "Ë")
sInput = Replace(sInput, "í", "í")
sInput = Replace(sInput, "Í", "Í")
sInput = Replace(sInput, "ì", "ì")
sInput = Replace(sInput, "Ì", "Ì")
sInput = Replace(sInput, "î", "î")
sInput = Replace(sInput, "Î", "Î")
sInput = Replace(sInput, "Ï", "Ï")
sInput = Replace(sInput, "ñ", "ñ")
sInput = Replace(sInput, "Ñ", "Ñ")
sInput = Replace(sInput, "ó", "ó")
sInput = Replace(sInput, "Ó", "Ó")
sInput = Replace(sInput, "ò", "ò")
sInput = Replace(sInput, "Ò", "Ò")
sInput = Replace(sInput, "ô", "ô")
sInput = Replace(sInput, "Ô", "Ô")
sInput = Replace(sInput, "ø", "ø")
sInput = Replace(sInput, "Ø", "Ø")
sInput = Replace(sInput, "õ", "õ")
sInput = Replace(sInput, "Õ", "Õ")
sInput = Replace(sInput, "ö", "ö")
sInput = Replace(sInput, "Ö", "Ö")
sInput = Replace(sInput, "ß", "ß")
sInput = Replace(sInput, "ú", "ú")
sInput = Replace(sInput, "Ú", "Ú")
sInput = Replace(sInput, "ù", "ù")
sInput = Replace(sInput, "Ù", "Ù")
sInput = Replace(sInput, "û", "û")
sInput = Replace(sInput, "Û", "Û")
sInput = Replace(sInput, "ü", "ü")
sInput = Replace(sInput, "Ü", "Ü")
sInput = Replace(sInput, "ÿ", "ÿ")
sInput = Replace(sInput, "'", "'")
sInput = Replace(sInput, "", "´")
sInput = Replace(sInput, "", "`")
RETURN objRegExp.Replace(sInput, "")
ELSE
RETURN sInput
END IF
END IF
End Function

To use the above function, follow the steps below
1. Navigate to report properties>code tab. Copy and paste the above code.

2.Call the function from any textbox value property where needed as follows:
=Code.StripHTML()

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