Unpacking 3 New Features for the DBRW in Slice

Since its first introduction, DBRW has become the most popular function to use in Excel reports amongst the IBM Planning Analytics (TM1) community.

DBRW allows you to bring TM1 data into a cell by referencing one element for each dimension in a cube like in the example below:

DBRW became very popular because it allows users to create complex Excel reports that combine various data from different cubes into the same table. This makes the world of difference when trying to unlock insights across your different data stores.

Slice adds three new innovative features into the DBRW function that provide even more value for all your Excel reporting needs.

Let’s run through each in turn.

1 – Virtual Consolidations

Virtual Consolidations allow the user to introduce additional consolidations to a worksheet by selecting multiple elements from the set editor. Slice will then sum the values of these elements and display the results in the report itself

In order to create a virtual consolidation, follow these steps:

Double click on a cell containing a SUBNM function to display the set editor.
In the set editor, CTRL + Click the elements you wish to roll up.
After applying the change, you will see a new display in the cell, which shows a ‘#’ before the list of elements that are being rolled up.

For example, #VIC+QLD+Tasmania will give you the sum of VIC, QLD, and Tasmania.

The default operation for these virtual consolidations is to create a sum of all the selected elements, but other operators, such as subtraction, multiplication, and division are also permitted.

The same functionality can also be achieved without a SUBNM formula present. Instead, the string for representing a virtual consolidation can be entered manually into a cell before being referenced by a DBRW. This allows for fantastic flexibility and agility in your report building.

2 – Virtual Expressions

In a similar vein to Virtual Consolidations, Virtual Expressions allow the addition of on-the-fly calculations on Excel worksheets. MDX-based functions, such as AVG, MIN, MAX, STDDEV, etc. can be utilized to build the report as needed.

A Virtual Expression starts with ## followed by the function name and then the elements.

For example: ##AVG({[Region].[VIC], [Region].[NSW]})

The illustration below shows how an average of two territories can be created with ease. In this case, the SUBNM is removed from the worksheet and is replaced with a Virtual Expression.

3 – Using a Range of Cells or a Named Range in DBRW

Slice enables you to get on-the-fly calculations that are based on a specific range of cells or even a named range. For example, if you want to show the sum of the last three months, the top 5 sales items, or the bottom 5 sales items – all of this can now be done easily with Slice.

In the example below we are using a named range called TopSalesModels including the top 5 sales models for the selected version:

Then in the DBRW function, instead of referencing just one element, you can use the TopSalesModels text defined above and you’re off to the races!

Now your Active Form can display the sum of the top 5 models by month and by region dynamically, providing real-time reporting functionality like you’ve never seen before.

Conclusion

Slice has been built for those who love TM1 Perspectives and want to use hierarchies at a more advanced level. Slice extends the functionality of the DBRW with many more innovative features that completely transform what is possible with TM1. It’s a powerful tool that can make your day-to-day operations that much more efficient – while maintaining the intuitive interface that you’ve come to expect.

If you’d like to try out Slice, the Community Edition is completely free to download. This includes all the features that you are familiar with in terms of Perspectives as well as hierarchies. If, after that, you feel that you need more functionality – then you can always upgrade to the Business Edition which gives you access to a range of additional features as well as dedicated product support.

Try it today