Skip to content

Aggregation in FetchXmlDataSets

FetchXmlDataSets provide functionality beyond the Advanced Search, namely the ability to precompress the data by summating the number of records or values ​​in an entity field.You can get an impression of the FetchXML syntax used when you export a diagram in the CRM system, here as an example. The FetchXML statement of the system diagram 'Companies by industry':

<fetch aggregate="true">
      <entity name="account">
            <attribute groupby="true" alias="groupby_column" name="industrycode" />
            <attribute alias="aggregate_column" name="name" aggregate="count" />
      </entity>
</fetch>

You can copy the above FetchXML into a FetchXmlDataSet as it is and receive the XML output as:

<AccountsByIndustrys>
    <AccountsByIndustry>
        <aggregate_column formattedvalue="16">16</aggregate_column> 
        <groupby_column name=" Chemische Industrie" formattedvalue="73">73</groupby_column> 
        <groupby_column_industrycodename> Chemische Industrie</groupby_column_industrycodename> 
    </AccountsByIndustry>
    <AccountsByIndustry>
        <aggregate_column formattedvalue="84">84</aggregate_column> 
        <groupby_column name="IT-Industrie" formattedvalue="74">74</groupby_column> 
        <groupby_column_industrycodename>IT-Industrie</groupby_column_industrycodename> 
    </AccountsByIndustry>
    …
</AccountsByIndustrys>

That is to say, the CRM system lists 16 companies in the chemical industry, 84 in the IT industry, etc. Please note that all other columns that are not aggregated must contain the groupby clause. The alias attribute is required for aggregations because it is necessary to generate the node name of the XML output.

Aggregation Types

The following aggregations can be specified in the aggregate attribute:

  • count: Counts the number of records based on the specified field
  • sum: Counts the sum of all field values ​​of the specified field (for numbers and currency fields)

Example

<i18n:DataSet Entity="account" ItemName="Account" MultipleItems="true" Limit="10">
   <i18n:Columns>
        <i18n:Column>name</i18n:Column>
        <i18n:Column>accountnumber</i18n:Column>
        <i18n:Column>numberofemployees</i18n:Column>
   </i18n:Columns>
   <i18n:LinkedFetchXmlDataSet ItemName="Contact" MultipleItems="true" FromAttribute="accountid">
        <i18n:FetchXml>
          <fetch aggregate="true">
            <entity name="contact">
              <attribute name="contactid" alias="AnzahlKontakte" aggregate="count"/>
              <attribute name="gendercode" alias="Geschlecht" groupby="true"/>
              <filter type="and">
                <condition attribute="parentcustomerid" operator="eq" value="@parentid" />
              </filter>
            </entity>
          </fetch>
        </i18n:FetchXml>
   </i18n:LinkedFetchXmlDataSet>
   <i18n:OrderBy>
        <i18n:Column Descending="true">numberofemployees</i18n:Column>
   </i18n:OrderBy>
</i18n:DataSet>

The XML output lists the 10 companies with the most employees and the number of contacts by gender:

<Accounts>
    <Account>
        <name>Bessel Flamengos GbR</name> 
        <accountnumber>DE29765</accountnumber> 
        <numberofemployees formattedvalue="230">230</numberofemployees> 
        <accountid>{E2F44AC7-12D7-E111-A5A7-00155D0F1612}</accountid> 
        <Contacts>
            <Contact>
                < AnzahlKontakte formattedvalue="29">29</ AnzahlKontakte> 
                <Geschlecht name="Männlich" formattedvalue="1">1</Geschlecht> 
                <Geschlecht_gendercodename>Männlich</Geschlecht_gendercodename> 
            </Contact>
            <Contact>
                < AnzahlKontakte formattedvalue="42">42</ AnzahlKontakte> 
                <Geschlecht name="Weiblich" formattedvalue="2">2</Geschlecht> 
                <Geschlecht_gendercodename>Weiblich</Geschlecht_gendercodename> 
            </Contact>
        </Contacts>
    </Account>
    …
</Accounts>

Additional DataSets

The following DataSets are available:

  • CallingUserDataSet: A DataSet which contains all data of the calling user.
  • DefaultDataSet: Corresponds to the attributes of the entity and the data for which a document is created, the "primary record". For compatibility reasons, its content is displayed directly under the root node.
  • RenderingContextDataSet: A DataSet containing values of the RenderingContext(For example: system-wide variables)