The four PowerOLAP's bi-directional reference formulas—OLAPReadWrite, OLAPTable, OLAPPivot and OLAPWriteTable—enable you to enter data into your PowerOLAP database via Excel. By entering data into a worksheet dynamically connected to PowerOLAP®, you work as easily as you do entering data through a Slice.
Note: For the succeeding exercise, again create a slice using the Avon Trading Company.olp
To enter data into a PowerOLAP® – Excel connected worksheet:
1. Click on a cell where you
want to enter or edit data,
at the intersection of July and Quantity.
2. Type <value>, e.g., type 1000, in cell B15.
3. Press Enter.
(If you have Excel's recalculation mode set to Automatic, your worksheet
will automatically update the cell and any affected cells to reflect the
new value. If Excel's recalculation mode is set to manual, press F9
to recalculate the worksheet.)
This figure shows the updated Excel example worksheet:
.png)
4. Return to the PowerOLAP Slice and
press F9.
Note that the Slice has been completely updated.
.png)
Note that all cells in the PowerOLAP grid (July, which is circled, and all other cells that include July values) have been updated according to the value you entered into the Excel worksheet.
Notes
Only values at the intersection of Detail members can be changed in the
Excel worksheet (same as applies in a Slice). Aggregate members' values
and Detail members affected by Cube formulas' values cannot be edited.
The following message box will be displayed when trying to write data to an aggregate level cell or to a cell that is governed by a formula:
.png)
Message prompt when writing to an aggregate level cell
.png)
Message prompt when writing to a cell with PowerOLAP® formula
Please see the following sub-topics: