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). 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.

Insert at position - fixes the calculation to a certain row/column in the grid (available from Version 8). This is useful, for example, when calculating the difference between two members, irrespective of what members have been selected. The formula will be treated as a literal Excel cell references rather than pinned to particular members in the cube.

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.
   

Group Members

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.

Calculated Values on Measures

Within the Measures Hierarchy Editor, you can perform a calculation on a measure by right clicking and selecting 'Calculated value...'. This will open the MDX formula wizard from which you can create various calculations such as a calculation which will show percentage contribution to specific levels in a chosen hierarchy (measure to base).

From Version 9.1, this wizard also includes common time calculations such as Year to Date (YTD), Month to Date (MTD), Rolling Total and Previous Period. See MDX formula wizard.

• 
• 

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.