In Retrieving Data into Formatted Worksheets, you used the Retain on Retrieval option to preserve formatting and formulas in a worksheet. This topic shows you how to create a report using Formula Preservation options.
To preserve formulas when retrieving or retaining data:
Press Alt and, in cell E1, drill down (double-click) on Scenario.
Select cell G3 and enter this formula in the cell: =B3/B$7*100
The $ in front of the 7 anchors the formula to the Year member.
The spreadsheet calculates the formula that you entered in cell G3 and now reflects Qtr1 as a percentage of Year.
In Mode, under Formula Preservation, select Retain on Retrieval and Retain on Keep and Remove Only, and then click OK.
In cells D2, E2, and F2, respectively, select Variance, %Variance, and Scenario.
Select Essbase, and then Remove Only.
Essbase removes the selected columns but retains the formula that you entered, keeping it with the retained dataset.
In Mode, under Formula Preservation, select Retain on Zooms, and then click OK.
In cell A3, drill down (double-click) on Qtr1.
Essbase drills down on Qtr1 and moves the formula down with the Qtr1 member.
In Mode, under Formula Preservation, select the Formula Fill , and then click OK.
In cell A3, drill down (double-click) on Qtr1.
Essbase drills down on Qtr1 and replicates the formula for each member of Qtr1 (Jan, Feb, and Mar). To view the replicated formulas, click in cells D3, D4, D5, and D6 and examine the syntax in the spreadsheet formula bar.
Before moving on with the tutorial, complete each of these tasks:
.
