ROLLUP_TO
Category: Dimensionality & hierarchies
Overview
The ROLLUP_TO function rolls up a node to match the level-dimensionality of another node. All levels that exist in the first node but not in the second are removed, and their values are aggregated.
Use this function when you want to automatically match one node’s dimensionality to another without manually specifying which levels to keep.
Syntax
ROLLUP_TO('Node1', 'Node2')
Example usage: ROLLUP_TO('Profit', 'Budget')
Parameters
| Parameter | Description | Type | Required |
|---|---|---|---|
| Node1 | The input node to be rolled up, specified in single quotes (e.g. 'Detail') | Node reference | Yes |
| Node2 | The target node whose level-dimensionality defines the output shape, specified in single quotes (e.g. 'Summary') | Node reference | Yes |
Output Shape
| Aspect | Behavior |
|---|---|
| Dimensionality | Reduced to only the levels present in Node2 |
| Values | Only the values of Node1 are used; Node2 only determines the shape |
| Row count | Reduced. Values are aggregated across the removed levels |
Watch Out
- Node2 cannot contain levels that are not in Node1. Every level in Node2 must also exist in Node1.
- Node1 and Node2 cannot have the same set of levels. Node2 must have fewer levels than Node1, otherwise there is nothing to roll up.
- The formula can feature only two inputs.
- The values of Node2 are ignored. Only its dimensional structure matters.
Example
Input node: Profit
| Product Group | Product | Year | Month | Value |
|---|---|---|---|---|
| Cars | Model T | 2025 | 2025-01 | 11 |
| Cars | Model T | 2025 | 2025-03 | 5 |
| Motorcycles | V-Twin | 2026 | 2026-02 | 7 |
| Motorcycles | 101 Scout | 2026 | 2026-01 | 19 |
Target node: Budget
Budget data only has Product Group and Year.
| Product Group | Year | Value |
|---|---|---|
| Cars | 2025 | 50 |
| Motorcycles | 2026 | 70 |
Match dimensionality of a target node
You want to compare actual quantities against budget, but the budget node only has Product Group and Year, no Product or Month detail.
Formula: ROLLUP_TO('Profit', 'Budget')
| Product Group | Year | → ROLLUP_TO Result |
|---|---|---|
| Cars | 2025 | 11 + 5 = 16 |
| Motorcycles | 2026 | 7 + 19 = 26 |
Product and Month are removed because they don’t exist in Budget.
The values of Budget (50, 70) are not used, only its dimensional structure.
Related Functions
| Function | When to use instead |
|---|---|
| ROLLUP | When you know exactly which levels to keep. |
| DROPLEVEL | When you know which levels to remove. |
| EXPAND | When you need to add levels instead of aggregating. |