Aggregations

Quick Links

Accounts Accounting Entries Products Customers Sales quotes Sales invoices Suppliers Purchase invoices

Aggregations in Sage Active allow you to compute totals, counts, averages, and grouped values across accounting, sales, and purchasing data.

There are two approaches to perform aggregations in the API.

Understanding the difference is important.

Approach 1 - Aggregations on standard queries (historical approach)

API resources / 📝Aggregations (historical approach)

This approach consists of adding aggregation parameters directly to existing business queries.

It is powerful but requires:

  • knowledge of the underlying queries
  • knowledge of available fields
  • manual construction of aggregation parameters

This approach is still supported for advanced usages.

Approach 2 - Aggregation API (recommended approach)

The Aggregation API already covers a wide range of business domains.

The following aggregation contexts are currently available through the catalog:

  • Accounting Entry Lines by Account
  • Accounting Entry Lines by Journal
  • Sales Quote Lines by Product
  • Sales Order Lines by Product
  • Sales Delivery Note Lines by Product
  • Sales Invoice Lines by Product
  • Sales Quotes by Customer
  • Sales Orders by Customer
  • Sales Delivery Notes by Customer
  • Sales Invoices by Customer
  • Sales Invoice Payments by Customer
  • Purchase Invoices by Supplier
  • Purchase Invoice Payments by Supplier

This list may evolve over time as new aggregation capabilities are added.

Sample of result

Thanks to the Aggregation API, this result is obtained with a single call to aggregationExecute, using only a few parameters:

  • an aggregation scope (entityKey = queryAggregateSalesInvoices)
  • a date range (dateMin = 2025-01-01, dateMax = 2026-12-31)
  • a grouping (groupByName = Customer)
  • an aggregation (aggregationType = SUM)
  • a period definition (periodType = Year)
  • a comparison flag (compare = true)
  • and a Top N limitation (top = 5)

The engine automatically handles:

  • period comparison (N / N-1)
  • ranking
  • grouping
  • percentage evolution calculation

The result shows, for each customer in the Top 5, the values for both periods and the percentage evolution.

Rank Customer Code Customer Name Period N Period N-1 Total Net (N) Total Net (N-1) Δ %
1 OPALE Opale 2026 2025 28,570 28,970 🔻 -1.38%
2 RUBIS Rubis sur ongle 2026 2025 14,364 342 🚀 +4100%
3 CARAT Carat S.a.r.l 2026 2025 12,800 24,100 🔻 -46.89%
4 BAGUES Bague’s en or 13 2026 2025 7,950 5,750 🔺 +38.26%
5 DUPOND Dupond INC 2026 2025 7,000 7,000 ➖ 0%

Aggregation Catalog

API resources / ⚙️Aggregation Catalog

This operation exposes a catalog of all available aggregations across Sage Active.

It allows you to discover:

  • what can be aggregated
  • how data can be grouped
  • which filters are available

This is the entry point for aggregations.

Aggregation Execute

API resources / ⚙️Aggregation Execute

This operation executes an aggregation based on the definitions returned by aggregationCatalog.

You no longer need to know the underlying business queries.

Recommendation

For any new development requiring aggregations, the recommended flow is:

  1. Call aggregationCatalog
  2. Build your aggregation dynamically
  3. Call aggregationExecute

The historical approach using aggregation parameters on standard queries should only be used for advanced or specific cases.

Using aggregationCatalog and aggregationExecute Operations Through an MCP Server

One possible use of the aggregationCatalog and aggregationExecute operations is to expose them as tools through an MCP server.
In this setup, an AI client can dynamically call these tools to discover available aggregations, understand how business metrics are computed, execute the appropriate queries, and translate the results into natural, user-friendly answers.
This removes the need for hardcoded knowledge in the AI layer and allows it to interact directly and reliably with the Aggregation API.

Conversation example

User Are any of these customers in my Top 10 billed revenue for 2025?

AI Let me check the available aggregations to find how billed revenue is calculated for Sales Invoices.

Tool call - AggregateCatalog This tool calls the Aggregation API aggregationCatalog operation to discover the appropriate Sales Invoices aggregation and identify that billed revenue can be computed using the invoice Total Net.

AI Great, I found the correct aggregation. Now I will run it to retrieve the Top 10 customers by billed revenue for 2025.

Tool call - AggregateExecute This tool calls the Aggregation API aggregationExecute operation with the relevant parameters for the year 2025.

AI Good news! Yes, Bague’s en or 13 (Metz) is in your Top 10 customers by billed revenue for 2025.

Here is the ranking:

  • Customer Demo - €53,107
  • Opale - €27,170
  • Dupond INC - €7,000
  • Bague’s en or 13 (Metz) - €2,150 ⭐
  • Carat S.a.r.l - €1,700
  • Ciselure - €530
  • Rubis sur ongle - €114
  • Grenat pour toi - €90
  • Diamant Vert - €41