This function builds a slice over the specified range
for this cube. The function takes as a parameter the name of the database,
the name of the cube within this database, the name of the range over
which the function would be built, zero rows and zero columns, constraints
on rows and columns and deleting rows and columns, and finally the
members that are to be accessed and the list of one or more of the
members. The report that is built over the specified range keeping
in mind the specified parameters as given to the function is the result
that this function returns.
The function has the following syntax:
Syntax: OLAPPivot
(database, cube, range, zeroRows, zeroColumns, rowConstraint, colConstraint,
deleteRows, deleteCols, 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 cubes and the dimensions 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.
- Range : Enter the range in the Excel spread
sheet upon which you want to build the report with OLAPPivot /
alternatively ,select the range automatically through the function
over which the report has to be built.
- ZeroRows : This is a Boolean parameter that,
if true, suppresses rows with zero values.
- ZeroColumns : This is a Boolean parameter that,
if true, suppresses columns with zero values.
- RowConstraint : This is a string that must
match the syntax of our constraints syntax in PowerOLAP. This
parameter determines the rows that will be displayed in Excel.
- ColumnConstraint : This is a string that must
match the syntax of our constraints syntax in PowerOLAP. This
parameter determines the columns that will be displayed in Excel.
- DeleteRows : This is a Boolean parameter that,
if true, Deletes extra rows. Be careful when specifying a true
value for this function as it can delete data unexpectedly.
- DeleteColumns : This is a Boolean parameter
that, if true, Deletes extra columns. Be careful when specifying
a true value for this function as it can delete data unexpectedly.
- Members : The members for each dimension must
be specified in the order of the dimensions in the cube. This
is the same way the OLAPRead, OLAPWrite, OLAPReadWrite and OLAPTable
functions work. We support additional criteria, however, for this
function. To display Members along the row or column axis, you
must specify the Rows or Columns keyword before the member specifier.
You can specify nesting by using a subscript identifier with the
Rows or Columns identifier. The Member parameter can consist of
any of the following:
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 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 OLAPPivot 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 column members reside. B9 specifies the first
cell range, from where the report should start.
=OLAPPivot($B$1,
$B$2, A8:G26, 0, 0, "", "", 0, 0, $C$3,
$C$4, C$5, $C6) |
The above formula returns a report within Excel
generated for the parameters and the intersection points thus defined,
which detail the values in the database "Acuity" (B1), for
the cube "AP_Trans" (B2), and these values are for the page
level dimensions which are:
1. "AP_Trans_Measures"
referenced by the member "Amount"(C3)
2. "Version" referenced
by the member "Actual" (C4).
The dimension on the rows is "AP_Vendor"
referenced by "ALL" (C6) member while the dimension on the
columns is "Time (Periods)" referenced by the member list
in C5.