This function returns the nth subset of the specified
dimension in the currently open database.
The OLAPSubset function is designed specifically to
allow you to view the names of the subsets that correspond with an
index number specified for the subset in a dimension within a specified
database. This function returns the name of the subset within the
dimension of a database.
The function has the following syntax:
Syntax: OLAPSubset
(database, dimension, n)
Explained below are the 3 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.
- Dimension : Enter the name of the dimension
in the above mentioned database whose subset names
you want to access / enter the cell reference which has the name
of such a dimension.
- N : Enter the index number corresponding to
the required subset name in the above mentioned dimension of the
specified database / enter the cell reference which has this index
as a value.
Remarks
- If a connection is not established with a database
the result will be a #value.
- The name of the dimension must be a valid name,
referenced by its accurate name that exists in the database to
which a connection is made.
- The value for parameter "n" must
be a positive real integer and should correspond to the value
for the subset name that the function has to return.
- 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 OLAPSubset function from the list
of functions provided and click "Ok".
- When building the formula enter the cell range
for the database name, the dimension name and type in the index
number for the subset in that dimension.If you don't want to manually
type in the dimension name , use the OLAPDimension function and
access the dimension name through this function and reference
the cell with this value when building this function.
- In the following example B1 is the cell that
holds the database name, B5 is the cell that holds the dimension
name, and the number 1 is the index of the subset and the subset
name corresponding to this index will be returned by the formula.
=OLAPSubset
($B$1, $B$5, 2) |
The above formula returns a value = " TimeSubset"
which is the value for the name of the subset at the index 1(n) in
the database "Acuity" (B1) in the dimension "Time (Periods)
" (B5).