Saturday, 16 April 2016

Group By Year - FetchXML Query

Group By for dates uses the dayweekmonthquarter or year value.
The following FetchXML query shows how to use the aggregate attribute and the groupby attribute to group the results by year.
  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='year' alias='year' />
  7. <filter type='and'>
  8. <condition attribute='statecode' operator='eq' value='Won' />
  9. </filter>
  10. </entity>
  11. </fetch>
Explanation
To create group by Year 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 year.
    <attribute name='actualclosedate' groupby='true' dategrouping='year' alias='year' />
- See more at: http://msxrmtools.com/fetchxml/reference

No comments:

Post a Comment