How to pass null or empty to FetchXML filters?

Question

Scenario: I've a text field in Dynamics CRM on Order Type. This field is integrated with some other systems and it'll be accepting only already stated list of values; like, ABC, IJK, XYZ etc. Now I can query this field using Advanced find if it contain data or not.

Now in report, I've a parameter that is having all those possible value and one additional as "Does not contain data" and its value is empty string. I've also enabled this report parameter for multi-select. But I am unable to get the orders if any of the value is selected from report parameters.

Below is my FetchXML query, Please let me know what I am missing in below.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="invoicedetail">
   <attribute name="productid" />
    <attribute name="invoicedetailid" />
    <attribute name="tv_ordertype" />
    <order attribute="productid" descending="false" />
    <filter type="and">
        <condition attribute="tv_ordertype" operator="in" value="@Order_Types" />
    </filter>
  </entity>
</fetch>

score 1 · Answer 1 · answered May 08 '19 at 06:52

1

Unfortunately you cannot combine the values ("ABC", "IJK", "XYZ") with an empty string option. Think about how SSRS will parse the empty string into FetchXml:

<condition attribute="tv_ordertype" operator="in" value="" />

Because the in operator has an empty string, there will be no matching results.

One approach that might work is to change your FetchXml to use an or filter, like this

<filter type="or">
    <condition attribute="tv_ordertype" operator="null" />
    <condition attribute="tv_ordertype" operator="in" value="@Order_Types" />
</filter>

This will now return all values from CRM that match your criteria OR have a null tv_ordertype

Then you can apply additional filtering at the tablix / report level

answered May 08 '19 at 06:52

jasonscript

6,039
3
28
43

What is "@Order_Types"? I can't find any documentation on this type of variable that starts with @ in FetchXML. – Shadowfax May 06 '21 at 17:43
@Shadowfax it's from the original question – jasonscript May 07 '21 at 02:08
right, it is in the original question, but is it a valid variable or is it just some gibberish meant as a placeholder? – Shadowfax May 07 '21 at 17:50
@Shadowfax oh, I think you can either do comma-separated values, but normally it would be like this: `option1option2` – jasonscript May 11 '21 at 02:42

AnkUser · Answer 2 · 2019-04-10T14:13:48.520

-1

Try something like below

  <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
    <entity name="invoicedetail">
        <attribute name="productid" />
        <attribute name="invoicedetailid" />
        <attribute name="tv_ordertype" />
        <order attribute="productid" descending="false" />      
        <filter type='and'>         
<condition attribute="tv_ordertype" operator="in" value="@Order_Types" />
        </filter>
    </entity>
</fetch>

edited Apr 10 '19 at 14:13

answered Apr 10 '19 at 13:08

AnkUser

5,421
2
9
25

it don't work out for me and ended up on error: `The FetchXML parameter "@Order_Types" cannot obtain multiple values. Change Parameter "@Order_Types" to single value parameter and try again` – Mohsin A. Apr 10 '19 at 14:00
I updated my answer, Does your parameter get all the Order_Types? Can you post screenshot of your Parameter and your Query. Ref Link https://nishantrana.me/2016/03/04/sample-fetch-xml-report-using-multivalued-parameter-in-operator-in-crm/ – AnkUser Apr 10 '19 at 14:15

score -1 · Answer 3 · answered Apr 12 '19 at 16:07

Your fetch XML should look like this:

<?xml version="1.0" encoding="UTF-8"?>
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
   <entity name="invoicedetail">
      <attribute name="productid" />
      <attribute name="invoicedetailid" />
      <attribute name="tv_ordertype" />
      <order attribute="productid" descending="false" />
      <filter type="and">
         <condition attribute="tv_ordertype" operator="in"/>
            <value>@Order_Types[0]</value>
            <value>@Order_Types[1]</value>
            <!-- etc -->
         </condition>
      </filter>
   </entity>
</fetch>

How to pass null or empty to FetchXML filters?

3 Answers3