«
»

Displaying Email Text in a CRM Report

 Posted by Cory Fellers in Dynamics CRM on August 28, 2009

If you have ever built a custom report Dynamics CRM that references the Description field of the email entity, you probably realized that the body of an email is stored in the database in HTML format. Thus your report may have looked something like this:

CRM Email Format

In order to display an email correctly in an SSRS report you need to add a function into the code library of the report and then reference that function in your data field. To drop in the code, navigate to Report -> Report Properties -> Code. The following function can be used to strip HTML from an input field:

Function RemoveHTML(strText)
strText=System.Text.RegularExpressions.Regex.Replace(strText,"\n"," ")
strText=System.Text.RegularExpressions.Regex.Replace(strText,"( )+"," ")
strText=System.Text.RegularExpressions.Regex.Replace(strText,"<( )*head([^>])*>","<head>",System.Text.RegularExpressions.RegexOptions.IgnoreCase)
strText=System.Text.RegularExpressions.Regex.Replace(strText,"(<( )*(/)( )*head( )*>)","</head>",System.Text.RegularExpressions.RegexOptions.IgnoreCase)
strText=System.Text.RegularExpressions.Regex.Replace(strText,"(<head>).*(</head>)",string.Empty,System.Text.RegularExpressions.RegexOptions.IgnoreCase)
strText=System.Text.RegularExpressions.Regex.Replace(strText,"<( )*script([^>])*>","<script>",System.Text.RegularExpressions.RegexOptions.IgnoreCase)
strText=System.Text.RegularExpressions.Regex.Replace(strText,"(<( )*(/)( )*script( )*>)","</script>",System.Text.RegularExpressions.RegexOptions.IgnoreCase)
strText=System.Text.RegularExpressions.Regex.Replace(strText,"(<script>).*(</script>)",string.Empty,System.Text.RegularExpressions.RegexOptions.IgnoreCase)
strText=System.Text.RegularExpressions.Regex.Replace(strText,"<( )*style([^>])*>","<style>",System.Text.RegularExpressions.RegexOptions.IgnoreCase)
strText=System.Text.RegularExpressions.Regex.Replace(strText,"(<( )*(/)( )*style( )*>)","</style>",System.Text.RegularExpressions.RegexOptions.IgnoreCase)
strText=System.Text.RegularExpressions.Regex.Replace(strText,"(<style>).*(</style>)",string.Empty,System.Text.RegularExpressions.RegexOptions.IgnoreCase)
strText=System.Text.RegularExpressions.Regex.Replace(strText,"<( )*br( )*>",vbCrLf,System.Text.RegularExpressions.RegexOptions.IgnoreCase)
strText=System.Text.RegularExpressions.Regex.Replace(strText,"<( )*li( )*>",vbCrLf,System.Text.RegularExpressions.RegexOptions.IgnoreCase)
strText=System.Text.RegularExpressions.Regex.Replace(strText,"<( )*div([^>])*>",vbCrLf,System.Text.RegularExpressions.RegexOptions.IgnoreCase)
strText=System.Text.RegularExpressions.Regex.Replace(strText,"<( )*tr([^>])*>",vbCrLf,System.Text.RegularExpressions.RegexOptions.IgnoreCase)
strText=System.Text.RegularExpressions.Regex.Replace(strText,"<( )*p([^>])*>",vbCrLf,System.Text.RegularExpressions.RegexOptions.IgnoreCase)
strText=System.Text.RegularExpressions.Regex.Replace(strText,"<[^>]*>",string.Empty,System.Text.RegularExpressions.RegexOptions.IgnoreCase)
strText=System.Text.RegularExpressions.Regex.Replace(strText," "," ",System.Text.RegularExpressions.RegexOptions.IgnoreCase)
strText=System.Text.RegularExpressions.Regex.Replace(strText,"•"," * ",System.Text.RegularExpressions.RegexOptions.IgnoreCase)
strText=System.Text.RegularExpressions.Regex.Replace(strText,"<","<",System.Text.RegularExpressions.RegexOptions.IgnoreCase)
strText=System.Text.RegularExpressions.Regex.Replace(strText,">",">",System.Text.RegularExpressions.RegexOptions.IgnoreCase)
strText=System.Text.RegularExpressions.Regex.Replace(strText,"‹","<",System.Text.RegularExpressions.RegexOptions.IgnoreCase)
strText=System.Text.RegularExpressions.Regex.Replace(strText,"›",">",System.Text.RegularExpressions.RegexOptions.IgnoreCase)
strText=System.Text.RegularExpressions.Regex.Replace(strText,"™","(tm)",System.Text.RegularExpressions.RegexOptions.IgnoreCase)
strText=System.Text.RegularExpressions.Regex.Replace(strText,"⁄","/",System.Text.RegularExpressions.RegexOptions.IgnoreCase)
strText=System.Text.RegularExpressions.Regex.Replace(strText,"&","&",System.Text.RegularExpressions.RegexOptions.IgnoreCase)
RemoveHTML=strText
End Function

You can call this new function by placing the following line in your report TextBox:

=code.RemoveHTML(Fields!description.Value)

The resulting report should now look more like this:

New CRM Email Format

, , , , ,

This entry was posted on August 28, 2009, 5:48 pm and is filed under Dynamics CRM. You can follow any responses to this entry through RSS 2.0. You can leave a response, or trackback from your own site.

  1. #1 by David on November 22, 2009 - 9:29 pm

    Sorry if I come across as dumb – this has been doing my head in for days. Where abouts does this code need to go? I have assumed that this goes in to the SSRS Report Server manager, but it keeps generating errors. Or does this need to go in through the CRM interface – if so, where?

    Thanks!

    Quote

  2. #2 by Cory Fellers on December 16, 2009 - 11:07 am

    David, I should have been more specific. I am using Microsoft Visual Studio to develop my custom reports for CRM 4.0. If you are using VS as well you can drop the first bit of code above into Report -> Report Properties -> Code.

    The second line of code will go into the actual Text Box where you are displaying the body of the email.

    Quote
(will not be published)