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.

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

Pareto Columns (new in v7.2)

Using this feature you can easily add columns to a grid to display the cumulative sum/percentage of your data, and optionally a chart.

The columns you can add are:

1. Rolling total
2. Percentage of Total
3. Rolling Percentage of Total

You can optionally add a chart and sort the data too, to easily create a traditional pareto chart with just a couple of clicks.