FetchXML Tutotorial

Group by year in fetchxml.

In this article we will learn How to use Group by year in fetchxml?

In this example shows how to use Group by year in fetchxml?

FetchXML includes grouping and aggregation features that you can use this function calculate sum, average min, max and count.

Information About aggregation
For create an aggregate attribute, set the keyword aggregate to true, then specify entity name, attribute name and mandatory to alias(variable name). You must also specify the type of aggregation you want to perform.

Group by year in fetchxml

In this example you can see how to use the aggregate attribute and the groupby attribute to group the results by year.

Use this Query to Fetch aggregate information about the opportunities that have been Win by year. and alias name is opportunity_count,estimatedvalue_sum, estimatedvalue_avg and year.
The main purpose for used here EntityCollection and RetrieveMultiple.

EntityCollection : EntityCollection is used for Contains a collection of entity instances.
RetrieveMultiple :RetrieveMultiple is used for Retrieves a collection of records.

string by_year = @"
<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='year' alias='year' />
<filter type='and'>
<condition attribute='statecode' operator='eq' value='Win' />
</filter>
</entity>
</fetch>";
EntityCollection byyear_data = _serviceProxy.RetrieveMultiple(new FetchExpression(by_year));
foreach (var item in byyear_data.Entities)
{
Int32 aggregate11 = (Int32)((AliasedValue)item["year"]).Value;
System.Console.WriteLine("Year: " + aggregate11);
Int32 aggregate11a = (Int32)((AliasedValue)item["opportunity_count"]).Value;
System.Console.WriteLine("Count of all opportunities: " + aggregate11a);
decimal aggregate11b = ((Money)((AliasedValue)item["estimatedvalue_sum"]).Value).Value;
System.Console.WriteLine("Sum of estimated value of all opportunities: " + aggregate11b);
decimal aggregate11c = ((Money)((AliasedValue)item["estimatedvalue_avg"]).Value).Value;
System.Console.WriteLine("Average of estimated value of all opportunities: " + aggregate11c);
System.Console.WriteLine("----------------------------------------------");
}
System.Console.WriteLine("===============================");

Leave a Reply

Your email address will not be published. Required fields are marked *