{Know-how} D365 - SSRS Report Prefiltering fetchxml/SQL

Hello All,

FetchXML and SQL querying are some of the best possible ways to retrieve data from MSCRM for BI or Data manipulation.

Previous articles around fetchxml querying are as follows, now lets jump into fetchxml/sql prefiltering

1. Timezone based querying
2. Hierarchical querying
3. Multi entity or conditions

What is prefiltering and why should I use it?

 As the name suggests, before execution of the report we restrict the scope to work based on relevant records. This is improves performance and also enables advanced find filtering before report execution.

How to enable prefiltering for FetchXML datasets?

• Under entity node, add an attribute enableprefiltering attribute and set its value to true

 <fetch distinct="false" mapping="logical">
   <entity name="account" enableprefiltering="1">
      <attribute name="accountid" />
      <attribute name="name" />      
   </entity>
 </fetch>

How to enable pre-filtering for SQL datasets?

• Add an alias with prefix CRMAF_ to the filtered view name

SELECT accountid, name  
FROM FilteredAccount AS CRMAF_FilteredAccount

For more details please refer,
https://technet.microsoft.com/en-us/library/dn531099.aspx