Entering Data Through an Excel Worksheet

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:

4.   Return to the PowerOLAP Slice and press F9.
Note that the Slice has been completely updated.

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:

Message prompt when writing to an aggregate level cell

Message prompt when writing to a cell with PowerOLAP^® formula