Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / IN operator in FetchXM...
Microsoft Dynamics CRM (Archived)

IN operator in FetchXML based SSRS Report

(0) ShareShare
ReportReport
Posted on by Mohsin Ali 3,618

I've below FetchXML as SSRS dataset that runs after selection of certain parameter and retrieve Invoice Lines and then Factors (Custom Entity). Factors are being retrieved on the base of report parameters Territory, Selected Product(s),  Sales Type and End Time. I want to retrieve only those Factors for a product that is having Invoice Line. 

For example if ABC and XYZ are two products selected in Product Parameter and ABC is associated with any number of Invoice Lines while XYZ is not associated with any Invoice Lines. Then no factor should be retrieved against XYZ product. 

Please help to get only those factors against product that is having invoice lines against them.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="invoicedetail">
    <attribute name="productid" />
    <attribute name="productdescription" />
    <attribute name="priceperunit" />
    <attribute name="quantity" />
    <attribute name="extendedamount" />
	<attribute name="new_lineamount" />
    <attribute name="invoicedetailid" />
    <order attribute="productid" descending="false" />
	<filter type="and">
		<condition attribute="new_product" operator="in" value="@Product" />
		<condition attribute="new_ordertype" operator="in" value="@Order_Type" />
	</filter>
    <link-entity name="invoice" from="invoiceid" to="invoiceid" link-type="inner" alias="invoice">
	  <filter type="and">
        <condition attribute="msdynce_invoicedate" operator="on-or-after" value="@goal_start" />
        <condition attribute="msdynce_invoicedate" operator="on-or-before" value="@goal_end" />
      </filter>
      <link-entity name="account" from="accountid" to="customerid" link-type="inner" alias="account">
	  <attribute name="ownerid" alias="agent"/>
        <filter type="and">
          <condition attribute="accountcategorycode" operator="eq" value="1" />
          <condition attribute="orb_sanctionliststatuscode" operator="ne" value="169810001" />
        </filter>
		<link-entity name="territory" from="territoryid" to="territoryid" link-type="inner" alias="territory">
          <link-entity name="new_factorsetup" from="new_region" to="territoryid" link-type="inner" alias="factor">
            <attribute name="new_factor" />
        <filter type="and">
          <condition attribute="new_product" operator="in" value="@Product" />
		  <condition attribute="new_salestype" operator="eq" value="@Sales_Type" />
		  <condition attribute="new_validto" operator="eq" value="@End_Date" />
        </filter>
          </link-entity>
        </link-entity>
      </link-entity>
    </link-entity>
  </entity>
</fetch>


