Saturday, 16 April 2016

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.
  1. <fetch distinct='false' mapping='logical' aggregate='true'>
  2. <entity name='opportunity'>
  3. <attribute name='opportunityid' alias='opportunity_count' aggregate='count'/>
  4. <attribute name='estimatedvalue' alias='estimatedvalue_sum' aggregate='sum'/>
  5. <attribute name='estimatedvalue' alias='estimatedvalue_avg' aggregate='avg'/>
  6. <attribute name='actualclosedate' groupby='true' dategrouping='quarter' alias='quarter' />
  7. <filter type='and'>
  8. <condition attribute='statecode' operator='eq' value='Won' />
  9. </filter>
  10. </entity>
  11. </fetch>
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:

No comments:

Post a Comment