Making a Dynamics CRM Online advanced report using FetchXML

Today to share an article about the production of reports, today we do not use SQL to do the report, we used FetchXML to do. This time our test environment is Dynamics CRM online and you should know that Dynamics CRM online can only deploy SSRS reports that are based on fetchxml production.

Since the high-level report, it must have its selling point: here, the advanced means that our report can be defined through the CRM Advanced Lookup interface to define the data source, unlike the normal SSRS report, the data source is fixed cannot be changed.

The following is the application of our report:

Customer and sales records are 1-to-many relationships, but in many cases, the customer record below is a sales record, so the dirty data can cause a lot of system problems, so we want to find a way to list all the customer records that lack sales data.

The solution is also very simple, we only need to make a left connection to the customer and sales data, we can easily put the missing sales data of the customer record pulled out. Now that the idea is set, we'll start making advanced reports:

The first is to create a report project from SQL Server tool, and then add a blank report and add a data source to it

To add a parameter, this parameter is mainly used to implement the ' advanced ' function--is to be Advanced Find Interface definition data source

For the parameter command, here the naming rules are fixed, you need to follow the crm_filteredxxxxx law, for example: Crm_filteredaccount, after the main record of the view name, where our master record is acount, The first letter of the main account should be capitalized.

Specify default values for parameter, we default to all customer records here, you can depend on your own situation

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

Configuration data collection, 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"/></link-entity><filter type= "and" ><condition entityname= "CSD" attribute = "Po_csdid" operator= "null" ></condition></filter></entity></fetch>

After the finished together, preview the report, no problem, upload it to CRM, it works as follows:

Making a Dynamics CRM Online advanced report using FetchXML