When working with Dataverse, developers often begin with QueryExpression for programmatic data retrieval.

However, as solutions mature, requirements evolve beyond simple record retrieval.

Real-world systems require:

• Revenue totals
• Record counts
• Average values
• Grouped datasets
• Date-based reporting
• Dashboard analytics

This is where FetchXML becomes essential.

FetchXML is Dataverse’s most powerful query language for analytics and reporting scenarios.

In this deep dive, we’ll explore:

• FetchXML fundamentals
• Aggregates (SUM, COUNT, AVG, MIN, MAX)
• Group By queries
• Date grouping
• Filters in aggregates
• Reading results in C#
• Performance best practices

What Is FetchXML?

FetchXML is an XML-based query language used to retrieve data from Dataverse.

It is used across multiple Microsoft technologies:

• Advanced Find
• Power Automate
• SSRS Reports
• Power Pages
• SDK integrations
• Dashboards

Unlike QueryExpression, FetchXML supports database-level aggregation.

Basic FetchXML Structure

<fetch>
  <entity name="account">
    <attribute name="name" />
  </entity>
</fetch>

Core components:

Element	Purpose
fetch	Root query node
entity	Primary table
attribute	Columns
filter	Conditions
link-entity	Joins

Enabling Aggregates

To perform aggregation, set:

aggregate="true"

Example:

<fetch aggregate="true">

This tells Dataverse to compute results instead of returning raw records.

COUNT Aggregate

Scenario

Count total Active Accounts.

<fetch aggregate="true">
  <entity name="account">

    <attribute name="accountid"
               alias="accountcount"
               aggregate="count" />

    <filter>
      <condition attribute="statecode"
                 operator="eq"
                 value="0" />
    </filter>

  </entity>
</fetch>

Reading COUNT in C#

var count = (int)((AliasedValue)
    results.Entities[0]["accountcount"]).Value;

Aggregate results always return AliasedValue.

SUM Aggregate

Scenario

Total Estimated Revenue.

<fetch aggregate="true">
  <entity name="opportunity">

    <attribute name="estimatedvalue"
               alias="totalrevenue"
               aggregate="sum" />

  </entity>
</fetch>

Reading SUM Result

var revenue = ((Money)((AliasedValue)
    results.Entities[0]["totalrevenue"]).Value).Value;

Money fields must be cast to Money first.

AVG Aggregate

<attribute name="estimatedvalue"
           alias="averagevalue"
           aggregate="avg" />

Useful for sales performance metrics.

MIN / MAX Aggregates

<attribute name="estimatedvalue"
           alias="maxvalue"
           aggregate="max" />

<attribute name="estimatedvalue"
           alias="minvalue"
           aggregate="min" />

Used for range analysis.

Group By Queries

Aggregates become significantly more powerful when grouped.

Scenario — Revenue by Account

<fetch aggregate="true">
  <entity name="opportunity">

    <attribute name="estimatedvalue"
               alias="totalrevenue"
               aggregate="sum" />

    <attribute name="parentaccountid"
               alias="account"
               groupby="true" />

  </entity>
</fetch>

Each result row represents one Account group.

Reading Grouped Results

foreach (var entity in results.Entities)
{
    var account = ((EntityReference)
        ((AliasedValue)entity["account"]).Value).Name;

    var revenue = ((Money)
        ((AliasedValue)entity["totalrevenue"]).Value).Value;
}

Multiple Column Grouping

Example:

Revenue grouped by:

• Account
• Owner

<attribute name="parentaccountid"
           alias="account"
           groupby="true" />

<attribute name="ownerid"
           alias="owner"
           groupby="true" />

Creates composite grouping.

Date Grouping

FetchXML supports time-based grouping — extremely useful for dashboards.

Opportunities by Year

<attribute name="createdon"
           alias="year"
           groupby="true"
           dategrouping="year" />

Supported values:

Value	Use Case
day	Daily metrics
week	Weekly trends
month	Sales reporting
quarter	Financial reporting
year	Annual performance

Filtering Aggregate Queries

Filters apply before aggregation.

<filter>
  <condition attribute="statecode"
             operator="eq"
             value="0" />
</filter>

This ensures only relevant records are aggregated.

Joins in Aggregate Queries

FetchXML supports joins inside aggregates.

Example — Revenue by Account Industry

<link-entity name="account"
             from="accountid"
             to="parentaccountid"
             link-type="inner">

  <attribute name="industrycode"
             alias="industry"
             groupby="true" />

</link-entity>

This allows cross-table grouping.

Ordering Aggregate Results

You can sort grouped results.

<order alias="totalrevenue"
       descending="true" />

Useful for leaderboard-style reports.

Executing FetchXML in C#

var fetchXml = @"<fetch aggregate='true'> ... </fetch>";

var results = service.RetrieveMultiple(
    new FetchExpression(fetchXml)
);

Performance Benefits

FetchXML aggregates execute at SQL level.

Benefits:

• No record looping
• Reduced network payload
• Faster query execution
• Scales to millions of rows

Best Practices

✔ Always use alias for aggregates
✔ Expect AliasedValue in results
✔ Filter before grouping
✔ Limit grouped columns
✔ Avoid aggregates in synchronous plugins
✔ Use for dashboards & reports

Plugin Performance Warning

Aggregates inside plugins can cause:

• SQL load spikes
• Timeouts
• Transaction delays

Better alternatives:

• Power BI
• Synapse Link
• Data Export
• Azure Functions

Common Mistakes

Mistake	Impact
Missing alias	Query failure
Wrong type casting	Runtime error
Aggregates in plugins	Performance issues
No filters	Large SQL scans

Real-World Use Cases

• Sales revenue dashboards
• Case volume reports
• SLA breach analytics
• Opportunity forecasts
• Activity productivity tracking

FetchXML powers most enterprise reporting in Dataverse.

Conclusion

FetchXML is the analytics engine of Dataverse querying.

While QueryExpression excels at transactional retrieval, FetchXML unlocks:

• Aggregation
• Grouping
• Date analytics
• Reporting datasets

Mastering FetchXML ensures you can build scalable dashboards, reports, and enterprise insights directly on Dataverse data.