FetchBased Reports In Microsoft Dynamics CRM

If you are familiar with Microsoft Dynamics CRM, you must be aware of fetchXML query. It’s one type of query language supported by Microsoft Dynamics CRM and natively rendered in C# expresses. Fetch XML is used 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 a simple example but it also supports complex queries. Some of the examples you can check here.

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

It takes you to the 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>

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

1. Microsoft Visual Studio 2010 or 2012 (download from here https://www.microsoft.com/en-in/download/details.aspx?id=36020)
2. Business Intelligence Development Studio or SQL Server Data tool (download from here https://www.microsoft.com/en-in/download/details.aspx?id=36843)
3. Microsoft Dynamics CRM 2016 Reporting Authoring Extension (download from here 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 the 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's 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 a new report and in report type use select existing file. 

Once report is uploaded you can run it after selecting the report from the reports list and run the report.