This function writes a value to a point in the specified
cube.
This function is designed specifically to allow you
to write back to a detail level point in a cube, based on specified
parameters that are provided to the function to help it determine
the exact intersection where to write back the value. The function
returns back to excel the same value that is written to the database.
The function takes as a parameter the name of the
database, the name of the cube, the names of the members for each
dimension within the cube to ascertain a specific point to write back
to and finally the value that has to be written to that point.
The function has the following syntax:
Syntax: OLAPWrite
(database, cube, value/member1, value/member2, …)
Explained below are the parameters that have to be
provided when building this function.
- Database : Enter the name of the database 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.
- Member1: Enter the name of the member from
which a relationship will be established to determine to point
where the value has to write back to / 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 cube name and the member names must exist
within this database and it should be referenced by the accurate
and exact name.
- All 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 OLAPOpen 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 OLAPWrite 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 the member names to determine
the exact point of write back. If you don't want to manually enter
the member names you can use another function OLAPMember in order
to get this value and then use that cell as a reference for a
formula.
- In the following example B1 is the cell that
holds the database name; B2 is the name of the cube within that
database; while C3, C4, "Period_01" and A18 are referenced
for members in the cube's dimensions in order for the function
to ascertain the correct intersection point and the last value
which is a number is the value that is written back to the cube.
The function determined the exact intersection where it needs
to write a value to, and then if it successfully writes that value
to the cube then the same value is returned by the function.
=OLAPWrite
($B$1, $B$2, $C$3, $C$4, "Period_01", $A$18, 900) |
The above formula returns a value = "900"
which signifies that this is the value that is written successfully
to a point which is an intersection of the members "Amount"
(C3), "Actual" (C4), "Period_01" and "BlanchettBlanchett
Gifts Unlimited" (A18) , and this point falls within the cube"AP_Trans"
(B2) in the database "Acuity" (B1). Since the value has
been successfully written to the point therefore the function returns
the value that is written.
