Saturday, 16 April 2016

Average Aggregates - FetchXML Query

Following FetchXML calculates average of estimated value from opportunity.
  1. <fetch distinct='false' mapping='logical' aggregate='true'>
  2. <entity name='opportunity'>
  3. <attribute name='estimatedvalue' alias='estimatedvalue_avg' aggregate='avg' />
  4. </entity>
  5. </fetch>
Explanation
To calculate average set following:
  • Set aggregate to true in <fetch> clause.
    <fetch distinct='false' mapping='logical' aggregate='true'> 
  • In <aggregate> attribute set aggregate to avg
    <attribute name='estimatedvalue' alias='estimatedvalue_avg' aggregate='avg' /> 
Limitation with null values while computing average
Null values are not considered when Microsoft Dynamics CRM computes the average of data. However, zero (0) is used. 
In the following example, with the following data, the average for Account 1 (two entries) is shown as 250 whereas the average for Account 2 (two entries) is shown as 125.
TopicPotential CustomerEstimated value
Opportunity 1Account 1null
Opportunity 2Account 1250
Opportunity 3Account 20
Opportunity 4Account 2250
- See more at: http://msxrmtools.com/fetchxml/reference

No comments:

Post a Comment