FetchXML Reporting in Microsoft Dynamics CRM 2011–Part 2

 

I wrote a blog a couple of months ago about the ability to create reports in CRM 2011 using FetchXML instead of standard SQL.

In part two I’m going to cover a couple of techniques that will also be useful:

Contextual FetchXML Reports
Parameters

 

Contextual FetchXML Reports

In the ‘Normal’ world of SQL there is a process for creating reports for CRM to run contextually, that is return results contextual to the record you are running it from, for example running a report to pull back all Cases for a Contact.

The Prefix CRMAF_ was used in the query which CRM recognised as a reference to contextualise the results.

The same is accomplished in CRM with FetchXML using the following syntax (Highlighted):

<fetch distinct="false" no-lock="false" mapping="logical">
<entity name="contact" enableprefiltering="1" prefilterparametername="CRM_FilteredContact">
<attribute name="contactid" />
<attribute name="fullname" />
<link-entity name="incident" to="contactid" from="customerid" link-type="outer" alias="incident1" enableprefiltering="1" prefilterparametername="CRM_FilteredIncident">
<attribute name="prioritycode" alias="incident1_prioritycode" />
<attribute name="createdon" alias="incident1_createdon" />
<attribute name="title" alias="incident1_title" />
<attribute name="incidentid" />
<link-entity name="contact" to="customerid" from="contactid" link-type="outer" alias="LE_53CF0568">
<attribute name="fullname" alias="LE_53CF0568_fullname" />
</link-entity>
</link-entity>
</entity>
</fetch>

Parameters

So as in SQL reports, adding parameters in FetchXML reports allows the user to decide what data will be returned, based on criteria. For the following example we’ll add a date parameter for when the returned cases where created:

<fetch distinct="false" no-lock="false" mapping="logical">
<entity name="contact" enableprefiltering="1" prefilterparametername="CRM_FilteredContact"><attribute name="contactid" />
<attribute name="fullname" />
<link-entity name="incident" to="contactid" from="customerid" link-type="outer" alias="incident1" enableprefiltering="1" prefilterparametername="CRM_FilteredIncident">
<attribute name="prioritycode" alias="incident1_prioritycode" />
<attribute name="createdon" alias="incident1_createdon" />
<attribute name="title" alias="incident1_title" />
<attribute name="incidentid" />
<filter type="and">
  <condition attribute="createdon" operator="on-or-after" value="@date" />
</filter>
<link-entity name="contact" to="customerid" from="contactid" link-type="outer" alias="LE_53CF0568"><attribute name="fullname" alias="LE_53CF0568_fullname" />
</link-entity>
</link-entity>
</entity>
</fetch>

Worth noting here that there are several options for the ‘Operator’ part of the filter. They include:

eq – Equal
on-or-after
on-or-before
gt – Greater Than
lt – Less Than
ne – Not Equal To

Also note that in the example above, you could also hard code a date parameter in or use a date picker.

Hope that helps, but for more information it’s worth checking the section on Technet - http://technet.microsoft.com/en-us/library/gg328117.aspx