How to Make a Condition Optional in FetchXML

Question

I have some SQL reports that has very slow performance, so we converted all of them into FetchXML, but all the SQL reports has all the conditions optional, something like this:

SELECT 
  ...
FROM ...
WHERE (@operator          = 'All' OR Operator          = @operator)
  AND (@new_gen_comp_name = 'All' OR new_gen_comp_name = @new_gen_comp_name)
  ...

In the parameters values, there is a value All if the user select this value, the condition will be ignored and therefore it will get all the values from that field.

Now I want to do that in FetchXML, I tried to put two conditions with filter or between them, one for the value and another to include null values like this:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
   <entity name="incident"> 
      ...
      ...
      <filter type="and">
         <filter type="and">
           <condition attribute="createdon" operator="on-or-after" value="@StartDate" /> 
           <condition attribute="createdon" operator="on-or-before" value="@EndDate" /> 
         </filter>
         <filter type="or">
           <condition attribute="new_gen_comp_type" operator="in" value="@new_gen_comp_type" /> 
           <condition attribute="new_gen_comp_type" operator="null" /> 
         </filter>
      </filter>

      ...
      ...
  </entity>
</fetch>

This worked fine only if the user select all the values for the parameter @new_gen_comp_type, but the problem is if the user select only specific values, it will include the null values too, and that is wrong.

So, is there any way to make these conditions optional in case if the user select select all for the values of the parameter like in SQL?

Answer 1

Since you're doing this in SSRS, you don't have an option to change the Fetch XML which is what you really need to do. (If the user selects "ALL", don't include a constraint on new_gen_comp_type)

The only option I can think of is kind of dumb but it should work. Create a new attribute on Incident new_all that is defaulted to "ALL", and run an update statement to populate all of your existing Incidents to "ALL". Then change your FetchXml filter to:

<filter type="or">
    <condition attribute="new_gen_comp_type" operator="eq" value="@new_gen_comp_type" /> 
    <condition attribute="new_all" operator="eq" value="@new_gen_comp_type" /> 
</filter>

If the user selects "ALL" the second statement will be true and everything will be returned. If the user selects something besides ALL, the second statement will always be false and the first statement will only return what matches.

Please note that attribute operator="in" will not work with multiple values. You need to make a value child tag with each value...

From the XSD:

The attribute "value" is used for all operators that compare to a single value (for example, eq). The element "value" is used for operators that compare to multiple values (for example, in). Some operators require neither the attribute "value" or the element "value" (for example, null).