OLAPChildCount

Description:

This function returns the number of children of the specified member in the specified dimension in a specified database. This function is designed specifically to allow you to view how many child level members there are under a specific aggregate. The function displays the number of child level member under the specified parent level member in a database. The function takes as a parameter the name of the database , the name of the dimension and the name of the member in that dimension which is an aggregate member and may contain one or more child level members.

The function has the following syntax:-

Syntax: OLAPChildCount (database, dimension, member)

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 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 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 , which count you want to ascertain. / enter the cell reference which has the name of such a member.

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 or else the formula will return a value of "0".
• 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 OLAPChildCount 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) whose children level members you wish to count. 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 aggregate (parent level) member which is referenced in D8 and it is the count of children for this member which should be displayed.

=OLAPChildCount($B$1, $B$5, $D$8)

 

The above formula returns a value = 3 which signifies that in the database "Acuity" (B1), in the dimension Time (Periods) (B5) , for the parent member Qtr_3 (D8) there are children level members to the count of 3.