6

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?

Daryl
  • 18,592
  • 9
  • 78
  • 145
Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164

3 Answers3

4

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

Daryl
  • 18,592
  • 9
  • 78
  • 145
  • Thanks for your response, but there is no `ALL` value in the parameters values, there is however a checkbox `select all`(since I set all the parameters to allow multiple values), if I selected that checkbox the value of the parameter becomes all the values in it comma separated, so how can I know if the user selects all the values or not? I think I can compare the count of selected values of a parameter `Parameters!new_gen_comp_type.Value.Count` with the count of the values in the dataset that the parameter values populated from right? – Mahmoud Gamal Jun 19 '14 at 18:28
  • I tried to compare the count of the select values of a parameter with the values of the dataset to know if the user selected all the values or not, but there was an error that I can't use an aggregate in a calculated field. How to come over that problem to try your solution?? Also I have a lot of conditions in that report about 10 conditions, do I need to add one more entity for each condition or this new attribute `new_all` will work fine and return all the values? – Mahmoud Gamal Jun 19 '14 at 18:29
  • Also are you sure about the point that the attribute operator="in" will not work with multiple values. Because it is working fine with me and I checked it again and it works fine. And the phrase you quoted says that `in` operator should work fine with multiple values `The element "value" is used for operators that compare to multiple values (for example, in).` – Mahmoud Gamal Jun 19 '14 at 18:29
  • @MahmoudGamal you described your parameter as checking for "All" in your question, so I assumed that was what was being passed in. The same logic that I describe will still work, just instead of defaulting the value to "All" default it to your CSV i.e. "A,B,C". With that said, in your second comment, it sounds like the user can pick multiple values, not just all, if this is correct then my implementation won't work. – Daryl Jun 19 '14 at 19:10
  • @MahmoudGamal as far as the `operator="in"` is concerned, I'm not aware that CRM assumes the value is a csv and performs a split on it before passing it to SQL. The "in" operator will work fine with multiple values, but each value has to be in it's own `` element. You can't pass multiple values in the `value=""` attribute. – Daryl Jun 19 '14 at 19:13
4

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.

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
0

Although an assumption, I see you are attempting a FetchXML based report in CRM. It is suggested to use CRM default filters in this case. The query requires no condition and just have select columns.

This would allow you to have optional parameters.

[Note: Filters/Parameters are applicable only of entity/related entities being queried]

Avik
  • 1
  • 1