Contrary to folk wisdom, if it walks like a duck and quacks like a duck, it’s not always a duck!

Most people use Simba’s MDX Provider for Oracle OLAP to create an Excel PivotTable to summarize and analyze data in an Oracle OLAP Database cube.  However, once the data is in the PivotTable, many users would like to add additional columns or calculations to their analysis.  In a normal spreadsheet, that’s easy, right?  Insert a column, type in the formula you wish to use (e.g. ratio of actual vs. target), and Excel will propagate that formula to every row in your column.

Even though a  PivotTable looks like a regular table in an Excel workbook, it isn’t one.  In reality, it is a single object that is displayed in a worksheet in a familiar format.  As you drill down or expand rows or columns, your PivotTable will grow and shrink as necessary, and any data you have in cells next to your PivotTable on the right or below it will be overwritten as it expands (though at least Excel warns you that you are about to overwrite some cells).   In fact, we recommend that if you must show additional information on your worksheet that is not part of the PivotTable, that you put it above or to the left of your PivotTable so it remains persistent.

Because your PivotTable is one single object, you can’t simply split it down the middle to insert a new row or column with your own formula.   But, there is good news – there are ways to add a new row or column to your PivotTable with your own formulae.  With PivotTables, it’s all about your data source.

  • In the rare case that the source data for your PivotTable is in one of your other worksheets in your Excel workbook, you can’t insert anything you want, but you can insert a new calculated field.  Just click anywhere in your PivotTable, and select from the ribbon Home>Insert>Insert Calculated Field.
  • In the more likely case that your data is a connection to an external data source, you need to go to your data source to add the calculation.  In the case of Oracle OLAP, you would use Oracle’s Analytic Workspace Manager (AWM) to add another calculated measure or calculated member to your cube.  After you have “maintained” your cube, the new member or measure shows up in the PivotTable field list, and you can add that as a row or column in your PivotTable.

However, if the above doesn’t apply to you, e.g. you want to add something that is not a calculated field, or you do not have access to the Oracle AWM or the cube in order to create a new calculated member or calculated measure, you can still have your way.   Here’s how:

  1. Click anywhere in the PivotTable
  2. On the ribbon, select PivotTable Tools > Options > OLAP Tools > Convert To Formulas

Your PivotTable has been turned into a regular table, with each cell containing its own formula to extract data from the database.  Your data will continue to be connected to your data source, and you can insert a row or column wherever you like in your table, with any value or calculation you need.   What you have lost, however, is the ability to change the structure of the PivotTable by adding or removing hierarchies, and you can no longer drill down or up through the various levels – your PivotTable has become static in structure but still refreshable from the OLAP data source.   Perhaps that’s exactly the trade-off you are looking for.

And that is how you get all your ducks in a row (or column) in a PivotTable!

Happy Holidays!