Utiliser FetchXML l’agrégation

Article
08/24/2023

Dans Microsoft Dataverse, FetchXML contient des fonctionnalités de groupement et d’agrégation vous permettant de calculer une somme, une moyenne, un nombre minimal et maximal.

Voici les fonctions d’agrégation prises en charge :

somme
moy
min
max
nombre(*)
nombre(nom d’attribut)

À propos de l’agrégation

Pour renvoyer des valeurs agrégées, définissez l’attribut aggregate de l’élément fetch sur true. Ensuite, dans l’élément entity, définissez un ou plusieurs éléments attribute avec les attributs name, aggregate et alias. Utilisez l’attribut aggregate pour indiquer le type d’agrégation souhaitée.

L’exemple suivant illustre une simple requête d’agrégat dans FetchXML.

<fetch distinct='false' mapping='logical' aggregate='true'>   
   <entity name='entity name'>   
      <attribute name='attribute name' aggregate='count' alias='alias name'/>   
   </entity>   
</fetch>

Le résultat d’une requête avec une colonne agrégée est différente du résultat d’une requête standard. La valeur d’attribut alias est utilisée comme identificateur pour le résultat agrégé.

Cet exemple montre le format du résultat d’une requête d’agrégation.

<resultset morerecords="0"'>   
   <result>  
      <alias>aggregate value</alias>  
   </result>  
</resultset>

L’exemple suivant affiche les résultats d’une requête lorsque la variable d’alias a la valeur account_count.

<resultset morerecords="0"'>   
   <result>  
      <account_count>20</account_count>  
   </result>  
</resultset>

Limitations

Les requêtes qui renvoient des valeurs agrégées sont limitées à 50 000 enregistrements. Cette limite aide à gérer les performances et la fiabilité du système. Si les critères de filtre de votre requête contient plus de 50 000 enregistrements, le message d’erreur suivant s’affiche :

Code d’erreur : -2147164125
Code d’erreur hexadécimal : 8004E023
Message d’erreur de plateforme : AggregateQueryRecordLimit exceeded. Cannot perform this operation.
Message d’erreur client : La limite d’enregistrement maximale est dépassée. Réduisez le nombre d’enregistrements.

Pour éviter cette erreur, ajoutez des filtres appropriés à votre requête afin d’éviter d’évaluer plus de 50 000 enregistrements. Ensuite, exécutez votre requête plusieurs fois et combinez les résultats.

Conseil

Si vous souhaitez obtenir un nombre total d’enregistrements sans filtre, utilisez le message RetrieveTotalRecordCount avec soit la fonction RetrieveTotalRecordCount de l’API web, soit avec la classe dRetrieveTotalRecordCountRequest du kit de développement logiciel (SDK) pour .NET. Les données récupérées proviendront d’un instantané au cours des dernières 24 heures.

Avg

L’exemple suivant montre comment utiliser l’attribut aggregate pour utiliser la fonction d’agrégation avg.

// Fetch the average of estimatedvalue for all opportunities.  This is the equivalent of 
// SELECT AVG(estimatedvalue) AS estimatedvalue_avg ... in SQL.
string estimatedvalue_avg = @" 
<fetch distinct='false' mapping='logical' aggregate='true'> 
   <entity name='opportunity'> 
      <attribute name='estimatedvalue' alias='estimatedvalue_avg' aggregate='avg' /> 
   </entity> 
</fetch>";

EntityCollection estimatedvalue_avg_result = _serviceProxy.RetrieveMultiple(new FetchExpression(estimatedvalue_avg));

foreach (var c in estimatedvalue_avg_result.Entities)
{
   decimal aggregate1 = ((Money)((AliasedValue)c["estimatedvalue_avg"]).Value).Value;
   System.Console.WriteLine("Average estimated value: " + aggregate1);

}

Limite avec des valeurs Null en calculant la moyenne

Les valeurs Null ne sont pas prises en compte lorsque Dataverse calcule la moyenne des données. Toutefois, zéro (0) est utilisé.

Dans l’exemple suivant, avec les données suivantes, la moyenne pour le Compte 1 (deux entrées) apparaît comme étant 250, tandis que la moyenne pour le Compte 2 (deux entrées) apparaît comme étant 125.

Opportunité	Prospect	Valeur estimée
Opportunité 1	Compte 1	null
Opportunité 2	Compte 1	250
Opportunité 3	Compte 2	0
Opportunité 4	Compte 2	250

Nombre

L’exemple suivant montre comment utiliser l’attribut aggregate pour utiliser la fonction d’agrégation count.

// *****************************************************************************************************************
//                FetchXML      opportunity_count   Aggregate 2
// *****************************************************************************************************************
// Fetch the count of all opportunities.  This is the equivalent of
// SELECT COUNT(*) AS opportunity_count ... in SQL.
string opportunity_count = @" 
<fetch distinct='false' mapping='logical' aggregate='true'> 
   <entity name='opportunity'> 
      <attribute name='name' alias='opportunity_count' aggregate='count'/> 
   </entity> 
</fetch>";

EntityCollection opportunity_count_result = _serviceProxy.RetrieveMultiple(new FetchExpression(opportunity_count));

foreach (var c in opportunity_count_result.Entities)
{
   Int32 aggregate2 = (Int32)((AliasedValue)c["opportunity_count"]).Value;
   System.Console.WriteLine("Count of all opportunities: " + aggregate2); 

}

CountColumn

L’exemple suivant montre comment utiliser l’attribut aggregate pour utiliser la fonction d’agrégation countcolumn.

// *****************************************************************************************************************
//                FetchXML      opportunity_colcount   Aggregate 3
// *****************************************************************************************************************
// Fetch the count of all opportunities.  This is the equivalent of 
// SELECT COUNT(name) AS opportunity_count ... in SQL.
string opportunity_colcount = @" 
<fetch distinct='false' mapping='logical' aggregate='true'> 
   <entity name='opportunity'> 
      <attribute name='name' alias='opportunity_colcount' aggregate='countcolumn'/> 
   </entity> 
</fetch>";

EntityCollection opportunity_colcount_result = _serviceProxy.RetrieveMultiple(new FetchExpression(opportunity_colcount));

foreach (var c in opportunity_colcount_result.Entities)
{
   Int32 aggregate3 = (Int32)((AliasedValue)c["opportunity_colcount"]).Value;
   System.Console.WriteLine("Column count of all opportunities: " + aggregate3);

}

Compter les colonnes distinctes

L’exemple suivant montre comment utiliser l’attribut aggregate avec la fonction d’agrégation countcolumn et l’attribut distinct pour compter les colonnes distinctes.

// *****************************************************************************************************************
//                FetchXML      opportunity_distcount   Aggregate 4
// *****************************************************************************************************************
// Fetch the count of distinct names for opportunities.  This is the equivalent of 
// SELECT COUNT(DISTINCT name) AS opportunity_count ... in SQL.
string opportunity_distcount = @" 
<fetch distinct='false' mapping='logical' aggregate='true'> 
   <entity name='opportunity'> 
      <attribute name='name' alias='opportunity_distcount' aggregate='countcolumn' distinct='true'/> 
   </entity> 
</fetch>";

EntityCollection opportunity_distcount_result = _serviceProxy.RetrieveMultiple(new FetchExpression(opportunity_distcount));

foreach (var c in opportunity_distcount_result.Entities)
{
   Int32 aggregate4 = (Int32)((AliasedValue)c["opportunity_distcount"]).Value;
   System.Console.WriteLine("Distinct name count of all opportunities: " + aggregate4);

}

Max

Les valeurs Null ne sont pas prises en compte lorsque Dataverse calcule le maximum de données. Toutefois, zéro (0) est utilisé.

L’exemple suivant montre comment utiliser l’attribut aggregate pour utiliser la fonction d’agrégation max.

// *****************************************************************************************************************
//                FetchXML      estimatedvalue_max   Aggregate 5
// *****************************************************************************************************************
// Fetch the maximum estimatedvalue of all opportunities.  This is the equivalent of 
// SELECT MAX(estimatedvalue) AS estimatedvalue_max ... in SQL.
string estimatedvalue_max = @" 
<fetch distinct='false' mapping='logical' aggregate='true'> 
   <entity name='opportunity'> 
       <attribute name='estimatedvalue' alias='estimatedvalue_max' aggregate='max' /> 
   </entity> 
</fetch>";

EntityCollection estimatedvalue_max_result = service.RetrieveMultiple(new FetchExpression(estimatedvalue_max));

foreach (var c in estimatedvalue_max_result.Entities)
{
   decimal aggregate5 = ((Money)((AliasedValue)c["estimatedvalue_max"]).Value).Value;
   Console.WriteLine("Max estimated value of all opportunities: " + aggregate5);

}

Min

Les valeurs Null ne sont pas prises en compte lorsque Dataverse calcule le minimum de données. Toutefois, zéro (0) est utilisé.

