This function takes several parameters that allow the user to configure ranges of data in their Excel spreadsheet. The following defines the parameters required by the OLAPPivot function:
• OLAPPivot
- (database, cube, Range, dim1Members,, constrainZeroRows, constrainZeroColumns,
rowConstraint, colConstraint, DeleteRows, DeleteColumns, dimNMembers)
• DatabaseName
- The first parameter is the database name. This is the name of the currently
open database. It is the same value that all functions take as their first
parameter.
• CubeName
- The second parameter is the name of the Cube. The Cube maintains a list
of Dimensions and all the Fact Data. The number of additional parameters
to this function depends on the number of Dimensions in the selected Cube.
• Range
- This parameter specifies the initial rows and columns to populate with
the values returned from the function. If this Range is larger than the
number of valid rows and columns returned from the function, the rows
at the bottom will be left blank. We never want to delete rows or columns
as it may affect other data entered by a user. If this range is not big
enough to handle the returned values, additional rows or columns will
be inserted and this parameter in the function will be updated to reflect
the new range.
• ConstrainZeroRows
- This is a numeric parameter that, if 1, suppresses rows with zero values.
("0" would show all rows.)
• ConstrainZeroColumns
- This is a numeric parameter that, if 1, suppresses columns with zero
values. ("0" would show all columns.)
• 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 numeric parameter that, if 1, 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 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:
[1] A Member or list of Members. (see OLAPTable function)
[2] The string ".All". This will return all the Members in the current Dimension in the order defined by the Edit dimension dialog box.
[3] The string ".Children:MemberName". This will return all the children of the Member specified by MemberName.
[4] The string ".Subset:SubsetName". This will return all the values in the Dimension subset named SubsetName.
[5] The string ".Level:x" where x is the requested level. This will return all Members at a specified level.
[6] The string ".Details" which will return a list of all Detail members.
[7] The string ".Aggregates" which will return a list of all Aggregate members.