*This post is locked for comments

  • Suggested answer
    Martin Donnelly Profile Picture
    Martin Donnelly 1,030 on at
    RE: IN operator in FetchXML based SSRS Report

    We can't see an example of what your @Product variable is, but IN requires a set while EQ takes a single value:

       <filter type="and">

         <condition attribute="statecode" operator="in">

           <value>0</value>

           <value>1</value>

         </condition>

       </filter>

    I always try to build my query in advanced find to unit test it then download the fetchxml from there.

  • Mohsin Ali Profile Picture
    Mohsin Ali 3,618 on at
    RE: IN operator in FetchXML based SSRS Report

    I can't reverse the cycle. The reason is Factor setup is dependent upon 4 items, Territory, Selected Product(s), Sales Type and End Date (Report Parameters).  What I diagnose is the issue using @Product filter with IN operator at two places, firstly while retrieving invoice lines and secondly while retrieving Factors.

    At the time of retrieving Invoice Lines there is no issue and I am able to retrieve only those invoice lines that are associated with any of the Product. And then secondly, while retrieving the Factor based on Selected Product(s), I am using IN operator to get the factor based on aforementioned parameters. So while retrieving the factors, the query is returning all factors for which the product is matched with other parameters, despite the fact if there is no invoice line against a specific product.

    That is the whole story of our business requirement.

  • Suggested answer
    Charles Abi Khirs Profile Picture
    Charles Abi Khirs 3,569 on at
    RE: IN operator in FetchXML based SSRS Report

    In that case, you need to change the fetchXml by starting with new_factorsetup entity and adding the link entities to Territory > Account > Invoice and finally > Invoice Line with link-type = inner for all the relations.

    As I recommended previously, test your FecthXml query using "FetchXml Tester" plugin in the "XrmToolbox". This way, you will be sure of the end result returned by your fetchXml.

    Hope this helps!

    If found helpful, please mark the answer as verified.

    Charles Abi Khirs.

  • Mohsin Ali Profile Picture
    Mohsin Ali 3,618 on at
    RE: IN operator in FetchXML based SSRS Report

    Just cross checked and found that it was wrongly pasted here but my original report dataset is fine. Below is from original report dataset. 

    <filter type="and">
    <condition attribute="productid" operator="in" value="@Product" />
    <condition attribute="tv_ordertype" operator="in" value="@Order_Type" />
    </filter>

  • Suggested answer
    Charles Abi Khirs Profile Picture
    Charles Abi Khirs 3,569 on at
    RE: IN operator in FetchXML based SSRS Report

    As per your query, you are setting the filter for "new_product" and "new_ordertype" on the Invoice Products entity level, while, you said that it is on the Factor entity. So make sure that this is the right filter.

    Mainly the rest of the fetchXml is correct.

    In addition, what I recommend is to test your FecthXml query using "FetchXml Tester" plugin in the "XrmToolbox".

    Hope this helps!

    If found helpful, please mark the answer as verified.

    Charles Abi Khirs.

  • Mohsin Ali Profile Picture
    Mohsin Ali 3,618 on at
    RE: IN operator in FetchXML based SSRS Report

    Yes, productid is on Invoice Products while same field is on custom entity "Factor" as new_product. So I want to retrieve all invoice products and then their associated factors further based Territory (from Accounts), Sales Type (Report Parameter), Selected Product(s) and Order Type. Both Products and Order Type are coming from a dataset.

  • Suggested answer
    Charles Abi Khirs Profile Picture
    Charles Abi Khirs 3,569 on at
    RE: IN operator in FetchXML based SSRS Report

    Since you need the parameters to be required, you can define them as Visible.

    As for the query setup, it will retrieve all invoice products based on your custom field "new_product" and "new_ordertype" parameters. There is another OOB field called "productid" is this the one you meant to use?

    Otherwise, you can add another link entity to the product entity with link-type = inner in order to fetch the invoice products that  are linked to an invoice and a product.

  • Mohsin Ali Profile Picture
    Mohsin Ali 3,618 on at
    RE: IN operator in FetchXML based SSRS Report

    I've already given try to this but Order Type and Product are required parameters, so can't make them internal nor hidden.

    My worry is that current query setup is runing for products despite the fact that if its having invoice line associated with it or not.

  • Suggested answer
    Charles Abi Khirs Profile Picture
    Charles Abi Khirs 3,569 on at
    RE: IN operator in FetchXML based SSRS Report

    Hello Mohsin,

    What you can do is to create a new dataset that fetchs the "products" and "order type". In these datasets, you have to check the option "Allow Multiple Vlaues".

    You can now use the parameters with the operator "in".

    Check this link (community.dynamics.com/.../sample-fetch-xml-report-using-multivalued-parameter-in-operator-in-crm) that explains how to achieve this task.

    Hope this helps!

    If found helpful, please mark the answer as verified.

    Charles Abi Khirs.

  • Mohsin Ali Profile Picture
    Mohsin Ali 3,618 on at
    RE: IN operator in FetchXML based SSRS Report

    I need values selected in Parameter, not the exact GUIDs. I tried to add below node in FetchXML but it throws exception like: "The FetchXML parameter @Product cannot obtain multiple values.

    <filter type="and">

    <condition attribute="productid" operator="in" >

    <value>@Product</value>

    </condition>

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

    <value>@Order_Type</value>

    </condition>

    </filter>

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

🌸 Community Spring Festival 2025 Challenge Winners! 🌸

Congratulations to all our community participants!

Adis Hodzic – Community Spotlight

We are honored to recognize Adis Hodzic as our May 2025 Community…

Kudos to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard > Microsoft Dynamics CRM (Archived)

#1
Mohamed Amine Mahmoudi Profile Picture

Mohamed Amine Mahmoudi 83 Super User 2025 Season 1

#2
Community Member Profile Picture

Community Member 52

#3
Victor Onyebuchi Profile Picture

Victor Onyebuchi 6

Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans