In Microsoft Dynamics CRM 2011 FetchXML includes grouping and aggregation features which let us to use aggregate functions like sum, count etc.
Using Group by we can calculate sum, avg, min, max, count. But, Group by clause is not supported through LINQ in CRM.
You can only specify one aggregate attribute in a query and you cannot use the distinct keyword. To create an aggregate attribute, set the keyword aggregate to true, then specify valid entity name, attribute name and alias(variable name). You must also specify the type of aggregation you want to perform.
<fetch distinct=’false’ mapping=’logical’ aggregate=’true’>
<entity name=’entity name’>
<attribute name=’attribute name’ aggregate=’count’ alias=’alias name’/>
</entity>
</fetch>”
Below is the example to get sum of total amount of all won quotes:
string quotes = @”
<fetch distinct=’false’ mapping=’logical’ aggregate=’true’>
<entity name=’quote’>
<attribute name=’totalamount’ alias=’totalamount_sum’ aggregate=’sum’/>
<attribute name=’statecode’ groupby=’true’ alias=’state’ />
<filter type=’and’>
<condition attribute=’ statecode ‘ operator=’eq’ value=’won’ />”+
“</filter> “+
“</entity> “+
“</fetch>”;
EntityCollection quotes_result = _service.RetrieveMultiple(new FetchExpression(quotes));
foreach (var q in quotes_result.Entities)
{
Decimal wonQuoteAmount = ((Money)((AliasedValue)q[“totalamount_sum”]).Value).Value;
}
Free 70% of storage space in CRM with Attachment Management Apps!
Attach2Dynamics – Store and manage documents/attachments in cloud storage of your choice – SharePoint, Dropbox or Azure Blob Storage from within Dynamics 365 CRM.
SharePoint Security Sync – Robust and secure solution to integrate Dynamics 365 CRM and SharePoint Security Sync thereby ensuring secure access to confidential documents stored in SharePoint.