When OutSystems Aggregates Aren't Enough: Window Functions in O11

OutSystems Aggregates are good. For most queries in most applications, they’re exactly the right tool — visual, maintainable, safe, and fast enough.

Then you hit a reporting requirement that breaks them.

Running totals. Rank within a partition. Previous row comparison. Period-over-period variance. These are standard SQL problems with standard SQL solutions — window functions. OutSystems Aggregates can’t express them. You either approximate with multiple queries and application-layer logic, or you drop into Advanced SQL.

Most O11 developers choose the approximation. It works at small scale. It falls apart at enterprise scale — multiple round trips, excessive data transfer, calculation logic scattered across server actions that should live in the database.

On New Zealand’s largest OutSystems platform, serving 10,000+ daily users across a multi-restaurant estate, we hit this ceiling building KPI dashboards and sales reporting infrastructure. Here’s what we learned about when and how to use window functions in OutSystems O11.

What window functions are

A window function performs a calculation across a set of rows related to the current row — without collapsing those rows into a single result the way GROUP BY does.

The canonical examples:

-- Running total of sales by day
SUM(NetSales) OVER (ORDER BY SaleDate)

-- Rank each restaurant by sales within its region
RANK() OVER (PARTITION BY RegionId ORDER BY NetSales DESC)

-- Previous day's sales for period-over-period comparison
LAG(NetSales, 1) OVER (PARTITION BY RestaurantId ORDER BY SaleDate)

-- Row number within a partition (useful for deduplication)
ROW_NUMBER() OVER (PARTITION BY RestaurantId, SaleDate ORDER BY CreatedOn DESC)

The key characteristic: each row in the result set retains its individual identity. You get the aggregate context — the running total, the rank, the previous value — alongside the row’s own data. GROUP BY collapses rows. Window functions don’t.

OutSystems Aggregates can do GROUP BY. They cannot do OVER().

How to use Advanced SQL in O11

Advanced SQL is a query element available in Server Actions and Data Actions. It accepts raw SQL and maps the output to an OutSystems Structure.

1. Define an output Structure

Create a Structure in your Data tab that matches your query output:

SalesRankResult
├── RestaurantId     (Long Integer)
├── RestaurantName   (Text)
├── NetSales         (Decimal)
├── RegionRank       (Integer)
├── RunningTotal     (Decimal)

2. Write the Advanced SQL query

SELECT
    r.Id                                        AS RestaurantId,
    r.Name                                      AS RestaurantName,
    s.NetSales,
    RANK() OVER (
        PARTITION BY r.RegionId
        ORDER BY s.NetSales DESC
    )                                           AS RegionRank,
    SUM(s.NetSales) OVER (
        PARTITION BY r.RegionId
        ORDER BY s.NetSales DESC
    )                                           AS RunningTotal
FROM
    {Restaurant} r
    INNER JOIN {DailySales} s ON s.RestaurantId = r.Id
WHERE
    s.SaleDate = @SaleDate
    AND r.RegionId = @RegionId

Note the {Entity} syntax — OutSystems resolves entity references at compile time, mapping to the correct physical table name. Always use this for entity references in Advanced SQL. Never hardcode table names; they include a platform-generated prefix that changes between environments.

3. Map parameters

Advanced SQL parameters use @ParameterName syntax. Map them in the query element’s parameter list — OutSystems handles parameterisation, which means SQL injection protection holds.

4. Map output

Set the output type to List of SalesRankResult. OutSystems maps column names to Structure attributes by name — case-insensitive, exact match required. If your column alias doesn’t match your Structure attribute name, you get nulls.

The queries we actually used

Running ingredient variance

The stock control system needed a running variance — how the gap between theoretical and actual consumption accumulates across a period, per ingredient, per restaurant.

SELECT
    iv.IngredientId,
    i.Name                                          AS IngredientName,
    iv.PeriodDate,
    iv.TheoreticalUsage,
    iv.ActualUsage,
    iv.TheoreticalUsage - iv.ActualUsage            AS DailyVariance,
    SUM(iv.TheoreticalUsage - iv.ActualUsage) OVER (
        PARTITION BY iv.RestaurantId, iv.IngredientId
        ORDER BY iv.PeriodDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )                                               AS RunningVariance
FROM
    {IngredientVariance} iv
    INNER JOIN {Ingredient} i ON i.Id = iv.IngredientId
WHERE
    iv.RestaurantId = @RestaurantId
    AND iv.PeriodDate BETWEEN @PeriodStart AND @PeriodEnd
ORDER BY
    iv.IngredientId,
    iv.PeriodDate

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is the explicit frame clause — it accumulates from the first row in the partition to the current row. Without it, some databases default to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which behaves differently when there are ties in the ORDER BY column. Be explicit.

Doing this in application logic would mean: fetch all variance records, iterate in a server action, maintain a running total per ingredient in a local variable, append to an output list. It works. It’s slower, more code, and the calculation logic lives somewhere it doesn’t belong.

Day-part sales ranking

The KPI dashboard needed sales ranked by day-part within each restaurant group, for the current trading week.

SELECT
    ds.RestaurantId,
    r.Name                                          AS RestaurantName,
    ds.DayPart,
    ds.NetSales,
    RANK() OVER (
        PARTITION BY ds.DayPart
        ORDER BY ds.NetSales DESC
    )                                               AS DayPartRank,
    AVG(ds.NetSales) OVER (
        PARTITION BY ds.DayPart
    )                                               AS DayPartAverage
