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.
Examples (based on the Bicycle Sales cube)
We want to see the difference between 2004 and 2003 in a Grid.
- Insert a Grid based on the Bicycle Sales demo cube, with the Time hierarchy on columns
- Drill down Time's All member by double-clicking on B5:
- Right-click on 2004 (E5) and choose XLCubed > Add Calculation
- In the title cell (Calc, F5), type a name for the Calculation:
- 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:
- The Grid Calculation column will now be used, even if the structure of the Grid is changed: