Overcoming HTML field data display issues in a Report

If you have a custom report in CRM that displays the description field of e-mails then you know that by default, the description field includes the html tags.  If you have good SQL experience then you probably already have a solution for this issue. 
 
Although I am not a SQL novice, I’m also not a skilled professional and I don’t spend much of my work week dedicated to SQL so I always have to work a little harder than many to resolve annoying issues like this.  So here is the solution for those that need it.  All you need to do is add a function to your CRM database and call that function in your query.  Here are the details.
 
First, test this in a development environment before applying it to a production server to ensure it works as expected.  Additionally, backup your production database before you add the function.  A copy of the function appears at the end of this post for your use.  The function was provided by Pinal Dave, which I found on his Blog, Journey to SQL Authority by Pinal Dave.
 
The steps are provided for SQL 2005 but they are similar if not the same in SQL 2008.
 
  • Open SQL Management Studio
  • Expand your test CRM database table view
  • Expand your test CRM Org table
  • Expand Programmibility
  • Expand Functions
  • Right-Click Scalar-valued Functions
  • Select New Scalar-valued Function
    • I use Scalar in this example because I was able to create a new function in this folder.  You can use a different Function folder if you prefer.

  • Replace the default function with the function below beginning with the word Create Function and ending with the Word Go.
  • Do not replace the verbiage above the Author, Create Date or Description.  See the screen shot below for reference

Default Function                                                                                   Replaced with New Function

                 

  • Execute the Query
  • Refresh the SQL Data Object view

  • Follow the steps above to expand the Programmability folder
  • Expand the Scalar-valued Function folder
  • Scroll to the bottom of the list
  • Right-click the function named dbo.udf_StripHTML and select Properties

 

  • Select Permissions from the left navigation link
  • Select the Add button, add the ‘CRMReaderRole’ and give the Role execute rights
  • Save your changes

The function is now ready for use.  To use the function in a query, simply call the function in the select query.  You’ll place the function name before the field that contains the html tags that you want to remove.  The name of the function is dbo.udf_StripHTML.  Here is an example of a simple query using the function"

  • select activityid, activitytypecodename, createdon, dbo.udf_StripHTML(description), owneridname from filteredactivitypointer

When you run the query, you’ll find that the description field for e-mail type activities now returns the field value minus the html tags, well, almost minus.  I did find that the function misses a couple of tags like ‘&nbsp’ and a few others.  I guess my next step will be to read a quick tutorial on writing SQL functions and I will see if I can modify the function to remove the few remaining html characters.  Better yet, feel free to post an improved function as a response to this post and I will be happy to use yours.

Function Code

CREATE FUNCTION [dbo].[udf_StripHTML]

(@HTMLText VARCHAR(MAX))

RETURNS VARCHAR(MAX)

AS

BEGIN

DECLARE @Start INT

DECLARE @End INT

DECLARE @Length INT

SET @Start = CHARINDEX(‘<‘,@HTMLText)

SET @End = CHARINDEX(‘>’,@HTMLText,CHARINDEX(‘<‘,@HTMLText))

SET @Length = (@End @Start) + 1

WHILE @Start > 0

AND @End > 0

AND @Length > 0

BEGIN

SET @HTMLText = STUFF(@HTMLText,@Start,@Length,)

SET @Start = CHARINDEX(‘<‘,@HTMLText)

SET @End = CHARINDEX(‘>’,@HTMLText,CHARINDEX(‘<‘,@HTMLText))

SET @Length = (@End @Start) + 1

END

RETURN LTRIM(RTRIM(@HTMLText))

END

GO

 

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