ROOTCOUNT (dimension)

Description:

This function returns the count of root level members in the hierarchy of the specified dimension.  This function is designed specifically to allow you to count number of root level in a specified cube that you wish to access from a specified database. The function takes as no parameter if the current cubes dimension are to be counted, however if the dimensions of another cube are to be counted then the name of that specific cube from within the database can be referenced in the function and the function will display the count of dimensions in the specified cube.

Syntax: ROOTCOUNT (dimension, member)

• Dimension : Enter the name of the dimension which exists in this database and is the dimension whose root level members you wish to access and count.

Remarks

• The dimension name must exist within this database and it should be referenced by the accurate and exact name.
• The dimension referenced above, should ideally have a hierarchical structure defined which will allow the function to determine the root level members.
• This argument is compulsory and must be supplied to the function.

Example

=ROOTCOUNT ("Time")	-returns the number of ROOT level members under the dimension name, specified herein (3)
=ROOTCOUNT ("Version")	-returns the number of ROOT level members under the dimension name, specified herein (2)
=ROOTCOUNT ("Region")	-returns the number of ROOT level members under the dimension name, specified herein(4).

 

Let us take the example of a cube in PowerOLAP which requires data that is the value for the count of ROOT level members in a dimension, accessed through the dimension name, which has to be given as a parameter for the function. If in the same dimension in another member or in another dimension in another cube, you want to display this accessed ROOTCOUNT value for the current cube for the dimension supplied as a parameter to the function then it is appropriate to use the ROOTCOUNT function to access such a member count. In this case we can write a formula in the cube in PowerOLAP to access this value for the count of ROOT LEVEL members in a dimension. With the help of this function the number can be determined and written to another member that is supposed to hold this value as fact data or you can also write this value to a different dimension in a different cube.



The screen above shows the formula for the ROOTCOUNT function. The formulas write values into the "MetaDataValues" dimension, to the "RootCount" member. In the formula, the ROOTCOUNT function takes one parameter, which is, the name of the dimension whose root level members are being counted. The Root member count is automatically accessed from the current cube in which the formula is written. The count of root level members from the specified dimension is returned as the result of the function. In the example above, the function returns the count of the Root level members which belong to the dimension named "Time (Periods)". This formula returns a count totaling "3".