Grid Calculations

Calculations can be added to any XLCubed grid and allow for users to create quick calculations that might not be contained in the cube. These calculations can use any standard Excel formula, from simple sums to complex vlookups, as well as XLCubed formulae and will be automatically propagated to all the other cells in the row or column. Grid calculations can be linked to grid members in order to respect drilling and swapping dimensions.

Video demonstration

Click here for videos showing grid calcualtions.

Creating and Managing Grid Calculations

The Add Calculation option is available on the Grid's right mouse button pop-up menu. Alternatively, grid calculations can be added from the Row Calculation or Column Calculation menus in the Grid ribbon (visible when selected cell is on grid).

An empty calculation will be added below the current cell. The formula can be typed into any of the cells, and will be propagated to all the other cells in the row or column automatically. Overtyping the title cell (defaults to 'Calc') will give the calculation a title.

Double clicking the title cell opens the Edit Grid Calculation dialog. This can also be accessed from XLCubed > Calculation > Edit Calculations right click option. In this dialog you can edit the title, number format and calculation position.

Calculation Position

Link to member - position the calculation either before of after a slice in the grid (shown in the members box at the bottom of the dialog).

Insert at position - fixed the calculation to a certain row/column in the grid.

Insert at end - insert the calculation at the end of the rows/columns, even when drilling.

Attached to - If a calculation is fixed to a hierarchy, then it will move with the hierarchy if it is swapped between rows and columns. From version 9, it is possible to attach the calculation to an axis so that it remains on that axis even if the grid structure is changed.

Deleting Calculations

You can view, manage and deleting existing calculations in the XLCubed > Calculation > Manage Calculations dialog (also accessible through the Grid ribbon). A calculation can also be deleted by selecting the XLCubed > Calculation > Delete Calculation right click option.

Example - Insert after slice

We want to see the difference between 2004 and 2003 in a Grid.

1. Right-click on 2004 (E5) and choose XLCubed > Add Calculation

2. In the title cell (Calc, F5), type a name for the Calculation:

3. In one of the column's data cells (for example, F6) type the formula for the Calculation, =E6-D6.

4. The Grid Calculation column propogate down onto all rows, even if the grid is drilled into or the structure of the grid is changed:

Example - Insert at end

The grid calculation in the example above was tied to the 2004 member. If that member no longer appeared in the grid (say, due to a slicer selection) then the calculation would also no longer appear.

It is possible to add a grid calculation that is not linked to a member but rather is always positioned at the end of the axis. This may be useful in a grid report that needs to show two different years on columns but where the years can change.

There are two methods to achieve this result: the first uses Excel functionality outside of the XLCubed grid, the second uses XLCubed grid calculation functionality referring to cell locations.

Using Excel functionality

To create a calculation at the end of the grid, select the relevant cell and fill in the formula.

Right click on the grid and select XLCubed > Properties > Behaviour and check the 'Fill Formulae next to Grid' option. This will detect the grid's size and propagate any adjacent formula across the relevant amount of rows/columns. It may also be necessary to set the grid to 'shift' rows/columns so that the formula is not overridden on grid drills (see Grid Properties).

For example, see the grid below with adjacent formula =C6-D6

1. Right-click grid, Properties and on Behaviour tab check Fill Formulae next to Grid.
   
2. You can see the calculation has been done for all rows in grids.
   

Using XLCubed Grid Calculations with 'Insert At End'

With this method, we can insert a grid calculation on rows/columns and change it's properties so that it is always positioned at the end of the axis.

1. Add a grid calculation
   

   
   

2. Right-click to edit the calculation. Delete everything in the Value area and enter a formula that refers to the cell locations e.g. =C6-D6. The calculation default to Insert after slice - change this to Insert at end.
   

   
   

3. Select ok, and the calculation appears at the end of the columns axis, regardless of what years are being shown.
   

Member Selector and Adhoc Groupings

Users can quickly add custom member groupings into their reporting - these calculations will then available for selection from the member selector across all grids and formulae sharing the same cube connection within the workbook.

To add a grouping, go into the the grid designer and in the member selector select the members you wish to group (holding Ctrl will allow you to select more than one member). Right click and select Group Members. You can then define a name for the grouping, the type of aggregate to be applied and the parent member the calculation will appear under.

• 
• 

The calculation will then appear in the member selector under the defined parent with the defined name. This can then be selected to be used as part of the grid.

If you wish to amend or delete the calculation, select the Custom Calculations tab on the XLCubed ribbon. This shows all user defined calculations in the workbook which can then be edited or deleted.

Measure to Base: Calculated Values on Measures

For measures, users can add a calculation which will show percentage contribution to specific levels in a chosen hierarchy. This is available in the member selector for measures, on the right click menu as shown below.

In this example a calculation is added to show the Reseller sales amount as a percentage of the parent at the country level in the Geography hierarchy.

See MDX formula wizard for more information on this dialog.

Pareto Columns (new in v7.2)

The Pareto Principle is often referred to as the 80-20 rule, that 80% of outcomes are attributable to 20% of causes. They are named after Vilfredo Pareto who lived in Italy in the 19thcentury and observed that 80% of the land was owned by 20% of the people. Pareto charts have both bar charts and a line graph where the bars represent individual values and the line represents the cumulative total.

To use Pareto Charts from XLCubed, within a grid, right-click on the column header to access XLCubed’s right-click menu, Grid Charts and Add Pareto Analysis.

Take this simple grid showing Reseller Sales for Product Model Categories for Canadian cities:

Right-clicking on All Products to Add Pareto Analysis brings up this window:

Click OK to return to the workbook and you will see that we have a chart showing that the top 9 cities provide some 80% of the sales.

You could also include the rolling total and percentage in your Pareto Chart.

Notice that we now also have some extra columns on the grid showing the cumulative total of all sales, the sales percentage per category and the cumulative percentage.

So that's Pareto Charts - in a nutshell, an easy to use graphical tool which ties directly into dynamic XLCubed grids.

Fixed Position Calculations (new in Version 8)

By default grid calculations are pinned to specific members of the hierarchy. This means that the calculations can be highly dynamic, appearing in the grid wherever the pinned member does, even after the member moves due to drilling or slice and dicing. Sometimes it can be useful to have a 'static' calculation, which is always in column 3 for example, irrespective of what members are chosen in the grid. A scenario would be that you choose a year in a slicer, and the quarters within that year are shown in the grid. You may want to always show the difference between Q2 20XX and Q1 20XX, irrespective of what year is chosen and version 8 enables this by providing 'unpinned' calculations which are inserted at a fixed position. Using this approach the calculation will always appear in the specified row or column irrespective of which other members are chosen.

To use this approach, insert the calculation as normal, and once inserted right click on the title to edit the calculation. You can now choose 'Insert at Position' and specify the column or row. On exiting the dialog type in your formula and it will be treated as a literal Excel cell reference rather than pinned to the members in the cube.

Fixed Axis Calculations (new in Version 9)

Fixed position calculations from v8 are still attached to the hierarchies on the axis. In some cases you may want to be able to swap the hierarchies on Columns, and still have the fixed position calculations appear.

This is possible in v9, in the edit dialog you can now specify that the calculation is fixed on the axis, not the hierarchies.

Time Calculations (new in Version 9.1)

Common time calculations such as Year to Date (YTD), Month to Date (MTD), Rolling total and Previous Period can easily be created using the calculation wizard, there are more details on the MDX formula wizard page.