CRM SDK Aggregate Fetch xml

We can use the following methods to retrieve data , whil integratingCRM with the help of CRM SDK.

The querying methods are

1. using QueryExpression
2. using Fetch XML

QueryExpression :

We use this type of querying method when we using direct selection . For example, if we need to get all data from account entity, then we use simple QueryExpression as below

QueryExpression query = new QueryExpression

{

EntityName = "account",

ColumnSet = new ColumnSet(true)

};

EntityCollection entities = service.RetrieveMultiple(query);

This is pretty direct method , also you can link different entities using LinkEntity() class

Syntax:

LinkEntity(string linkFromEntityName, string linkToEntityName, string linkFromAttributeName, string linkToAttributeName, JoinOperator joinOperator)

Example :

QueryExpression query = new QueryExpression

{

EntityName = "account",

ColumnSet = new ColumnSet(true)

};

var accountLinkEntity = new LinkEntity("account", "systemuser", "ownerid", "systemuserid", JoinOperator.Inner);

accountLinkEntity.Columns.AddColumns("firstname");

query.LinkEntities.Add(accountLinkEntity);

EntityCollection entities = service.RetrieveMultiple(query);

Fetch XML

The Link entity and query expression is suitable only in direct join operations .

But in case of Aggregate result , those query expression is very difficult to implement.

 Note : The aggregate function SUM,AVG,MIN and MAX do not work for date field in CRM 2011 . From CRM 2013 onward MIN and MAX will works for Date field.

Let us look into an example ,

Suppose we need to get the sum of all sale amount for different products  in sales history entity. Then, we need product wise aggregate . This is impossible using query expression. We have an alternative for this , that is fetch xml.

Case : 1

string groupby = @"

<fetch distinct='false' mapping='logical' aggregate='true' >

<entity name='[Entity_Name]'>

<attribute name='[Aggregate_Attribute_Name]' alias='[alias_Name]' aggregate='sum' />

<attribute name='[Groupby_Attribute_Name]' alias='[alias_Name]' groupby='true' />

</entity>

</fetch> ";

EntityCollection groupby_result = service.RetrieveMultiple(new FetchExpression(groupby));

In the Above example,

• Entity_Name -> Name of the entity
• Aggregate_Attribute_name -> Its is the name of the attribute which's aggregate to be calculated , also we need to specify the type of operation aggregate='sum'.
• Groupby_Attribute_Name -> Name of the attribute whose sum grouped based on this attribute groupby='true'.

Case : 2

If we need to add a filtration in that fetch XML . Add filter Tag in that fetch XML.Please check the below XML

<fetch distinct='false' mapping='logical' aggregate='true' >

<entity name='[Entity_Name]'>

<attribute name='[Aggregate_Attribute_Name]' alias='[alias_Name]' aggregate='sum' />

<attribute name='[Groupby_Attribute_Name]' alias='[alias_Name]' groupby='true' />

<filter type='and'>

<condition attribute='[Filter_Attribute_Name]' operator='eq' value='[Filter_Value]' />

</filter>

</entity>

</fetch>

Case : 3

If we need to group based on an attribute which is in another entity and it is linked to our aggregate Entity. Add link entity tag .Please check the below XML

<fetch distinct='false' mapping='logical' aggregate='true' >

<entity name='[Entity_Name]'>

<attribute name='[Aggregate_Attribute_Name]' alias='[alias_Name]' aggregate='sum' />

<link-entity name='[LinkEnity_Name]' from='[Mapping_LinkEnity_Attribute]' to='[Mapping_Enity_Attribute]'>

<attribute name='[Groupby_Attribute_Name]' alias='Country' groupby='true' />

</link-entity>

<filter type='and'>

<condition attribute='[Filter_Attribute_Name]' operator='eq' value='[Filter_Value]' />

</filter>

</entity>

</fetch>

Case : 4

If we need to filter the data with in a particular date rage (fiscal period) .Please check the below XML

<fetch distinct='false' mapping='logical' aggregate='true' >

<entity name='[Entity_Name]'>

<attribute name='[Aggregate_Attribute_Name]' alias='[alias_Name]' aggregate='sum' />

<link-entity name='[LinkEnity_Name]' from='[Mapping_LinkEnity_Attribute]' to='[Mapping_Enity_Attribute]'>

<attribute name='[Groupby_Attribute_Name]' alias='Country' groupby='true' />

</link-entity>

<filter type='and'>

<condition attribute='[Filter_Attribute_Name]' operator='eq' value='[Filter_Value]' />

<condition attribute = '[Date_Attiribute_Name]' operator='between'>

<value>" + FromDate + @"</value>

<value>" + Todate + @"</value>

</condition>

</filter>

</entity>

</fetch>

Case : 5

If we need to filter the data with in a certain data (like 'in' condition in SQL operation ) .Please check the below XML

<entity name='[Entity_Name]'>

<attribute name='[Aggregate_Attribute_Name]' alias='[alias_Name]' aggregate='sum' />

<link-entity name='[LinkEnity_Name]' from='[Mapping_LinkEnity_Attribute]' to='[Mapping_Enity_Attribute]'>

<attribute name='[Groupby_Attribute_Name]' alias='Country' groupby='true' />

</link-entity>

<filter type='and'>

<condition attribute='[Filter_Attribute_Name]' operator='eq' value='[Filter_Value]' />

<condition attribute = '[Filter_Attiribute_Name]' operator='in'>

<value>A</value>

<value>B</value>

.

.

.

</condition>

</filter>

</entity>

</fetch>

Case : 6

If we need to Group-by using year field in a date attribute .Please check the below XML

<fetch distinct='false' mapping='logical' aggregate='true' >

<entity name='[Entity_Name]'>

<attribute name='[Aggregate_Attribute_Name]' alias='[alias_Name]' aggregate='sum' />

<attribute name='[Date_Attribute_Name]' alias='Year' groupby='true' dategrouping='year' />

<filter type='and'>

<condition attribute='[Filter_Attribute_Name]' operator='eq' value='[Filter_Value]' />

</filter>

</entity>

</fetch>

Case : 7

If we need to Add Multiple Group-by .Please check the below XML

<fetch distinct='false' mapping='logical' aggregate='true' >

<entity name='[Entity_Name]'>

<attribute name='[Aggregate_Attribute_Name]' alias='[alias_Name]' aggregate='sum' />

<attribute name='[Attribute_Name1]' alias='Name1' groupby='true' dategrouping='year' />

<attribute name='[Attribute_Name2]' alias='Name2' groupby='true' />

<filter type='and'>

<condition attribute='[Filter_Attribute_Name]' operator='eq' value='[Filter_Value]' />

</filter>

</entity>

</fetch>

If we want to group by month then use dategrouping='month'
If we want to filter two years value then, use operator ='between'  because 'in-fiscal-year'  will take only one year values.

Case : 8

If we need to average value .Please check the below XML

<fetch distinct='false' mapping='logical' aggregate='true'>

<entity name='[Entity_Name]'>

<attribute name='[Avrage_Attribute_Name]' alias='[alias_Name]' aggregate='avg' />

</entity>

</fetch>

Case : 9 

If we need to get Count of Specific field .Please check the below XML

<fetch distinct='false' mapping='logical' aggregate='true'> 
    <entity name='[Entity_Name]'> 
       <attribute name='[Count_Attribute_Name]' alias='[alias_Name]' aggregate='count'/> 
    </entity> 

</fetch>