Work with NULL Parameters in Dynamics 365 Fetch XML Report

Problem Statement:

I come across a requirement to implement a Report for Online CRM. There are 4 Parameters like “Lead Source”, “Industry”, “State” and “PostalCode”.

Lead Source and Industry are Option sets – So, I have created as “Multi select” parameters and used “IN” condition inside Data set. No issues here.

“State” and “PostalCode” – These are String fields. I have created as Parameters by allowing NULL values. But, if I include these attribute conditions in Filter, Fetch XML returning 0 records while running report if User does not select any value because there is no way to Ignore the Fetch XML condition if Parameter does not contains value. [If SQL SSRS Report, we could have write a Data Set query conditions based by Parameter value].

Workaround:

1. Removed the condition for “State” and “PostalCode” attributes from Fetch XML

2. Added filter at “Tablix” level.

3. Right click on “Tablix”  – select “Tablix Properties”

4. Go to “Filters” – Click “Add”

5. Select the field “State” under “Expression”, Operator as “=”

6. Write Expression for Value as “IF State Parameter has Value, Consider Parameter Value ELSE consider State Value”. [By this, Tablix shows records based on Parameter selection. If no selection returns all values].

=IIF(IsNothing(Parameters!State.Value),Fields!State.Value,Parameters!State.Value)

Hope this helps someone