Use FetchXML to create a Dynamics CRM Online Advanced Report

Use FetchXML to create a Dynamics CRM Online Advanced Report

Today I will share with you an article on report production. Today we do not use SQL statements for reports. We use FetchXML for reports. This test environment is Dynamics CRM Online. You should know that Dynamics CRM Online can only deploy SSRS reports created based on FetchXML.

Now that we have talked about advanced reports, there must be a selling point: Here, advanced means that our reports can define data sources through the CRM advanced search interface, unlike common SSRS reports, data sources are fixed and cannot be changed.

The following describes the application scenarios of this report:

There is a one-to-many relationship between the customer and the sales record, but in many cases, the customer record is a sales record. Such dirty data may cause many system problems, so we want to find a way to list all customer records that lack sales data.

The solution is also very simple. We only need to establish a left connection between the customer and the sales data to easily pull out the customer records without sales data. Now that the idea is fixed, let's start to prepare advanced reports:

 

First, you need SQL Server Tool to create a report project, then add a blank report, and add a data source for it.

Add a parameter for the parameter. this parameter is mainly used to implement the 'extension' function. It is used to define the data source on the advanced search interface.

For the Parameter command, the naming rules here are fixed and must follow the CRM_FilteredXXXXX rule. For example: CRM_FilteredAccount, the view name of the master record must be followed. Here, the main record is Acount, the first letter of the primary Account must be capitalized.

Specify the default value for Parameter. Here, all customer records are recorded by default.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">    <entity name="account">        <all-attributes>    </all-attributes></entity></fetch>

Configure the data set. Here we use the following FetchXML:

 

<fetch distinct="true"><entity name="account" enableprefiltering="1" prefilterparametername="CRM_FilteredAccount"><attribute name="accountid"><attribute name="accountnumber"><attribute name="name"><link-entity name="p_csd" from="p_customer" to="accountid" alias="csd" link-type="outer"><attribute name="p_csdid"><filter type="and"><condition entityname="csd" attribute="po_csdid" operator="null"></condition></filter></attribute></link-entity></attribute></attribute></attribute></entity><link-entity name="p_csd" from="p_customer" to="accountid" alias="csd" link-type="outer"></link-entity></fetch>

After the report is finished, you can preview the report and upload it to CRM. The running effect of the report is as follows: