RSMUS.comIssue Explanation
When writing FetchXML to use as a data source for a custom report, a nice place to get a head start is to use advanced find within the environment and use the Download Fetch XML button. I often head to advanced find for a quick way to get my field schema names. The Download Fetch XML button is great for retrieving the bit value of an option set or the GUID of a lookup value as well.
For example, if I want to create a report that returns Cases for a specific Subject, I could open advanced find, and set the ‘Look For:’ to Cases and add a filter criteria of Subject Equals [Any subject value].
Download Fetch XML will give me the following Filter node:
<filter type=”and”>
<condition attribute=”subjectid” operator=”eq” uiname=”Service” uitype=”subject” value=”{111DE3D1-21D5-E411-80EB-C4346BAD3638}” />
</filter>
In order to convert that statement into a filter statement that accepts our SSRS parameter we just drop the uniname attribute and change the GUID to a reference to our parameter:
<filter type=”and”>
<condition attribute=”subjectid” operator=”eq” uitype=”subject” value=”@paramSubject” />
</filter>
Converting an option set or lookup field into a single select parameter is easy enough, but if you try to take the same approach to creating a multi select parameter you will not be very successful.
<filter type=”and”>
<condition attribute=”subjectid” operator=”in”>
<value uiname=”Service” uitype=”subject”>{111DE3D1-21D5-E411-80EB-C4346BAD3638}</value>
<value uiname=”Query” uitype=”subject”>{0F1DE3D1-21D5-E411-80EB-C4346BAD3638}</value>
</condition>
</filter>
As you can see, Dynamics adds a value node for each value that I select as lookup value to return. If you were to do some googling you would find a huge number of articles suggesting that you could embed a piece of custom code to modify your FetchXML source text on the fly to add these value nodes for each option selected in the multi select parameter. Unfortunately, if you are working in an online deployment any custom code in an SSRS report will not execute successfully, meaning that the code to modify your FetchXML on the fly will not work.
Solution
I tried a number of different configurations and eventually found a statement that will work:
<filter type=”and”>
<condition attribute=”subjectid” operator=”in” value=”@paramSubject”>
</condition>
The above filter statement has been tested in and functions as expected in v8.2 and v9.0 for mutli-select lookup and option set parameters.
To learn more about how you can take advantage of this and other Dynamics 365 features, visit RSM’s Microsoft Dynamics 365 resource. To make sure you stay up to date with the Microsoft Dynamics Community, subscribe to our Microsoft Dynamics Community Newsletter. For more information on Microsoft Dynamics 365, contact us.
By: Steve Trefz
