This function creates a table of values from the cube
based upon the parameters k1 to k'n; also enables writing data from
the worksheet to in the database.
The function takes as a parameter the name of the
database, the name of the dimension that has the members that are
to be accessed, the name of the parent member for which child level
members the weight will be tracked and the name of the child level
member, whose weight is the result returned by the function.
The function has the following syntax:
Syntax: OLAPTable
(database, cube, k1, k2, …,k'n)
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 to 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 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 the 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 OLAPTable 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 how to create the report and based on what
parameters that would form the page level dimensions as well the
parameters that would form the members along the rows and the
columns. 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
b8:g8 specifies the range that form the members that have to be
included in the columns, while a9:a26 is the range within which
the row members reside. B9 specifies the first cell range, from
where the report should start.
=OLAPTable
($B$1, $B$2, $C$3, $C$4, B8:G8, A9:A26, $B$9) |
The above formula returns a report generated
for the parameters defined, which signifies details the values in
the database "Acuity" (B1), for the cube "AP_Trans",
and these values are pertaining to the "Actual" Version,
(C4), and the measure is "Amount" (C3) and the
values along the rows of the report are the names of vendors from
the "AP_Vendor" dimension(A9:A26), while the values along
the columns are the members from "Time"(Periods) dimension.(B8:G8).
