The following FetchXML shows how to use the aggregate attribute and multiple groupby clauses.
<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' />
<attribute name='actualclosedate' groupby='true' dategrouping='year' alias='year' />
<filter type='and'>
<condition attribute='statecode' operator='eq' value='Won' />
</filter>
</entity>
</fetch>
Explanation
To create multiple group by FetchXML query:
- Set aggregate to true in fetch node.
<fetch distinct='false' mapping='logical' aggregate='true'>
- Add required aggregate attributes in entity clause.
<attribute name='name' alias='opportunity_count' aggregate='count' />
- Add multiple group by attributes by setting keyword groupby to true.
<attribute name='actualclosedate' groupby='true' dategrouping='quarter' alias='quarter' />
<attribute name='actualclosedate' groupby='true' dategrouping='year' alias='year' />
- See more at: http://msxrmtools.com/fetchxml/reference
No comments:
Post a Comment