15.3 Using Consolidations¶
Once created, a consolidation works like a dimension: drag it into a table, chart or filter. This topic covers where it can be used, how it calculates with metrics, and what to watch out for with totals.
Adding a Consolidation to a Visual¶
Drag it from the Object Tree – Report Objects → Custom dimensions for the workbook's own, or Dimensions for global ones – onto a visual, like any dimension unit. See 5.6 Side Panels.
Visibility: Here, Workbook or Everywhere¶
In design mode, right-click the consolidation in the Element Structure panel → Visibility:
| Choice | Available in |
|---|---|
| Only here | This visual only |
| Entire workbook | All visuals of the workbook; listed under Report Objects → Custom dimensions |
| Everywhere | All workbooks of the project; listed under its dimension |
Widening the visibility moves the consolidation; narrowing it makes a copy, so other users keep theirs. Under Custom dimensions, Make global does the same as Everywhere.
Make a consolidation global only when it's meant for everyone – e.g. the official sales regions.
Calculating with Metrics¶
For plain groups, a member's value is simply the sum of its values. For ratios it matters what is calculated first. The metric setting Evaluation order in consolidations decides (in the metric's advanced settings – see 10.3 Creating and Editing Metrics):
| Setting | Margin % for the member Nordic |
|---|---|
| Consolidation expression first | Total profit of Nordic ÷ total revenue of Nordic – usually what you want |
| Metric expression first | The member's expression applied to each country's margin % |
| (Automatic) (default) | Consolidation first for metrics that multiply or divide other metrics, otherwise metric first |
Totals¶
Totals are calculated from the data, not by adding up the member rows. They can therefore differ from the sum of the rows when members overlap, when some members are hidden, or when members are calculations. In an income statement built from calculated members, hide the total and show the result as a member instead.
Drilling¶
Double-clicking a member opens the data behind it in a new window, filtered to the member's values – e.g. the postal codes of Jutland. See 6.2 Drilling Down and Drilling Through.
Tips – Using Consolidations¶
- Use Entire workbook for consolidations used in several visuals of the same workbook, so you edit them in one place.
- A consolidation can also drive a filter: put it in a filter visual or Filter Panel box to let users pick a group.
Common Issues – Using Consolidations¶
Problem: The total doesn't match the sum of the rows. - Solution: Members overlap, are hidden or calculated. See Totals above.
Problem: Roll-up doesn't accept my consolidation. - Solution: Consolidations can't be used in Roll-up quick transformations.