Sunday, 20 August 2017

{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


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

No comments:

Post a Comment

Contact Form

Name

Email *

Message *