When working in Excel with IBM Planning Analytics (TM1), efficient data aggregation is essential for building dynamic reports so here are five powerful ways to aggregate data using the DBRW function in TM1 with Slice:

You can easily select any consolidation in your dimensions by clicking in any cell with a SUBNM formula anywhere in your report.

As we all know, TM1 cubes are designed with hierarchical structures where parent elements automatically roll up data from their children. This built-in core aggregation method ensures that totals and subtotals are always available and dynamically updated as the underlying data is constantly updated and actualised.

That’s right, we have brought this feature back with Slice v4.6.0 from the good old Perspectives exclusively with Slice. By leveraging dynamic subsets within dimensions, you can aggregate values across a specific selection of elements. Using subsets as an element reference in Excel, you can create flexible and responsive reports that adjust to changing data structures.

When predefined consolidations don’t meet reporting needs, virtual consolidations are indeed an option (like subset references), allowing you to sum multiple elements on the fly. This can be done using formulas like DBRW() or DBR() toaggregate non-hierarchical elements.

For more advanced calculations, virtual expressions let you perform on-the-fly aggregations, averages and other calculations. You can combine DBRW()with mathematical operations to generate custom roll-ups, ratios, and weighted averages without modifying the underlying cube.

By using Excel’s native cell references within DBRW() or DBR() formulas, you can aggregate TM1 data dynamically. This approach is particularly useful when working with ad hoc reports that require flexibility.

Each of these methods provides a different way to aggregate data in TM1 Planning Analytics with Slice, depending on your reporting needs. Whether leveraging TM1’s built-in consolidations or using virtual consolidations for custom roll-ups, mastering these techniques become a second nature with Slice.

Need help optimising your TM1 reports? Let’s connect! 🚀

Try Slice for free and decide from experience