Preserving Excel Formulas Within and Outside of Essbase Ad Hoc Grids
Data source types: Oracle Essbase
Formulas Within the Grid
Starting in Oracle Smart View for Office 11.1.2.5.810, formulas will be retained in Oracle Essbase grids after performing Undo or Redo.
Note:
Ensure that you have selected the Preserve Formulas and Comments in ad hoc operations (except pivot and undo) option in the Options dialog, Member Options tab.
To retain the formulas, you must first perform a Refresh operation on the grid that contains formulas. The Refresh action adds the formulas to the undo buffer.
In some cases, an Undo doesn't work as expected after a zoom operation when formulas are outside of the grid. In this case, for more reliable formula preservation when performing Undo or Redo after a zoom operation, set up your grid as shown in Formulas Outside the Grid.
Formulas Outside the Grid
For smoother functioning of Essbase ad hoc analysis when formulas are present outside of the grid, as a best practice, Oracle recommends that you add a simple comment in any blank cell of the last column. The formula will be retained when you refresh the grid. The formula will be lost on pivots and undo.
Note:
Ensure that you have selected the Preserve Formulas and Comments in ad hoc operations (except pivot and undo) option in the Options dialog, Member Options tab.
In the example below, a formula is added to cell E4, outside of the grid. In order to preserve this formula during refreshes, a comment should be added to a blank cell in column E. In the example, the word "comment" is added to cell E1.
| - | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | East | comment | ||||
| 2 | Scenario | |||||
| 3 | Profit | |||||
| 4 | Cola | Qtr1 | 5789 | =C4 | ||
| 5 |
The comment is needed especially if you are performing an active ad hoc analysis This helps Essbase and Smart View to keep the content lying outside the grid intact during refresh and other ad hoc operations such as Zoom In and Zoom Out or Keep Only and Remove Only. Without the comment, you may lose the formulas outside the grid when performing these ad hoc operations.