This function returns a value from the cube, based
on the parameters from k1 to kN. The cell where the value is returned
is a read / write cell, which implies that a value can also be written
back to the database from that cell. The function takes as a parameter
the name of the database, the name of the cube within this database,
and finally the members that are to be accessed in order to determine
the exact intersection where the value resides which has to be read
from and written to. These members supplied as a parameter to the
function can be one or a list of members depending upon the dimensional
structure of the cube.
The value that the function reads from a point in
the cube is the value that is returned and this value is accessed
keeping in mind the parameters given to the function. This value is
the result that this function returns.
The function has the following syntax:
Syntax: OLAPReadWrite
(database, cube, member1,member2,…)
Explained below are the parameters that have to be
provided when building this function.
- Database : Enter the name of the database which
has the dimension that you want to access / enter the cell reference
which has the name of this Database.
- Cube : Enter the name of the cube which exists
in this database mentioned above that you wish to access /enter
the cell reference which has the name of such a dimension.
- Members (k1, k2, …k'n)
: Enter the name of the member from which a relationship will
be established to determine the point where the values have to
be read from in order to build the report / enter the cell reference
which has such a member as its value.
Remarks
- If a connection is not established with a database
the result will be a #value.
- The database name, the cube name and the member
names must be referenced by their exact name and should exist
as such within the database.
- The member names should be chosen as such,
that they allow the function to determine the exact intersection
points for which it should create the report.
- All the arguments are compulsory and neither
argument is optional.
Example
This function may be easier to understand with an
example.
- In order to build this function with more ease,
open the specific database and bring a slice from that database
into Excel. In this way you will have connected to PowerOLAP because
you now have the OLAPDatabase formula automatically on your sheet
when you bring a slice of the cube into Excel.
- In Excel, use the menu: Insert -> Function
and select PowerOLAP from the drop down list.
- Choose the OLAPReadWrite function from the
list of functions provided and click "Ok".
- When building the function enter the cell range
for the database name, the cube name, and enter the cell references
for the members, for all dimensions, which are page level members
as well as column and row level members, because this allows the
function to determine the exact intersection point from where
the value has to be read and written to subsequently. Instead
of entering the cell range the values can be manually entered
into the function. Also, the cube name and the member names can
be accessed into a cell through the functions OLAPCube and OLAPMember
respectively and this cell can be referenced within the function.
- In the following example B1 is the cell that
holds the database name, B2 is the name of the cube, C3 and C4
are the page level members, that help determine the intersection
point, and D8 and A12 are the members on the rows and the columns
respectively that pinpoint the exact intersection from where this
function has to pull the values from and after that write back
to that point.
=OLAPReadWrite($B$1,
$B$2, $C$3, $C$4, $D$8, $A$12) |
The above formula returns a value = "121414",
and this value is read from PowerOLAP and its based upon the parameters
supplied to the function that help it determine the intersection point
from where it has to read the value that is to be returned as the
result of the function.
The value above exists in the database "Acuity"
(B1), for the cube "AP_Trans" (B2), and this value exists
at a point which is the intersection of all the members that are referenced
in this example, which is the member "Amount" (C3), the
member "Actual" (C4), the member "Qtr_3" (D8)
and the member "AdvCons_Advantage Consulting" (A12).
These members help determine the exact intersection
point from where this value is read and the same is returned as the
result of the function. This value is read from the PowerOLAP database
and the same value can be changed /edited and written back to the
cube in PowerOLAP as the ReadWrite function allows this functionality.
