I’ve been working with Dynamics CRM reports quite a bit over the last few months. Most of the work has been using the BI Dev Studio (BIDS) in Visual Studio. It definitely falls into the artful science category for me, with flavors of Excel, SQL Server, and even Publisher when developing a report.
In the case of displaying Email activities, specifically the body of an HTML email, the art takes a decidedly sad turn.
Okay, let’s see what that looks like in a simple report that shows a summary of email messages….
This is useful for…nearly nobody.
There are many ways to try to tackle this…ranging from CRM plug-ins, to complex report design, to, well, something a mentor of mine tipped me off to.
This gets used in the report design as an expression, replacing the normal field reference in the report design. It uses a basic utility that strips out the HTML leaving only the plain text behind. Okay, if that sounded clunky, a picture is worth a thousand words:
There are certainly more elegant ways of doing this, but if the key is to provide easy, readable email contents in a report this sure foots the bill! From a user perspective, to see the full HTML version, I like to add in a clickable link to the email that pops the CRM Email Activity record.
I picked up a little tidbit while working through some applications of this: If you try passing a null field using this utility it returns an ugly little #Error instead of a blank string. Also, SSRS will still throw the #Error even if you’ve included an IIF statement in the expression to make sure there’s a value before calling the utility. Below is a little workaround that gets the job done.
Microsoft.Crm.Reporting.RdlHelper.HtmlUtility.RemoveHtml(field & “”)
If anyone knows an easier way to do that, please drop the details in the comments section.
Again, there’s surely a prettier way to handle this, rendering HTML in the report or including some basic formatting, but this is pretty lightweight and works well when printing a basic list of emails with the body text in a report.
As far as I can tell, this utility is an undocumented feature of CRM, so quite technically speaking it is not supported by Microsoft and is something that they could yank out in a future release without notice. That said, this is the method used in the out-of-the-box CRM reports to strip out HTML tags.
|Objective:||Strip out HTML tags from an email message when displaying in a CRM report|
|CRM Version:||Undocumented feature. I’ve tested with CRM 2011 through UR8|
|Valid as of:||9/10/2012|
|Permissions Required:||Create/Write reports.|
I received a question regarding a problem someone was having when importing a report that leveraged the expression above. After a little troubleshooting, the answer was that the report lacked a reference to the following Microsoft assembly:
Microsoft.Crm.Reporting.RdlHelper, Version=22.214.171.124, Culture=neutral, PublicKeyToken=31bf3856ad364e35
Vanilla CRM reports and those reports created using the built-in CRM Report Wizard include this reference automatically. However if you build your own report from scratch, you’ll need to add this manually. With the report file open in Visual Studio BI Dev Studio, hit the menu item at Report->Report Properties… Next, click Add and paste in the reference to the assembly and click OK.
Thanks for the question! Other comments and questions are welcome.