This function returns the level number of the member
in the dimension.
This function is designed specifically to allow you
to view at what level does a particular member reside in a dimension
in a specified database. It returns to the user an integer value that
signifies the position / level of the member in the hierarchy of a
particular dimension.
The function has the following syntax:
Syntax: OLAPMemberLevel
(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
wherein you want to access the name of the member to track its
level / alternatively enter the cell reference in the Excel sheet
which has the name of such a dimension.
- Member : Enter the name of the member which
exists in the dimension mentioned above and it is the member for
which you want to access the level according to its positioning
in the hierarchy in the dimension / alternatively enter the cell
reference in the Excel sheet which has the name of such a member
as its value.
Remarks
- If a connection is not established with a database
the result will be a #value.
- The dimension must exist within this database
and it should be referenced by the accurate and exact name.
- The member specified in the "Member"
parameter should exist in the dimension that is mentioned above
and again must be referenced by the accurate and exact name.
- These 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 OLAPMemberLevel function from the
list of functions provided and click "Ok".
- When building the formula enter the cell range
for the database name with the required dimension, and the dimension
name which has the member whose name you specify next, and finally
the member name for which you are tracking the level within the
dimension's hierarchy. If you don't want to manually enter the
member name or the dimension name, you can use the functions called
OLAPMember and OLAPDimension respectively in order to get this
value and then use those cells as references for the function.
- In the following example B1 is the cell that
holds the database name, B5 is the name of the dimension from
which the member names will be accessed, and D8 is the cell reference
for the member name ,and this function is going to track the level
within the hierarchy of a dimension for this member and the same
integer value will be returned by the function.
=OLAPMemberLevel($B$1,
$B$5, $D$8) |
The above formula returns a value = "1"
which signifies that in the database "Acuity" (B1), in the
dimension "Time (Periods)" (B5), and for the member "Qtr_3"
the corresponding level number in the dimension's hierarchy for this
member, is level "1", which is the value returned by the
function as displayed above.