Group By Quarter - FetchXML Query

The following example shows how to use the aggregate attribute and the groupby attribute to group the results by quarter.

<fetch distinct='false' mapping='logical' aggregate='true'>
   <entity name='opportunity'>
      <attribute name='opportunityid' alias='opportunity_count' aggregate='count'/>
      <attribute name='estimatedvalue' alias='estimatedvalue_sum' aggregate='sum'/>
      <attribute name='estimatedvalue' alias='estimatedvalue_avg' aggregate='avg'/>
      <attribute name='actualclosedate' groupby='true' dategrouping='quarter' alias='quarter' />
      <filter type='and'>
         <condition attribute='statecode' operator='eq' value='Won' />
      </filter>
   </entity>
</fetch>

Explanation

To create group by Quarter FetchXML query:

• Set aggregate to true in fetch node.

  <fetch distinct='false' mapping='logical' aggregate='true'>
  

• Add required aggregate attribute in entity clause.

  <attribute name='name' alias='opportunity_count' aggregate='countcolumn' />
  

• Add group by attribute set keyword groupby to true and dategrouping to quarter.

  <attribute name='actualclosedate' groupby='true' dategrouping='quarter' alias='quarter' />

- See more at: http://msxrmtools.com/fetchxml/reference