CRM 2011: Retrieve Hierarchical Records - Recursive FetchXML

Nathan Eccles, 31 October 2012

If you have done any work with SSRS reports in CRM 2011 you will be familiar with the issues around getting reports to run in CRM Online. Most of these issues stem from the inability to use SQL to access the data, and the limitations of FetchXML. Thankfully there are almost always work-arounds, however complicated or difficult to find, which will achieve similar results. 

In the following scenario I wish to look at the Frosty’s Global account and see its address along with the addresses for all active children accounts.

In order to achieve this we need to set up layering which will traverse the hierarchical tree and return all accounts with a parent account in the list of accounts in the layer above it.

This might sound complicated, but the process for implementing it is actually very simple; 

The first layer uses pre-filtering to return the id of the account you have run the report on. This should be assigned to a parameter from use in traversing the lower layers. 

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="account" enableprefiltering="1">
    <attribute name="accountid" />
  </entity>
</fetch>

All layering Parameters (including the final Parameter, but excluding the CRM_account Parameter) should be set to “Allow multiple values”, have the parameter visibility set to “Internal”, and have their Default Values set to “Get values from a query”. They should then be set up to look at their corresponding Dataset, and the ID field of the record you are searching.

After that, each additional layer simply searches all accounts for those who have a parent account in the list from the layer above it and assign it to a unique Parameter. To ensure every layer returns a result we also return the pre-filtered account in every layer. 

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="account">
    <attribute name="accountid" />
    <filter type="or">
      <!--This changes for each layer, eg @L2 @L3 etc--> 
      <condition attribute="parentaccountid" operator="in" value="@L1" />
      <!--This remains the same for all layers--> 
      <condition attribute="accountid" operator="in" value="@L1" />
    </filter> 
  </entity>
</fetch> 

The final step is to do 1 last retrieve which returns accounts from all layers and then applies any additional filtering you wish to do on the records, such as ensuring they are active, and assigns them to the AllAccounts Parameter.

This step simply makes implementing multiple Datasets easier. The filtering on accounts can be done per Dataset, but having it all as 1 query makes it simpler to change. 

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="account">
    <attribute name="accountid" />
    <filter type="or">
      <condition attribute="accountid" operator="in" value="@L1" />
      <filter type="and">
        <filter type="or">
          <condition attribute="parentaccountid" operator="in" value="@L1" />
          <condition attribute="parentaccountid" operator="in" value="@L2" />
          <condition attribute="parentaccountid" operator="in" value="@L3" />
          <condition attribute="parentaccountid" operator="in" value="@L4" />
          <condition attribute="parentaccountid" operator="in" value="@L5" />
        </filter> 
        <condition attribute="statecode" operator="eq" value="0" />
      </filter> 
    </filter> 
  </entity>
</fetch> 

From here all you need to do is make all datasets used in the report filter on the AllAccounts Parameter and you will have successfully implemented a hierarchical retrieve that will work with CRM Online. 

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="account">
    <attribute name="accountid" />
    <attribute name="name" />
    <attribute name="address1_line1" />
    <attribute name="address1_city" />
    <attribute name="address1_country" />
    <filter type="and">
      <condition attribute="accountid" operator="in" value="@AllAccounts" />
    </filter> 
  </entity>
</fetch> 

Unfortunately using this method we are limited in the number of layers we can navigate down, as each additional layer requires an additional Dataset and Parameter. However typically this won’t be an issue as it is unlikely that you would ever have an unpredictable number of layers. 

If you have any feedback please feel free to post below. I’d love to learn from you as I hope you’ve learnt from me.