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)

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

Share this:

Like this:

Leave a Reply Cancel reply