Preserving Formulas when Retrieving Data

When you retrieve data into a worksheet with formulas, you must select the option to preserve the formulas in the cells or Essbase may overwrite them when retrieving data.

Collectively, the Formula Preservation options enable you to retain formulas during data retrievals, keep and remove only operations, and drill operations. In addition, Essbase can replicate formulas for additional members retrieved into the worksheet as part of a drilling operation.

Keep in mind these guidelines and restrictions when using the Formula Preservation options:

On the Mode page of the Essbase Options dialog box, you must select the Advanced Interpretation option to enable Retain on Retrieval. The Formula Preservation options do not work with free-form retrieval mode.
You must enable Retain on Retrieval to enable Retain on Keep and Remove Only and Retain on Zooms.
You must enable Retain on Zooms to enable Formula Fill.
When you select the Retain on Retrieval option, the Suppress #Missing Rows and Zero Rows options on the Display page are not available. If you selected a Display option, the Formula Preservation options become unavailable automatically.
When you select the Retain on Zooms option, the Remove Unselected Groups option on the Zoom page is not available. When you enable the Remove Unrelated Groups option, Retain on Zooms becomes unavailable automatically.
When Retain on Retrieval is selected, retrieval time may be slightly delayed.
As a general rule, insert a blank row as the last row in the formula range. This action ensures that the cell range in the formula expands properly when you drill down on members when Retain on Zooms is selected.
For example, cell B6 is in a blank row. Therefore, the formula for cell B7 should be =SUM(B2:B6) to ensure that it contains the blank row:
When you drill down on Qtr4, the formula for cell B9, =SUM(B2:B8), properly expands to include the range of Oct, Nov, and Dec:
Formula arrays are not supported in Spreadsheet Add-in when the preserve formula option is on. If formula arrays are in the worksheet, Essbase does not preserve these types of formulas.

To preserve formulas when retrieving data:

Select Essbase, and then Options.
In Essbase Options, select Mode and select Advanced Interpretation.
In Formula Preservation, select Retain on Retrieval, to enable Formula Preservation mode.
To save this setting, click OK.
To update the worksheet with the latest data values, select Essbase, and then Retrieve.