Skip to main content

Running Aggregates Tutorial

This tutorial builds a cumulative cost trend chart with a rolling average overlay using running aggregates in the Report Forge Output Designer.

Goal: A combo chart showing period-by-period cost as bars and cumulative cost as a line — with a secondary line showing the 3-period rolling average for trend smoothing.

Assumed blueprint structure: A repeating section called "Cost Report" with fields:

  • Period — Select (Jan, Feb, Mar, …) or Date
  • PeriodCost — Currency
  • BudgetCost — Currency

Step 1 — Open the Output Designer​

  1. Open the blueprint → click Output Designer.
  2. Select the output template to edit (or create a new one).
  3. Navigate to the page where the cost chart will go.

Step 2 — Add a Combo Chart component​

  1. In the component panel (left sidebar), drag a Chart component onto the canvas.
  2. In Chart settings → Chart type, select Combo (Bar + Line).
  3. Set chart title: Cumulative Cost Trend.

Step 3 — Configure the X-axis (period dimension)​

  1. Open the Data panel → X-axis.
  2. Set Dimension source to the Cost Report section.
  3. Set Dimension field to Period.
  4. Set Sort to Ascending (earliest period first).

The X-axis now shows the reporting periods in chronological order.

Step 4 — Add the Period Cost bar series​

  1. In Series, click + Add series.
  2. Set Series type to Bar.
  3. Set Series label to Period Cost.
  4. Set Value source to Cost Report.
  5. Set Value field to PeriodCost.
  6. Set Aggregation to Sum (sum within each period if multiple rows exist per period).
  7. Set Y-axis to Primary.
  8. Style: colour #4A90D9 (blue), bar width 70%.

Step 5 — Add the Budget Cost bar series​

  1. Click + Add series → Bar.
  2. Set Series label to Budget Cost.
  3. Set Value field to BudgetCost, Aggregation to Sum.
  4. Set Y-axis to Primary.
  5. Style: colour #BDBDBD (grey), bar width 70%, Stacking: None, Opacity: 60%.

The two bars now appear side-by-side per period.

Step 6 — Add the Cumulative Cost running_sum line​

  1. Click + Add series → Line.
  2. Set Series label to Cumulative Cost.
  3. Set Value source to Running aggregate.
  4. Configure running aggregate:
SettingValue
Source fieldPeriodCost
Aggregate typerunning_sum
Sort fieldPeriod
Sort directionAscending
Reset on groupOff (accumulate across all periods)
  1. Set Y-axis to Secondary (the cumulative total is much larger than period values — secondary axis keeps the scale readable).
  2. Style: colour #E5A500 (amber), line width 2px, solid, no markers.

Step 7 — Add the Rolling Average running_avg line​

The 3-period rolling average uses running_avg on the same field — it shows the average cost per period up to and including each point, smoothing out spikes.

  1. Click + Add series → Line.
  2. Set Series label to Rolling Average.
  3. Set Value source to Running aggregate.
  4. Configure:
SettingValue
Source fieldPeriodCost
Aggregate typerunning_avg
Sort fieldPeriod
Sort directionAscending
Reset on groupOff
  1. Set Y-axis to Primary (the rolling average is in the same range as period costs).
  2. Style: colour #E57373 (red), line width 2px, dashed, show point markers.
note

running_avg in Report Forge is a cumulative average — it averages all values from the first period up to the current one. A true 3-period rolling window (average of only the last 3 periods) requires an expression-based calculated field approach, which is not covered in this tutorial.

Step 8 — Configure the secondary Y-axis​

  1. Open Y-axis settings → Secondary axis.
  2. Enable secondary axis.
  3. Set label: Cumulative Cost ($).
  4. Set format: Currency, 0 decimal places.
  5. Enable Grid lines: Off (to avoid visual clutter with two axis grid systems).

Primary axis settings:

  • Label: Period Cost ($)
  • Format: Currency, 0 decimal places
  • Grid lines: On

Step 9 — Add a legend​

  1. Open Legend settings.
  2. Enable legend.
  3. Position: Bottom.
  4. Show all four series in the legend: Period Cost, Budget Cost, Cumulative Cost, Rolling Average.

Step 10 — Add a data table below the chart​

A data table beneath the chart gives reviewers precise values that are hard to read from the chart.

  1. Drag a Table component beneath the chart.
  2. Set data source to Cost Report, sorted by Period ascending.
  3. Add columns:
ColumnFieldAggregation
PeriodPeriodNone
Period CostPeriodCostSum
Budget CostBudgetCostSum
Cumulative Cost—Running aggregate: running_sum on PeriodCost
vs Budget—Calculated: [PeriodCost] - [BudgetCost]
  1. Add conditional formatting to the "vs Budget" column: values below 0 → red text.

Step 11 — Preview and verify​

  1. Click Preview in the Output Designer toolbar.
  2. Select a test edition with at least 4 periods of data.
  3. Verify:
    • Bar heights match the period cost values in your edition data.
    • The Cumulative Cost line increases monotonically (never goes down, assuming all costs are positive).
    • The Rolling Average line starts at the period 1 value (same as Cumulative ÷ 1) and settles toward the true average over time.
    • The secondary axis scale does not overlap the primary axis labels.

Common issues:

  • All bars the same height: Check that the X-axis dimension is not grouping all periods into one — verify the Period field has unique values per row.
  • Line starts at zero: Check the running aggregate sort field matches the X-axis dimension field.
  • Secondary axis numbers too small: Increase the axis minimum to 0 and let Report Forge scale the max automatically.

The finished chart​

Your completed chart shows:

  • Grey bars: Budget cost per period (what was planned).
  • Blue bars: Actual period cost (what was spent).
  • Amber line (secondary axis): Cumulative actual cost climbing through the programme.
  • Dashed red line (primary axis): Rolling average of actual period cost — shows whether spend per period is trending up or down.

This combination gives project managers both the period detail (bar chart) and the programme trend (cumulative line) in a single chart — with the rolling average helping identify spend acceleration or deceleration.