FROM
    {DailySales} ds
    INNER JOIN {Restaurant} r ON r.Id = ds.RestaurantId
WHERE
    ds.SaleDate BETWEEN @WeekStart AND @WeekEnd
    AND r.GroupId = @GroupId
ORDER BY
    ds.DayPart,
    DayPartRank

RANK() vs ROW_NUMBER() vs DENSE_RANK() — be deliberate. RANK() leaves gaps after ties (1, 1, 3). DENSE_RANK() doesn’t (1, 1, 2). ROW_NUMBER() assigns unique numbers regardless of ties. For sales ranking where you want tied restaurants to share a rank, RANK() or DENSE_RANK() is correct. ROW_NUMBER() is correct for deduplication.

Period-over-period comparison

Sales versus the same period last week, per restaurant, without a self-join:

SELECT
    RestaurantId,
    SaleDate,
    NetSales,
    LAG(NetSales, 7) OVER (
        PARTITION BY RestaurantId
        ORDER BY SaleDate
    )                                               AS SameDay7DaysAgo,
    NetSales - LAG(NetSales, 7) OVER (
        PARTITION BY RestaurantId
        ORDER BY SaleDate
    )                                               AS WeekOnWeekChange
FROM
    {DailySales}
WHERE
    RestaurantId = @RestaurantId
    AND SaleDate BETWEEN @PeriodStart AND @PeriodEnd
ORDER BY
    SaleDate

LAG(NetSales, 7) returns the value from 7 rows prior within the partition — which, when rows are daily and ordered by date, is exactly 7 days ago. No self-join. No subquery. No application-layer comparison logic.

Multi-tenant scoping in Advanced SQL

This is where Advanced SQL requires more discipline than Aggregates.

In an Aggregate, if you forget a tenant filter, OutSystems will still produce valid SQL — it just won’t be the SQL you wanted. In Advanced SQL, you have full control, which means full responsibility.

Every Advanced SQL query in a multi-tenant system needs an explicit tenant scope in the WHERE clause. No exceptions:

WHERE
    ds.RestaurantId = @RestaurantId  -- tenant scope, always first
    AND ds.SaleDate BETWEEN @PeriodStart AND @PeriodEnd

We enforced this as a code review rule: any Advanced SQL query without a tenant parameter in the WHERE clause doesn’t ship. Missing tenant scoping in a multi-restaurant system means one restaurant’s data leaks into another’s reporting. That’s a serious data integrity failure, not a minor bug.

Performance considerations

Window functions execute in the database, which is where they belong. The alternative — multiple round trips, data transfer, application-layer aggregation — is almost always slower at scale.

A few things to watch:

Indexing. Window functions partition and order by columns. If those columns aren’t indexed, you’re sorting large datasets at query time. On the stock variance query, (RestaurantId, IngredientId, PeriodDate) is a composite index that covers both the partition and the order — one index, used twice.

Frame clauses. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is generally faster than RANGE BETWEEN for running totals, because ROWS-based frames don’t need to handle duplicate values in the ORDER BY column. Use ROWS when you want exactly that behaviour.

CTE vs subquery. For complex window function queries, a CTE is often cleaner than a nested subquery and produces equivalent execution plans on modern SQL Server versions. OutSystems Advanced SQL supports CTEs:

WITH RankedSales AS (
    SELECT
        RestaurantId,
        NetSales,
        RANK() OVER (PARTITION BY RegionId ORDER BY NetSales DESC) AS RegionRank
    FROM {DailySales}
    WHERE SaleDate = @SaleDate
)
SELECT *
FROM RankedSales
WHERE RegionRank <= 5

This pattern — window function in the CTE, filter in the outer query — is the correct way to filter on a window function result. You cannot reference a window function alias in a WHERE clause directly; it doesn’t exist at the point WHERE is evaluated. The CTE makes it a derived column you can then filter on.

When not to use Advanced SQL

Advanced SQL is the right tool for a specific set of problems. It’s not a replacement for Aggregates.

Don’t use it for:

• Simple CRUD queries — Aggregates are faster to write, easier to read, and safer to maintain
• Queries where the output structure changes frequently — the Structure mapping is a maintenance overhead
• Anything a well-structured Aggregate can express correctly — the visual model is an asset, not a limitation

Use it for:

• Window functions — running totals, ranks, LAG/LEAD comparisons
• Queries that require CTEs for readability or correctness
• Complex multi-table joins where the Aggregate visual model becomes unreadable
• Performance-critical queries where you need precise control over the execution plan

The discipline is knowing which category your query falls into before you reach for Advanced SQL. Most queries don’t need it. The ones that do benefit significantly from it.

The broader principle

OutSystems Aggregates are excellent up to their ceiling. Understanding where that ceiling is — and knowing what’s on the other side of it — is the difference between a reporting system that works at development scale and one that works at production scale.

Window functions are standard SQL. They’re supported by SQL Server, which is what OutSystems O11 runs on. They belong in the database. The question is never whether to use them — it’s whether you’re reaching for them at the right moment or avoiding them because Advanced SQL feels like a departure from the platform.

It isn’t. It’s the platform working as designed.

On New Zealand’s largest OutSystems platform, the reporting layer uses Advanced SQL for exactly these queries — running variance, day-part ranking, period-over-period comparison — and Aggregates for everything else. KPI dashboards load in under two seconds across dozens of restaurants, with calculation logic that lives in the database where it belongs.

That’s the correct ratio. Advanced SQL as a precision tool, not a default.