Paste and Drill Through Functions
The Paste button allows you to paste a copied data value to another cell or a range of cells in a sliced OLAPTable, OLAPPivot or OLAPWriteTable worksheet. These types of PowerOLAP sliced worksheets require use of this special button to paste values because of the data entry qualities from Excel back to the PowerOLAP database. The Paste function, however, cannot be used in an OLAPReadWrite worksheet because of the cell reference formulas governing the cells.
1. From the PowerOLAP application, choose the OLAPTable option as the Excel function via the Options command then create an excel worksheet out of a PowerOLAP slice (e.g., Sales Acount by Month) by pressing F8.
2. Click on a cell containing Fact Data,
e.g., on the July, Quantity value you entered previously, 1000.
3. Copy this value as you normally would in Excel, by using Excel’s Copy button, , or by pressing Ctrl-C.
4. Choose a destination cell for this copied value and highlight it,
e.g., to the cell containing May, Quantity data.
5. From the Excel ribbon, go to the Add-Ins tab and click the Paste button to put the value into the chosen cell.
6. Press F9 to recalculate. The July Quantity value is successfully copied to May.
The Drill-Through button allows you to see the “source” of an Aggregate data value, shown as list of Detail Member values in a new worksheet within the same workbook. (Note: you can even “drill-through” into the relational source of data if you are using OLAP Exchange® component—see the OLAP Exchange® section).
1. Click on an Aggregate data value, such as Total Year, Quantity, I.e., cell B21.
2. From the Excel ribbon, go to the Add-Ins tab and click the Drill-Through button. The following pop-up window appears:
- Click on the corresponding aggregate cell in Excel, go to the Add-Ins tab of the Excel ribbon, click the PowerOLAP drop down menu and select Drill Through option.
3. Click OK.
You can now save and name the Excel worksheet as you normally would (as an XLSX file), and this worksheet will remain connected to the PowerOLAP database.