L’exemple suivant montre comment utiliser l’attribut aggregate pour utiliser la fonction d’agrégation min.

// *****************************************************************************************************************
//                FetchXML      estimatedvalue_min   Aggregate 6
// *****************************************************************************************************************
// Fetch the minimum estimatedvalue of all opportunities.  This is the equivalent of 
// SELECT MIN(estimatedvalue) AS estimatedvalue_min ... in SQL.
string estimatedvalue_min = @" 
<fetch distinct='false' mapping='logical' aggregate='true'> 
   <entity name='opportunity'> 
      <attribute name='estimatedvalue' alias='estimatedvalue_min' aggregate='min' /> 
   </entity> 
</fetch>";

EntityCollection estimatedvalue_min_result = _serviceProxy.RetrieveMultiple(new FetchExpression(estimatedvalue_min));

foreach (var c in estimatedvalue_min_result.Entities)
{
   decimal aggregate6 = ((Money)((AliasedValue)c["estimatedvalue_min"]).Value).Value;
   System.Console.WriteLine("Minimum estimated value of all opportunities: " + aggregate6);

}

Sum

L’exemple suivant montre comment utiliser l’attribut aggregate pour utiliser la fonction d’agrégation sum.

// *****************************************************************************************************************
//                FetchXML      estimatedvalue_sum   Aggregate 7
// *****************************************************************************************************************
// Fetch the sum of estimatedvalue for all opportunities.  This is the equivalent of 
// SELECT SUM(estimatedvalue) AS estimatedvalue_sum ... in SQL.
string estimatedvalue_sum = @" 
<fetch distinct='false' mapping='logical' aggregate='true'> 
   <entity name='opportunity'> 
      <attribute name='estimatedvalue' alias='estimatedvalue_sum' aggregate='sum' /> 
   </entity> 
</fetch>";

EntityCollection estimatedvalue_sum_result = _serviceProxy.RetrieveMultiple(new FetchExpression(estimatedvalue_sum));

foreach (var c in estimatedvalue_sum_result.Entities)
{
   decimal aggregate7 = ((Money)((AliasedValue)c["estimatedvalue_sum"]).Value).Value;
   System.Console.WriteLine("Sum of estimated value of all opportunities: " + aggregate7);

}

Plusieurs agrégats

L’exemple suivant montre comment utiliser plusieurs attributs aggregate pour utiliser les fonctions d’agrégation count, sum et avg dans une seule requête.

// *****************************************************************************************************************
//                FetchXML      estimatedvalue_avg, estimatedvalue_sum   Aggregate 8
// *****************************************************************************************************************
// Fetch multiple aggregate values within a single query.
string estimatedvalue_avg2 = @" 
<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'/> 
   </entity> 
</fetch>";

EntityCollection estimatedvalue_avg2_result = _serviceProxy.RetrieveMultiple(new FetchExpression(estimatedvalue_avg2));

foreach (var c in estimatedvalue_avg2_result.Entities)
{
   Int32 aggregate8a = (Int32)((AliasedValue)c["opportunity_count"]).Value;
   System.Console.WriteLine("Count of all opportunities: " + aggregate8a);
   decimal aggregate8b = ((Money)((AliasedValue)c["estimatedvalue_sum"]).Value).Value;
   System.Console.WriteLine("Sum of estimated value of all opportunities: " + aggregate8b);
   decimal aggregate8c = ((Money)((AliasedValue)c["estimatedvalue_avg"]).Value).Value;
   System.Console.WriteLine("Average of estimated value of all opportunities: " + aggregate8c);

}

Regrouper par

Cet exemple explique comment utiliser plusieurs attributs aggregate et un attribut groupby lié.

// *****************************************************************************************************************
//                FetchXML      groupby1   Aggregate 9
// *****************************************************************************************************************
// Fetch a list of users with a count of all the opportunities they own using groupby.
string groupby1 = @" 
<fetch distinct='false' mapping='logical' aggregate='true'> 
   <entity name='opportunity'> 
      <attribute name='name' alias='opportunity_count' aggregate='countcolumn' /> 
      <attribute name='ownerid' alias='ownerid' groupby='true' /> 
   </entity> 
</fetch>";

EntityCollection groupby1_result = _serviceProxy.RetrieveMultiple(new FetchExpression(groupby1));

foreach (var c in groupby1_result.Entities)
{
   Int32 aggregate9a = (Int32)((AliasedValue)c["opportunity_count"]).Value;
   System.Console.WriteLine("Count of all opportunities: " + aggregate9a + "\n");
   string aggregate9b = ((EntityReference)((AliasedValue)c["ownerid"]).Value).Name;
   System.Console.WriteLine("Owner: " + aggregate9b);
   string aggregate9c = (string)((AliasedValue)c["ownerid_owneridyominame"]).Value;
   System.Console.WriteLine("Owner: " + aggregate9c);
   string aggregate9d = (string)((AliasedValue)c["ownerid_owneridyominame"]).Value;
   System.Console.WriteLine("Owner: " + aggregate9d);
}

Les exemples ci-dessous montrent le groupe suivant :

Regrouper par entités liées

L’exemple suivant montre comment utiliser les attributs aggregate pour appliquer la fonction d’agrégation sum aux valeurs des tables liées.

// *****************************************************************************************************************
//                FetchXML      groupby2   Aggregate 10
// *****************************************************************************************************************
// Fetch the number of opportunities each manager's direct reports 
// own using a groupby within a link-entity.
string groupby2 = @" 
<fetch distinct='false' mapping='logical' aggregate='true'> 
   <entity name='opportunity'> 
      <attribute name='name' alias='opportunity_count' aggregate='countcolumn' /> 
      <link-entity name='systemuser' from='systemuserid' to='ownerid'>
          <attribute name='parentsystemuserid' alias='managerid' groupby='true' />
      </link-entity> 
   </entity> 
</fetch>";

EntityCollection groupby2_result = _serviceProxy.RetrieveMultiple(new FetchExpression(groupby2));

foreach (var c in groupby2_result.Entities)
{

     int? aggregate10a = (int?)((AliasedValue)c["opportunity_count"]).Value;
     System.Console.WriteLine("Count of all opportunities: " + aggregate10a + "\n");
}

Regrouper par année

Effectuez un regroupement par dates utilisant la valeur jour, semaine, mois, trimestre ou année. Cet exemple explique comment utiliser l’attribut aggregate et l’attribut groupby pour regrouper les résultats par année.


// *****************************************************************************************************************
//                FetchXML      byyear   Aggregate 11           
// *****************************************************************************************************************
// Fetch aggregate information about the opportunities that have 
// been won by year.
string byyear = @" 
<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='Won' />
      </filter>
   </entity> 
</fetch>";

EntityCollection byyear_result = _serviceProxy.RetrieveMultiple(new FetchExpression(byyear));

foreach (var c in byyear_result.Entities)
{
   Int32 aggregate11 = (Int32)((AliasedValue)c["year"]).Value;
   System.Console.WriteLine("Year: " + aggregate11);                      
   Int32 aggregate11a = (Int32)((AliasedValue)c["opportunity_count"]).Value;
   System.Console.WriteLine("Count of all opportunities: " + aggregate11a);
   decimal aggregate11b = ((Money)((AliasedValue)c["estimatedvalue_sum"]).Value).Value;
   System.Console.WriteLine("Sum of estimated value of all opportunities: " + aggregate11b);
   decimal aggregate11c = ((Money)((AliasedValue)c["estimatedvalue_avg"]).Value).Value;
   System.Console.WriteLine("Average of estimated value of all opportunities: " + aggregate11c);
   System.Console.WriteLine("----------------------------------------------");
}

Regrouper par trimestre

Cet exemple explique comment utiliser l’attribut aggregate et l’attribut groupby pour regrouper les résultats par trimestre.

// *****************************************************************************************************************
//                FetchXML      byquarter   Aggregate 12           
// *****************************************************************************************************************
// Fetch aggregate information about the opportunities that have 
// been won by quarter.(returns 1-4)
string byquarter = @" 
<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='quarter' alias='quarter' />
       <filter type='and'>
           <condition attribute='statecode' operator='eq' value='Won' />
       </filter>
    </entity> 
</fetch>";

EntityCollection byquarter_result = _serviceProxy.RetrieveMultiple(new FetchExpression(byquarter));

foreach (var c in byquarter_result.Entities)
{
    Int32 aggregate12 = (Int32)((AliasedValue)c["quarter"]).Value;
    System.Console.WriteLine("Quarter: " + aggregate12);
    Int32 aggregate12a = (Int32)((AliasedValue)c["opportunity_count"]).Value;
    System.Console.WriteLine("Count of all opportunities: " + aggregate12a);
    decimal aggregate12b = ((Money)((AliasedValue)c["estimatedvalue_sum"]).Value).Value;
    System.Console.WriteLine("Sum of estimated value of all opportunities: " + aggregate12b);
    decimal aggregate12c = ((Money)((AliasedValue)c["estimatedvalue_avg"]).Value).Value;
    System.Console.WriteLine("Average of estimated value of all opportunities: " + aggregate12c);
    System.Console.WriteLine("----------------------------------------------");
}

