Grid Calculations

The Add Calculation option is available on the Grid's right mouse button pop-up menu.

For example, if you have the Time hierarchy on the columns and right click to add a Grid Calculation to Q2 you can add an additional Q2-Q1 delta column to the Grid. The column is inserted as an empty column to which you can add a title and formula. 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.

This column will be automatically refreshed as you drill or swap dimensions.

You can view or delete the inserted rows/columns on the XLCubed > Calculation > Manage Calculations form.

Video demonstration

Click here for videos showing grid calcualtions.

Examples (based on the Bicycle Sales cube)

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

1. Insert a Grid based on the Bicycle Sales demo cube, with the Time hierarchy on columns
2. Drill down Time's All member by double-clicking on B5:

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

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

5. In one of the column's data cells (for example, F6) type the formula for the Calculation, =E6-D6. You may need to resize the column:

6. The Grid Calculation column will now be used, even if the structure of the Grid is changed:

More examples - Grid Calculations

This is how you can add a grid calculation that is not tied to members.

A typical scenario would be a grid report that needs to show two different years on columns where the years can change.

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

Option 1: using Excel functionality

1. Use Excel to create a calculation outside of the grid – in this example enter formula in cell E6 as follows: =C6+D6

2. As you can see in the screenshot below, Excel has filled in the sum of C6+D6

3. Right-click grid, Properties and on Behaviour tab check Fill Formulae next to Grid.

4. You can see the calculation has been done for all rows in grids.

Option 2: using XLCubed Grid Calculations

1. Add a grid calculation

2. Right-click to edit the calculation

3. In the window above delete everything in the Value area and enter a formula that refers to the cell locations eg =C6+D6.

4. Uncheck anything in the Attached to area and also select Insert at end.

5. The screenshot above shows that the calculation has been done for all rows.

Member Selector and Adhoc Groupings

Users can quickly add custom groupings into their reporting, with the calculations then available for selection from the member selector across all grids and formulae sharing the same cube connection within the workbook. This is done on the right click menu in the member selector as shown below:

1. Choose the members to group, and select group members.
2. Then name the grouping as required, and choose the parent element which the group is to appear under.

The new element will appear as a custom calculation. If you wish to amend or delete it, select the custom calculation tab on the XLCubed ribbon.

Share to Base: Calculated Value 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.

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.