Virtual Consolidations and Virtual Expressions

Virtual Consolidation and Virtual Expressions allow the user to introduce additional calculations to a worksheet by either selecting multiple elements from the set editor or by manually inserting an MDX set expression. These calculations are performed by the TM1 server, as part of the MDX query generated by Slice.

In order to create a virtual consolidation:

1. Double click on a cell containing a SUBNM function to display the set editor.

2. In the set editor, CTRL+click the elements you wish to rollup.

3. 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 permitted.

The same behavior can also be achieved without a SUBNM formula present. The string for representing a virtual consolidation can also be entered manually into a cell and then be referenced, e.g. by a DBRW.

Similar to Virtual Consolidations, Virtual Expressions allow the addition of on-the-fly calculations to be added to an Excel Worksheet. MDX-based functions, such as AVG, MIN, MAX, STDDEV, etc. can be utilized.

A Virtual Expression starts with ## following by the function name and then the elements:

• ##AVG({[Region].[VIC], [Region].[NSW]})

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

The list of all numeric functions supported by the TM1 can be found here.