Regrouper par mois

Cet exemple explique comment utiliser l’attribut aggregate et l’attribut groupby pour regrouper les résultats par mois.

// *****************************************************************************************************************
//                FetchXML      bymonth   Aggregate 13           
// *****************************************************************************************************************
// Fetch aggregate information about the opportunities that have 
// been won by month. (returns 1-12)
string bymonth = @" 
<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='month' alias='month' />
       <filter type='and'>
           <condition attribute='statecode' operator='eq' value='Won' />
       </filter>
    </entity> 
</fetch>";

EntityCollection bymonth_result = _serviceProxy.RetrieveMultiple(new FetchExpression(bymonth));

foreach (var c in bymonth_result.Entities)
{
    Int32 aggregate13 = (Int32)((AliasedValue)c["month"]).Value;
    System.Console.WriteLine("Month: " + aggregate13);
    Int32 aggregate13a = (Int32)((AliasedValue)c["opportunity_count"]).Value;
    System.Console.WriteLine("Count of all opportunities: " + aggregate13a);
    decimal aggregate13b = ((Money)((AliasedValue)c["estimatedvalue_sum"]).Value).Value;
    System.Console.WriteLine("Sum of estimated value of all opportunities: " + aggregate13b);
    decimal aggregate13c = ((Money)((AliasedValue)c["estimatedvalue_avg"]).Value).Value;
    System.Console.WriteLine("Average of estimated value of all opportunities: " + aggregate13c);
    System.Console.WriteLine("----------------------------------------------");
}

Regrouper par semaine

Cet exemple explique comment utiliser l’attribut aggregate et l’attribut groupby pour regrouper les résultats par semaine.

// *****************************************************************************************************************
//                FetchXML      byweek   Aggregate 14           
// *****************************************************************************************************************
// Fetch aggregate information about the opportunities that have 
// been won by week. (Returns 1-52)
string byweek = @" 
<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='week' alias='week' />
      <filter type='and'>
          <condition attribute='statecode' operator='eq' value='Won' />
      </filter>
   </entity> 
</fetch>";

EntityCollection byweek_result = _serviceProxy.RetrieveMultiple(new FetchExpression(byweek));

foreach (var c in byweek_result.Entities)
{
   Int32 aggregate14 = (Int32)((AliasedValue)c["week"]).Value;
   System.Console.WriteLine("Week: " + aggregate14);
   Int32 aggregate14a = (Int32)((AliasedValue)c["opportunity_count"]).Value;
   System.Console.WriteLine("Count of all opportunities: " + aggregate14a);
   decimal aggregate14b = ((Money)((AliasedValue)c["estimatedvalue_sum"]).Value).Value;
   System.Console.WriteLine("Sum of estimated value of all opportunities: " + aggregate14b);
   decimal aggregate14c = ((Money)((AliasedValue)c["estimatedvalue_avg"]).Value).Value;
   System.Console.WriteLine("Average of estimated value of all opportunities: " + aggregate14c);
   System.Console.WriteLine("----------------------------------------------");
}

Regrouper par jour

Cet exemple explique comment utiliser l’attribut aggregate et l’attribut groupby pour regrouper les résultats par jour.

// *****************************************************************************************************************
//                FetchXML      byday   Aggregate 15           
// *****************************************************************************************************************
// Fetch aggregate information about the opportunities that have 
// been won by day. (Returns 1-31)
string byday = @" 
<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='day' alias='day' />
      <filter type='and'>
          <condition attribute='statecode' operator='eq' value='Won' />
      </filter>
   </entity> 
</fetch>";

EntityCollection byday_result = _serviceProxy.RetrieveMultiple(new FetchExpression(byday));

foreach (var c in byday_result.Entities)
{
   Int32 aggregate15 = (Int32)((AliasedValue)c["day"]).Value;
   System.Console.WriteLine("Day: " + aggregate15);
   Int32 aggregate15a = (Int32)((AliasedValue)c["opportunity_count"]).Value;
   System.Console.WriteLine("Count of all opportunities: " + aggregate15a);
   decimal aggregate15b = ((Money)((AliasedValue)c["estimatedvalue_sum"]).Value).Value;
   System.Console.WriteLine("Sum of estimated value of all opportunities: " + aggregate15b);
   decimal aggregate15c = ((Money)((AliasedValue)c["estimatedvalue_avg"]).Value).Value;
   System.Console.WriteLine("Average of estimated value of all opportunities: " + aggregate15c);
   System.Console.WriteLine("----------------------------------------------");
}

