This function returns the nth member in the specified
subset of the currently open database.
The OLAPSubsetMember function is designed specifically
to allow you to view the names of all the members that make up a subset
created within a dimension in the specified database. This function
returns the name of the subset member within a subset created for
a dimension.
The function has the following syntax:
Syntax: OLAPSubsetMember
(database, dimension, subset, n)
Explained below are the 4 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.
- Subset : Enter the name of the subset created
in the above mentioned dimension, and whose member names you want
to access / enter the cell reference which has the name of such
a subset.
- N : Enter the index number corresponding to
the required subset member name in the above mentioned subset
for a specified dimension / 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 and subset 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 member 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 OLAPSubsetMember 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 , the name of a subset
within this dimension and type in the index number for the member
of this subset. If you don't want to manually type in the dimension
name, or the subset name use the OLAPDimension and the OLAPSubset
function respectively and access the dimension name and subset
name through their respective functions and reference the cell
with the necessary 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, J54 is the cell that holds the subset name and the number
4 is the index of the member in the subset referenced through
J54. The subset member name corresponding to this index will be
returned by the function.
=OLAPSubsetMember
($B$1, $B$5, $J$54, 4) |
The above formula returns a value = " Qtr_4
" which is the value for the name of the subset member at the
index 4 (n) from the subset "TimeSubset" (J54) that resides
in the dimension "Time (Periods) "(B5) in the database "Acuity"
(B1).