CRM Reporting Undocumented Feature Uncovered

Most who live in the Microsoft Dynamics CRM world, like Rockton Software, understand that there has been a large change with the functionality of reporting with CRM 2011 and CRM Online. Most of these changes being brought forth were to allow the custom reporting in CRM online. In order for Microsoft to make reporting happen for their online offering of this product, they had to bring in their own custom language—FetchXML—for retrieving data from the online servers. To keep the data safe and secure in the online servers, Microsoft had to implement RDL sandboxing on the SSRS servers. This brings out the ‘one step forward two steps back’ type of scenarios.

 

For the first time, users are able to create and upload custom reports into CRM Online that are more advanced and personalized for their organization. With that being said, the SSRS Sandboxing brings in many restrictions (see MSDN documentation). Those who are accustomed to using custom code and scripts behind expressions find themselves left without a way to manipulate the data in some of the most basic ways.

 

It appears that the CRM developers have written a helper class specifically for reporting to help reduce some of these limitations that become a nuisance when writing reports for CRM online—great news! Unfortunately, the methods that were exposed to helper class are not documented nor discussed anywhere that I have been able to find.

 

For those of you who you have dug into the out of box CRM reports (including reports built using the built in Report Builder) you will see that most of the reports have a reference to the following assembly:

Microsoft.Crm.Reporting.RdlHelper, Version=5.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35

 

This RdlHelper file is packaged and installed on the SQL Reporting Server with the rest of the CRM reporting extensions, which should make it available to those running on both: onPremise and Online versions of CRM. However, to the be able to use what they have written for us, we need to know what is available and how to use it.

 

I have gone into reports that CRM provides OOB and have pulled out the specific areas the Microsoft has used the RdlHelper class in their reports:

CDate(Microsoft.Crm.Reporting.RdlHelper.DateTimeUtility.ConvertUtcToLocalTime

(DateTime.UtcNow, Parameters!CRM_UserTimeZoneName.Value))    

 

This is the most common helper that I had found on most reports, it appears to take in your system UTC time and apply the UTC offset from CRM for the timestamp that is placed on the footer of the reports.  So it will display as time run based off of your CRM time zone setting and not the PC time zone setting.

Microsoft.Crm.Reporting.RdlHelper.ReportUtility.GetArrow(Parameters!

CRM_SortField.Value,"name", Parameters!CRM_SortDirection.Value)

 

This takes in 3 string inputs. The CRM parameter for what field is supposed to be sorted, the name of the field, and the sort direction and then will return back the sort code value to the column that matches what CRM has so that when the sorting is changed in CRM, the reporting will reflect the same.

Microsoft.Crm.Reporting.RdlHelper.HtmlUtility.GetImage

(Fields!ActivityTypeCode.Value)

 

This takes in an ObjectTypeCode and returns the CRM defined 16x16 image associated with the entity type to drop on the report for easy identification of the record type.

Microsoft.Crm.Reporting.RdlHelper.UserSummary.GetDot

(count(Fields!systemuserid.Value))

 

This appears to be specific to the User Summary report, it takes in an integer and if it is set to 1, then it will return a dot, otherwise it will return blank.  They are using it to set the indicator on security roles for each user to signify that the user is a member of that security role.

Microsoft.Crm.Reporting.RdlHelper.NumberUtility.GetAbsValue

(First(Fields!EndTimeDelay.Value, "DSTimeParam"))

 

This takes in an integer and returns its absolute value.

Microsoft.Crm.Reporting.RdlHelper.HtmlUtility.RemoveHtml

(Fields!description.Value)

 

This takes in a string of text and strips out the markup tags and returns the plain text back.

 

This list is pretty small now, but I could imagine that there are many more methods in the assemble that are available. We just don’t have any documentation behind them at this point. To me this looks like a great direction for the CRM development team to take as it allows end users regain some usability of the reporting engine that are removed by enabling SSRS Sandboxing. I can only imagine that this RdlHelper class will grow over time as the Microsoft developers begin to convert the out-of-box reports over to using FetchXML language.

 

I have placed a suggestion in the Microsoft Connect page asking for this RdlHelper class to get documented in the CRM SDK.  If you get a moment, please go out and give the suggestion your thumbs up, so maybe we can get some more information behind what all exists in the RdlHelper assembly that they have created.

 

Written By Bryan Page, .NET Developer at Rockton Software, Microsoft Dynamics Add-On Partner.