Regroupement multiple

Cet exemple explique comment utiliser l’attribut aggregate et plusieurs clauses groupby.

// *****************************************************************************************************************
//                FetchXML      byyrqtr   Aggregate 16           
// *****************************************************************************************************************
// Fetch aggregate information about the opportunities that have 
// been won by year and quarter.
string byyrqtr = @" 
<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='quarter' alias='quarter' />
      <attribute name='actualclosedate' groupby='true' dategrouping='year' alias='year' />
      <filter type='and'>
          <condition attribute='statecode' operator='eq' value='Won' />
      </filter>
   </entity> 
</fetch>";

EntityCollection byyrqtr_result = _serviceProxy.RetrieveMultiple(new FetchExpression(byyrqtr));

foreach (var c in byyrqtr_result.Entities)
{
   Int32 aggregate16d = (Int32)((AliasedValue)c["year"]).Value;
   System.Console.WriteLine("Year: " + aggregate16d);
   Int32 aggregate16 = (Int32)((AliasedValue)c["quarter"]).Value;
   System.Console.WriteLine("Quarter: " + aggregate16);
   Int32 aggregate16a = (Int32)((AliasedValue)c["opportunity_count"]).Value;
   System.Console.WriteLine("Count of all opportunities: " + aggregate16a);
   decimal aggregate16b = ((Money)((AliasedValue)c["estimatedvalue_sum"]).Value).Value;
   System.Console.WriteLine("Sum of estimated value of all opportunities: " + aggregate16b);
   decimal aggregate16c = ((Money)((AliasedValue)c["estimatedvalue_avg"]).Value).Value;
   System.Console.WriteLine("Average of estimated value of all opportunities: " + aggregate16c);
   System.Console.WriteLine("----------------------------------------------");
}

Trier par

Cet exemple explique comment utiliser l’attribut aggregate et plusieurs clauses orderby.

// *****************************************************************************************************************
//                FetchXML      byyrqtr2   Aggregate 17           
// *****************************************************************************************************************
// Specify the result order for the previous sample.  Order by year, then quarter.
string byyrqtr2 = @" 
<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='quarter' alias='quarter' />
      <attribute name='actualclosedate' groupby='true' dategrouping='year' alias='year' />
      <order alias='year' descending='false' />
      <order alias='quarter' descending='false' />
      <filter type='and'>
          <condition attribute='statecode' operator='eq' value='Won' />
      </filter>
   </entity> 
</fetch>";

EntityCollection byyrqtr2_result = _serviceProxy.RetrieveMultiple(new FetchExpression(byyrqtr2));

foreach (var c in byyrqtr2_result.Entities)
{
   Int32 aggregate17 = (Int32)((AliasedValue)c["quarter"]).Value;
   System.Console.WriteLine("Quarter: " + aggregate17);
   Int32 aggregate17d = (Int32)((AliasedValue)c["year"]).Value;
   System.Console.WriteLine("Year: " + aggregate17d);
   Int32 aggregate17a = (Int32)((AliasedValue)c["opportunity_count"]).Value;
   System.Console.WriteLine("Count of all opportunities: " + aggregate17a);
   decimal aggregate17b = ((Money)((AliasedValue)c["estimatedvalue_sum"]).Value).Value;
   System.Console.WriteLine("Sum of estimated value of all opportunities: " + aggregate17b);
   decimal aggregate17c = ((Money)((AliasedValue)c["estimatedvalue_avg"]).Value).Value;
   System.Console.WriteLine("Average of estimated value of all opportunities: " + aggregate17c);
   System.Console.WriteLine("----------------------------------------------");
}

Voir aussi

Page de grands ensembles de résultats avec FetchXML
Schéma FetchXML
RetrieveMultiple
RetrieveMultipleRequest
FetchExpression

Notes

Pouvez-vous nous indiquer vos préférences de langue pour la documentation ? Répondez à un court questionnaire. (veuillez noter que ce questionnaire est en anglais)

Le questionnaire vous prendra environ sept minutes. Aucune donnée personnelle n’est collectée (déclaration de confidentialité).

Partager via