This function returns the nth parent of the specified
member in the specified dimension.
This function is designed specifically to allow you
to view the parent member for any specified member in a dimension.
The function displays the name of the parent member that is the parent
for a member at the specified index in the dimension. 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 member
for which the parent level members are being tracked, and the index
of the member.
The function has the following syntax:
Syntax: OLAPParent
(database, dimension, member, 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 and alias group that you want to access / enter
the cell reference which has the name of this Database.
- Dimension : Enter the name of the dimension
which exists in this database mentioned above and is the dimension
whose members you wish to access /enter the cell reference which
has the name of such a dimension.
- Member : Enter the name of the member for which
you want to access the parent level member / enter the cell reference
which has such a member as its value.
- N : The index number of the member that is
the value for the member parameter above, and the parent member
for which will be the result of the function.
Remarks
- If a connection is not established with a database
the result will be a #value.
- The dimension and in it the member name, must
exist within this database and it should be referenced by the
accurate and exact name.
- The value for parameter "n" must
be a positive real integer and should correspond to the value
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 OLAPParent function from the list
of functions provided and click "Ok". a enter the cell
range for the database name, the dimension name, and the member
name.
- When building the function enter the database
name, dimension name, and the member name, also type in the index
for the member, depending upon the parent value that you require
from the result. If you don't want to manually enter the dimension
name and the member name you can use another function called OLAPDimension
and OLAPMember respectively in order to get this value and then
use that cell as a reference for a formula.
- In the following example B1 is the cell that
holds the database name, B5 is the name of the dimension that
contains the member which is referenced in B8 and 1 is the index
for the member, through which the function determines the parent
name that it has to display as the result.
=OLAPParent
($B$1, $B$5, $B$8,1) |
The above formula returns a value = "All_Periods"
which signifies that this is the parent member as per the index 1
for the member Qtr_1 (B8), which belongs to the dimension Time (Periods)
(B5) from the database "Acuity" (B1).