FetchBased Reports in Dynamics CRM

If you are familiar with Microsoft Dynamics CRM, you must be aware with fetchXML query. It’s one type of query language supported by Microsoft dynamics CRM and natively render in C# expresses. Fetch XML uses to directly query Microsoft dynamics CRM entities or related entities with filter criteria and where clauses.

Sample fetch XML to retrieve accounts –

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">

<entity name="account">

<attribute name="name"/>

<attribute name="primarycontactid"/>

<attribute name="telephone1"/>

<attribute name="accountid"/>

<order attribute="name" descending="false"/>

</entity>

</fetch>

This is simple example but it also supports complex queries. Some of examples you can check here –

https://msdn.microsoft.com/en-in/library/gg328332.aspx

Easy and best way to create fetch example query is advance filter in dynamics CRM. Advance filter provides you visual dashboard to create and download fetch XML queries as below.

It takes you to next page where you can compose fetch query and download it.

Join fetch query example listed below to retrieve data from contact and account entities-

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">

<entity name="account">

<attribute name="name"/>

<attribute name="primarycontactid"/>

<attribute name="telephone1"/>

<attribute name="accountid"/>

<order attribute="name" descending="false"/>

<link-entity name="contact" from="parentcustomerid" to="accountid" alias="ab">

<filter type="and">

<condition attribute="parentcustomeridname" operator="like" value="a%"/>

</filter>

</link-entity>

</entity>

</fetch>

Fetch Xml queries get translated into C# using fetch express and entity collection holder. To create fetch xml based reports these components required, we move forward.

1. Microsoft Visual studio 2010 or 2012 (download https://www.microsoft.com/en-in/download/details.aspx?id=36020)

2. Business Intelligence Development Studio or Sql server Data tool (download https://www.microsoft.com/en-in/download/details.aspx?id=36843)

3. Microsoft Dynamics CRM 2016 Reporting Authoring Extension (download https://www.microsoft.com/en-us/download/details.aspx?id=50375).

Once your development environment is ready with all these components, open visual studio and select

Right click on report folder and select add new report. It opens report wizard. Type new data source name and then select Microsoft Dynamics 365 Fetch in type dropdown. Put Dynamics CRM URL and provide credentials and click Next button to select organization and move next.

Wizard next step is to specify query to compose required data for report and click next.

Click Next button and create report page. Modify reports as per need and save it. You can check report preview using preview button.

To deploy created reports login in Dynamics CRM and open reports sections under Tools top navigation. Select new report and in report type use select existing file.

Once report is uploaded you can run after select report from reports list and run report.