Description:
The OLAPWriteTable Creates a table of values from the cube based upon the parameters k1 to kN.
The OLAPWriteTable is the enhanced version of the OLAPTable function in that it can act as a regular OLAPTable but it can also be used to create a formula in Excel which will actually recalculate or change the particular data point in PowerOLAP but it will still retain the formula in the corresponding cell in Excel. The OLAPWriteTable consists of four modes: (1) Display Cube Values, (2) Use Excel formulas, (3) Commit formula Values to Cube and (4) Clear formulas and display cube values.
When in Display Cube Values Mode, the OLAPWriteTable functions similarly when using OLAPTable function. It displays the cube values in a tabular form into your worksheet and allows writeback capability.
The Use Excel Formulas Mode is somehow identical to an OLAPTable function, as well, except that it provides the users the capability to create an excel formula in a particular cell and the calculated value will be written back to the PowerOLAP database. The corresponding cells will still be able to retain the formulas that have been specified even after recalculation or refreshing the Excel.
The Commit Formula Values to Cube Mode is used when committing the results of formula calculations in excel to the corresponding database, specifically in instances when you have copied a certain formula to several cells.
The Clear Formulas and Display Cube Values Mode is used when you wish to remove the formulas in your worksheet and return to the Display Cube Values mode.
To switch modes indicate 1, 2, 3 or 4 as the last parameter of the OLAPWriteTable function. Specify 1 to set the function to Display Cube Values Mode, specify 2 to set the function to Use Excel Formulas Mode, specify 3 to set the function to Commit Formula Values to Cube Mode and 4 to set the function to Clear Formulas and Display Cube Values Mode.
Another method of switching between modes is by double clicking on the OLAPWriteTable funtion and selecting from the preferred modes in the OLAPWriteTable Mode dialog that comes up.
The function has the following syntax:
Syntax: OLAPTable (database, cube, k1, k2, …,kN, ModeNumber)
Explained below are the parameters that have to be provided when building this function.
Remarks
Example
This function may be easier to understand with an example.
=OLAPWriteTable($B$1,$B$2,$C$3,B8:E8,$C$4,A9:A25,$B$9,2) |
The above formula returns a report generated for the parameters defined, which signifies/details the values in the database "UsingPO" (B1), for the cube "Future Year Model", and these values are pertaining to the "Actual" Version, (C3), for the "USA" Region (C4) and the values along the rows of the report are the members of the "Months" dimension(A9:A25), while the values along the columns are the members from "Accounts" dimension comprised of the members Sales, Cost of Sales, Gross Profit and a newly added member called Gross Margin Ratio(B8:E8), 2 is the mode type used which indicates that it is currently in the "Use Excel Formulas" Mode".
Let's say for instance you want to compute for the Gross Margin Ratio but you do not know how to create cube formulas in PowerOLAP. You can actually create a simple formula that will calculate for that value right in your Excel worksheet. As in the example below, a formula was created to compute for the Gross Margin Ratio for the month of January by dividing Gross Profit of January(D9) by Sales of January(B9).
When you hit enter you should get the calculated value of the formula. Once you recalculate, this value will be written back to the corresponding data point in your PowerOLAP database. Notice that when you select the calculated cell (E9) even though it shows up the result or calculated value in that cell, the formula bar still identifies what formula was used to derive that value. Aside from that, the cell is marked with a cell/comment indicator.
You can then copy the formula used for cell E9 to the cells E10 to E20. Notice that after recalculation the affected cells are all marked with a comment indicator and clicking on each of the cells E9 to E20 displays in the formula bar the formula governing the cells. However, at this point, the calculated values are not yet written back to the database. (Notice that cells E18 to E20 returned a #DIV/0! error due to the reason that Sales Amount and Gross Profit are null or empty)
To commit the calculated values to the database, double click on the cell containing the OLAPWriteTable function and select 'Commit Formula Values to Cube' Mode or change the last parameter to "3".
Now when you go back and recalculate the corresponding database. You will see the calculated values coming in.
Now, if you want to remove the formulas from the Excel worksheet and go back to the Display Cube Values Mode, double click on the cell containing the OLAPWriteTable Mode and select 'Clear Formulas and Display Cube Values' from the options or simply change the last parameter of the OLAPWriteTable function to "4".
Notice that the cell/comment indicators in the worksheet for cells E9 to E20 are removed. Clicking on the affected cells will show that the formulas were indeed removed and all that was retained are the numbers or the calculated results.
Clicking into the OLAPWriteTable function will show that the mode changed and was automatically set to use 'Display Cube Values' mode (Note that the Mode number in the OLAPWriteTable function is now set to 1).