CRM SDK Aggregate Fetch xml - International Days

International Days

Internationally important Days

Breaking

Home Top Ad

Post Top Ad

Thursday, 8 June 2017

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>


2 comments:

Post Bottom Ad

Pages