LEVEL (dimension, member)

Description:

This function returns the level of a member. Root level members have level 0 and the level for the leaf level member increases with each generation of children.

Syntax: LEVEL (dimension, member)

• Dimension : is the name of the dimension from which you want to access members whose level you want to ascertain. The dimension may belong to a cube or it may independently reside in the database. Its members are all accessed by a reference.
• Member : is the member of the dimension whose level number is what you want the function to access. Members in a dimension can be at different levels depending upon the hierarchy structure of the dimension and depending upon at what level the member exists in that dimension.

Remarks

• Both arguments are compulsory.
• If you specify a number instead of a text value or a text value as a number for any of the parameter then the function would return an error which is-#VALUE.

Example

The LEVEL function can be used in any database where you want to access the level of the given member name from a given dimension and this level value is either used as a reference in an outer formula or it is used to populate a value into another member.

This function returns the level for the member specified in the dimension it belongs and this level is returned as the result of the function.

=LEVEL ("Period_1", "Time")	- returns the level of the specified member in the specified dimension named "Time" and the currently specified member is at the lowest level in the hierarchy, and the function returns: 2.
=LEVEL ("Version", "Current Budget")	- returns the level of the specified member "Current Budget" in the specified dimension "Version", the member is at the level: 2.

Let us take the example of a database in PowerOLAP where we have dimensions which member names are to be accessed in order to find the levels for those members based on the hierarchy of a specified dimension. The level so acquired can be used for referencing in another formula or they are to be accessed to get them as a value and assign that value as fact data to another member. We shall use this example in this case to populate a value into a cube that uses the level of a member from a specified dimension as a fact value to populate its own members within its own dimension and the value to be populated is accessed through the "LEVEL" function.

A number value is returned as the result for the LEVEL function.



The formula above writes values into the "MetaDataValues" dimension into the member named "LEVEL" by creating the appropriate value for this member, calculating the level for the member based upon the parameters that are given as input to this function, namely the dimension name "Time (Periods)" and the name of the member to be accessed which is referenced here as "Period_01". The function acquires the specified member from the specified dimension and then it reads the hierarchy from the dimension and accordingly determines the level for the member that is referenced in the formula. In the above example, the level for the member "Period_01" is determined by reading the hierarchy for its dimension and the resultant level (2) is the number returned as the result of the function.