ROOT (Dimension, Index)

Description:

This function returns the indexed root of the specified dimension hierarchy. This function is designed specifically to allow you to view the root level member under a specific dimension with a hierarchical structure. The function displays the name of the member at the specified index under the specified dimension.  Thee function takes as a parameter the name of the dimension which has the hierarchy with member names that are to be accessed, and the index of the root for the member whose name you wish to access from the hierarchy.

Syntax: ROOT (dimension, index)

• Dimension : Enter the name of the dimension which exists in this database mentioned above and is the dimension whose members you wish to access.
• Index : Enter the index number of the root that exists under the dimension specified above, and corresponds to a member, the name of which will be the result of the formula.

Remarks

• The name of the dimension must be a valid name and should exist in the database.
• The index number for the root must be a valid number corresponding with a specific member in the dimension and should not be a number that is smaller or greater than a valid number for the members in that dimension.
• The value for parameter "index" must be a positive real integer.
• Both arguments are compulsory.

Example

=ROOT ("Region", "2)	-returns the 2nd root level member from the dimension Region ( the Member returned is : "North America")
=ROOT ("Version", 1)	-returns the 2nd root level member from the dimension Region ( the Member returned is : "All Versions")
=ROOT ("Time", 3)	-returns the 3rd root level member from the dimension Time ( the Member returned is : "Quarter_1")

 

Let us take the example of a cube in PowerOLAP which requires data that is the value for the name of root member which is accessed through its index number and the dimension name which has the hierarchy, both of which are to be given as a parameter to the function. If in the same dimension in another member or in another dimension in another cube, you want to display this accessed ROOT member value for the index supplied to the function then it is appropriate to use the ROOT function to access the member name at a given root index. In this case we can write a formula in the cube in PowerOLAP to access this value for an index number with the help of this function and write this value 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 a formula for the ROOT function. The formula writes a value into the "MetaDataValues" dimension, into the "ROOT" member. In the formula, the ROOT function takes two parameters, which are, the name of the dimension whose members are accessed, and the index number for the root member whose name has to be returned as the result of the function. This formula accesses the root level member based upon the other two parameters that are given and then depending upon the index of the root level member the resultant value is returned by the function. The function above accesses the members from the "Time (Periods)" dimension, and from that dimension it accesses the 1^st Root which is "All Periods" and this is the value returned.