OLAPRoot

Description:

This function returns the nth root of the specified dimension.

The OLAPRoot function is designed specifically to allow you to view the name of the root level members that correspond with an index number specified for a dimension within a specified database. This function returns the name of the member that is the root member for that dimension.

The function has the following syntax:

Syntax: OLAPRoot (database, dimension, n)

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 that you want to access / enter the cell reference which has the name of this Database.
• Dimension : Enter the name of the dimension in the above mentioned database whose root member name you want to access / enter the cell reference which has the name of such a dimension.
• N : Enter the index number for the root member in the above mentioned dimension of the specified database / enter the cell reference which has this index as a value.

Remarks

• If a connection is not established with a database the result will be a #value.
• The value for parameter "n" must be a positive real integer and should correspond to the value for the root member 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 OLAPRoot 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 type in the index number for the root level member.
• In the following example B1 is the cell that holds the database name, B5 is the cell that holds the dimension name, and the number 1 is the index of the root level member and this member's name of which will be returned by the formula.

=OLAPRoot($B$1, $B$5, 1)

 

The above formula returns a value = " All_Periods " which is the value for the root level member at the index 1(n) in the database "Acuity" (B1) in the dimension "Time (Periods) " (B5).