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).

Answer 2

I found a solution for this using the dataset filters, I removed all the conditions from the fetchxml(I left only those conditions that aren't optional). Then I made all the parameters multi valued, if the user select more than one vale it will make the parameter optional, otherwise if he selects one value it will search with it.

I added a filter for each condition, for example if you have a condition in the fetch xml like this:

<condition attribute="attribute1" operator="in" value="@a1" /> 
...
...

Then I added a filter for each conditions like so:

• Right click the data set that the table is reading from.
• Click the properties.
• Choose the filters tab.
• Click the Add button.
• In the expression choose the fild [attribute1].
• For the operator choose in.
• For the value, click the function button fx and enter the following expression =IIf(Parameters!a1.Count>1,Fields!attribute1.Value,Parameters!a1.Value).

So if the user selects only one value for the parameter values, the condition will be false then the field attribute1 will be compared to the parameters value, therefore the condition will be applied otherwise the field value will be compared to its value, which is always be true, so that the condition will be always true and ignored.

The problem with thi solution is that, the user can't select multi values and search with them, it will be treated as it selects all the values, I tried to overcome this by:

• Checking if the selected values for the parameter values equal to the count of the total values of the dataset that the parameter is populated from or if it is not populated from a data set, compare the total values to the total count of all the values instead of checking if the count is greater than 1, but I couldn't be able to do that. I got an error that I couldn't use an aggregate in the filters expressions.

• Trying to include a null value into the parameters values (I couldn't do that in my case because the parameter values was populated from a dataset), so that I can check if the selected value is null then ignore the condition, somthing like this: =IIf(Parameters!a1.value = nothing,Fields!attribute1.Value,Parameters!a1.Value).

If you could make one of these works, then this solution will work fine.

Note that, you might need to use Parameters!a1.Label instead of Parameters!a1.Value if that field has a label attribute1name and a value attribute1, this is the way fetchxml works. So use parameter label to get the name to compare it to attribute1name or use paramter value to compare it to attribute1.