{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

{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

Xrm.Page.getAttribute("statuscode").getSelectedOption()
{"value":1,"text":"Active"}



Get the selected option's value

Xrm.Page.getAttribute("statuscode").getValue()
1



Get the selected option's text/label

Xrm.Page.getAttribute("statuscode").getText()
Active



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

Xrm.Page.getAttribute("statuscode").getOptions()
[{"value":1,"text":"Active"},{"value":100000000,"text":"Test1"},"value":100000001,"text":"Test2"}]



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

Xrm.Page.getAttribute("statuscode").setValue(100000000)



Remove an option from optionset - Remove Test1

window.frames[0].Xrm.Page.getControl("statuscode").removeOption(100000000)



Add an option to optionset - Add Test1 Again


Xrm.Page.getControl("statuscode").addOption({value:100000000,text:"Test1"})



Remove all options from an optionset

Xrm.Page.getControl("statuscode").clearOptions()


Hope this helps!