reporting-servicesvisual-studio-2015fetchxmlxrm

How do I link the same entity in FetchXml (ssrs) with a different prefilterparametername?

I'm building a SSRS report for Dynamics CRM so I have to use FetchXml. For example I have 10 accounts and I have to substract 5 accounts form a second query.

I prepared 2 fetchxml reporting parameters:

CRM_FilteredAccount for the 10 accounts

CRM_FilteredAccountNeg for the accounts I have to substract

And I have the Dataset shown below.

Dataset so far:

<fetch distinct="false" useraworderby="false" no-lock="false" mapping="logical" >
  <entity name="account" enableprefiltering="1" prefilterparametername="CRM_FilteredAccount" >
    <attribute name="name" alias="name" />
  </entity>
</fetch>

(Not working) Dataset with link-entity with different parameter:

<fetch distinct="false" useraworderby="false" no-lock="false" mapping="logical" >
  <entity name="account" enableprefiltering="1" prefilterparametername="CRM_FilteredAccount" >
    <attribute name="name" alias="name" />
    <link-entity name="account" enableprefiltering="1" prefilterparametername="CRM_FilteredAccountNeg" from="accountid" to="accountid" link-type="outer" alias="neg" >
      <filter>
        <condition entityname="neg" attribute="accountid" operator="null" />
      </filter>
    </link-entity>

  </entity>
</fetch>

I expected the first filterparametet to give a result of 10 accounts. Then with the link-entity with the second filterparameter to give a result of only 5 accounts remaining.

Solution

  • Alright let’s do something like below: 1st dataset lets call it “Negative “ For this dataset use fetchxml retrieving account where account id is not null. This dataset will get us 5 records. Now for this dataset we will have accountid field use this field as Parameter and call is “NegativeAccounID” ie it will contain Guid of all account which have to be excluded from our Next fetchxml

    Now 2nd dataset, lets call it “Result” For this dataset use fetchxml retrieving all account where accountid not eq NegativeAccountId (parameter) we defined.

    This will give you exact result.

    You will have to undesirable how parameter works.