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" />      

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

{Know-how} D365 CRM Javascript - Optionset related methods - get/set/add/remove/retrieve all/remove all

Hello all,

Optionsets are one of the key data capturing controls and manipulating options are essential to achieve an efficient UX experience in all systems including our MSCRM. Below are the quick reference to D365 CRM optionset related javascript methods, which helps in achieving the same.

Here we are going to use "Status Reason"(statuscode) field as our optionset. Lets not wait anymore, Js snippets are as follows😉

Get the selected option - returns the label/text and value


Get the selected option's value


Get the selected option's text/label


Get all the options for the optionset - Returns an array of options


Set the optionset value(selected option) - Set to Test1


Remove an option from optionset - Remove Test1


Add an option to optionset - Add Test1 Again


Remove all options from an optionset


Hope this helps!

Contact Form


Email *

Message *