OLAPIsChildOf

Description:

This function returns true if the specified child belongs to the specified parent in the specified dimension.

This function is designed specifically to allow you to check if there exists a valid parent -child relationship between two members. The result of the function is displayed as a Boolean value. If the child member that is specified in the function, is the child of the parent member also specified in the function then the function returns "True" or else if there is the member is not the child of the member specified as the parent, or if there is no relationship whatsoever between the two members, then the function returns the value which equals "False".

The function takes as a parameter the name of the database with the required dimension, the name of the dimension that has the child and parent level members, the name of the parent member with what the child member is compared with, and the last parameter is the name of the child member that is being checked for belonging to the specified parent or not.

The function has the following syntax:

Syntax: OLAPIsChildOf (database, dimension, parent, child )

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 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 within which you want to check the relationship between 2 members / alternatively enter the cell reference which has the name of such a dimension.
• Parent : Enter the name of a parent level member for which you want to ensure the relationship with one or more child level members whose name you specify as the next parameter / enter the cell reference which has such a member as its value.
• Child : Enter the name of a child level member for which you want to ensure the relationship with a parent level members whose name has been specified as the parameter mentioned above / enter the cell reference which has such a member as its value.

Remarks

• If a connection is not established with a database the result will be a #value.
• The dimension and in it the member names for both child and parent, must exist within this database and they should be referenced by the accurate and exact name.
• The member referenced in the "Parent" parameter is supposed to be a parent (aggregate) member having one or more than one child.
• The member referenced in the "Child" parameter is supposed to be a child level (detail) member.
• 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 OLAPIsChildOf 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 parent level member name (if it exists on the sheet) as well as the child level member name. 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. For the dimension too, if it doesn't already exist on your sheet and you don't want to manually type it in the function, use the OLAPDimension function to access the dimension name and then refer that cell in this function.
• In the following example B1 is the cell that holds the database name, B5 is the name of the dimension that contains the parent and the child level members. The parent member is referenced through B8 and the child level member is referenced through "Period_1".

=OLAPIsChildOf ($B$1, $B$5, $B$8,"Period_01")

 

The above formula returns a value = "True" which signifies that "Period_01" which is specified as the child parameter, is indeed the child member under the parent "Qtr_1" signified through B8 which exists in the dimension Time (Periods) (B5) in the database "Acuity" (B1).