I had written a post long back showcasing the aggregate queries on Dynamics CRM on the below link:
This is a very performance effective and seldom used piece from most CRM girls and guys out there.
I had got lot of feedback and comments but somehow people were not able to use it in actual code.
So, I had committed to doing a follow-up article with actual reusable code asset. It took a while for
me to come back to this one, but I never break my promises.
So here it goes(The aliased value is Money in my example, it can be decimal and integer as well):
public static decimal GetSum(string primaryEntityId, IOrganizationService service)
{
string fetchXml = @”<fetch mapping=’logical’ aggregate=’true’ version=’1.0′>” +
“<entity name=’childentityname’>” +
“<attribute name = ‘attributetoadd’ alias=’sum_attributetoadd’ aggregate=’sum’ />” +
“<filter>” +
“<condition attribute = ‘primaryEntityIdfieldname’ operator=’eq’ value='” +
primaryEntityId
+ “‘ />” +
“</filter></entity></fetch>”;
FetchExpression fetch = new FetchExpression(fetchXml);
EntityCollection result = service.RetrieveMultiple(fetch);
decimal sum = 0;
if (result.Entities.Count == 1)
{
sum = ((Money)((AliasedValue)(result.Entities[0].Attributes[“sum_attributetosum”])).Value).Value;
}
return sum;
}
Hope it helps you and Happy CRMing!
How to avoid the currency exchange rate applying on ‘sum’
you can retrieve currency exchange rate by the following request:
RetrieveExchangeRateRequest request = new RetrieveExchangeRateRequest()
{
TransactionCurrencyId = _currency.Id
};
RetrieveExchangeRateResponse response =
(RetrieveExchangeRateResponse)_serviceProxy.Execute(request);
Next you can convert the sum amount to the format you like.
Thanks and Regards,
Deepesh
[…] https://dynamicsofdynamicscrm.com/2015/10/01/code-tipaggregate-fetchxml-queries-code-for-dynamics-cr… […]