This function returns the nth child of the specified
member in the specified dimension in a specified database.
This function is designed specifically to allow you
to view the child level members under a specific aggregate. The function
displays the name of the member at the specified index under the specified
parent member. The function takes as a parameter the name of the database,
the name of the dimension which has the child level member, user or
the group as created whose security privileges you wish to check.
The function has the following syntax:-
Syntax: OLAPChild
(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 which
is an aggregate level member and will contain one or more child
level members whose name you wish to access / enter the cell reference
which has such a member as its value.
- N : The index number of the child that exists
under the member above, the name of which will be the result of
the formula.
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 member referenced in the "Member"
parameter should be a parent (aggregate) member having one or
more than one child.
- 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 OLAPChild 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 the member name
(if it exists on the sheet) and type in the index for the child
as per the result that you require. If you don't want to manually
enter the member you can use another function called OLAPMember
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 parent member which is referenced in B8 and 1 is
the index of the child, the name of which we want displayed.
OLAPChild($B$1,
$B$5, $B$8, 1) |
The above formula returns a value = "Opening_Balance"
which signifies that this is the child member in the index 1, in the
database "Acuity" (B1), in the dimension Time (Periods)
(B5) , for the parent member Qtr_1 (B8).