#MSDynCRM #CRM2013 Using FetchXML versus SQL script for writing Microsoft Dynamics CRM SSRS custom reports

This might sound a known fact but you will be surprised that quite a few people don’t know why and when do they need to use FetchXML for writing an SQL Server Reporting Services report as opposed to writing common SQL Server script.

Here is how Microsoft Defines FetchXML:

FetchXML is a proprietary query language that is used in Microsoft Dynamics CRM 2013 and Microsoft Dynamics CRM Online. It’s based on a schema that describes the capabilities of the language. The FetchXML language supports similar query capabilities as query expressions. In addition it is used as a serialized form of query, used to save a query as a user-owned saved view in the userquery entity and as an organization-owned saved view in the savedquery entity.

A FetchXML query can be executed by using the IOrganizationService.RetrieveMultiple method. You can convert a FetchXML query to a query expression with the FetchXmlToQueryExpressionRequest message.
For information about how to use LINQPad to work with FetchXML, see this blog post: Getting FetchXML from LINQPad: http://blogs.msdn.com/b/devkeydet/archive/2012/04/22/getting-fetchxml-from-linqpad.aspx

Now to answer the main question of this post: When should I use FetchXML for reports?

As a fact, you cannot write SSRS reports with direct SQL script on Microsoft Dynamics CRM Online. Hence, if you want to write an SSRS report for Dynamics CRM Online, you have to write your query in FetchXML.

Within your SSRS report, you will need to create a dataset that returns the data you require in your report and then everything else will be the same as per any other SSRS report.

Please note however that you do require Microsoft Dynamics CRM Report Authoring Extension installed to be able to write FetchXML queries and to connect to your Dynamics CRM Online organisation.

Additionally, at the time of this post, you can only write Custom SSRS reports for Dynamics CRM using Visual Studio 2010 with SQL Server Data Tools SSDT OR Business Intelligence Development Studio (SQL 2008 and VS 2008 with BI)

You cannot write SSRS reports in Visual Studio 2012, 2013 at this point of time. SSDT for Visual 2013 is not currently available due to some issues.

Finally, here are some sample FetchXML queries:

Total number of Leads (Count - Aggregate):
<fetch distinct='false' mapping='logical' aggregate='true'> 
 <entity name='lead'> 
 <attribute name='fullname' alias='lead_count' aggregate='count'/> 
 </entity> 
</fetch>
Total number of Opportunities (Count - Aggregate):
<fetch distinct='false' mapping='logical' aggregate='true'> 
 <entity name='opportunity'> 
 <attribute name='name' alias='opportunity_count' aggregate='count'/> 
 </entity> 
</fetch>
Number of leads by Category:
<fetch distinct='false' mapping='logical' aggregate='true'> 
 <entity name='lead'> 
 <attribute name='fullname' alias='lead_count' aggregate='count'/> 
 <filter type="and">
 <condition attribute="crm_leadtype" operator="eq" value="100000001" />
 </filter>
 </entity>
</fetch>
<!-- where crm_leadtype is an option set (category) and its value is integer-->
The "Not Equal" filter condition example:
<condition attribute="crm_leadtype" operator="neq" value="100000001" />
FetchXML Average aggregate:
<fetch distinct='false' mapping='logical' aggregate='true'> 
 <entity name='opportunity'> 
 <attribute name='estimatedvalue' alias='estimatedvalue_avg' aggregate='avg' /> 
 </entity> 
</fetch>
FetchXML Maximum Aggregate:
<fetch distinct='false' mapping='logical' aggregate='true'> 
 <entity name='opportunity'> 
 <attribute name='estimatedvalue' alias='estimatedvalue_max' aggregate='max' /> 
 </entity> 
</fetch>
FetchXML Sum Aggregate:
<fetch distinct='false' mapping='logical' aggregate='true'> 
 <entity name='opportunity'> 
 <attribute name='estimatedvalue' alias='estimatedvalue_sum' aggregate='sum' /> 
 </entity> 
</fetch

More information and samples can be found here:
http://msdn.microsoft.com/en-us/library/gg309565(v=crm.6).aspx

Hope this helps!