[TIP] Workaround for Multi-Select Parameters in SSRS for Dynamics 365

 Workaround for Multi-Select Parameters in SSRS for Dynamics 365

Today, we're going to tackle a common challenge for report developers working with SSRS in Dynamics 365 - handling multi-select parameters. If you've tried to use multi-select parameters in your FetchXML, you've probably found out that FetchXML doesn't natively support arrays or lists for conditions. But don't worry, we've found a workaround!

This method involves fetching all data and then applying the filter on the client side, specifically at the table or matrix level within the report. Let's walk through the steps:

1.  Modify your FetchXML to return all data. The first step is to alter your FetchXML query to fetch all the data, without the condition of filtering based on the tri_facilityid. Simply remove the line <condition attribute="tri_facilityid" operator="eq" value="@FacilityId" />  from your FetchXML.

2. Change the parameter property to enable "Allow multiple values".

3. Apply the filter at the table or matrix level. After we've fetched all the data, we can apply our multi-select filter directly on our table or matrix in the report:

- Right-click on your table or matrix, and select Tablix Properties.

- Navigate to the Filters tab, then click Add.
- In the Expression dropdown, select your tri_facilityidValue field (or tri_facilityid, depending on your data).- 
- In the Operator dropdown, select In.

- In the Value field, enter =Parameters!FacilityId.Value.This tells SSRS to only display rows where the tri_facilityidValue is in the list of values selected in your FacilityId parameter.

 

One thing to note is that while this workaround is useful for dealing with multi-select parameters, it may have performance implications if your dataset is large, since SSRS needs to fetch all data and then apply the filter on the client side.

Despite these limitations, SSRS in Dynamics 365 is still a powerful tool for generating data-driven reports. As always, understanding the tool's constraints and knowing how to work around them is key to leveraging its full capabilities.

We hope you found this tip useful! Stay tuned for more insights and workarounds for your reporting needs in Dynamics 365. Feel free to share your own tips and experiences in the comments below.

Happy reporting!