Jump to: navigation, search
  • Main page
  • Recent changes
  • Random page
  • XL3PropertyLookup

    Returns a member property of a given member.

    Use the Insert Formula > Member Property menu or ribbon item to insert the formula using a wizard. See here for details.

    Syntax

    XL3PropertyLookup( Connection, Hierarchy, Member, Property, [TryConvertType] )

    Parameters

    Parameter Description
    Connection Connection number to use
    Hierarchy Name of the hierarchy that the following member applies to e.g. "Measures" or "[Customer].[Customer Geography]"
    Member Member whose member property should be returned
    Property Property to retrieve
    TryConvertType Try to turn text into a number or date on the web, defaults to true.

    Intrinsic Member Properties

    XL3PropertyLookup supports the following Intrinsic Member Properties:

    Property name Description
    MEMBER_UNIQUE_NAME The unique name of the member. For providers that generate unique names by qualification, each component of this name is delimited
    MEMBER_CAPTION A label or caption associated with the member. It is used primarily for display purposes. If a caption does not exist, MEMBER_NAME is returned
    LEVEL_UNIQUE_NAME Unique name of the level to which the member belongs. For providers that generate unique names by qualification, each component of this name is delimited
    LEVEL_NUMBER The distance of the member from the root of the hierarchy. The root level is zero (0)
    CUBE_NAME The name of the cube to which this member belongs
    CATALOG_NAME The name of the database to which this member belongs
    CHILDREN_CARDINALITY The number of children that the member has. This can be an estimate, so you should not rely on this to be the exact count. Providers should return the best estimate possible
    MEMBER_ORDINAL The ordinal number of the member. This is the sort rank of the member when members of this dimension are sorted in their natural sort order. If providers do not have the concept of natural ordering, this should be the rank when sorted by MEMBER_NAME
    DIMENSION_UNIQUE_NAME The unique name of the dimension to which this member belongs. For providers that generate unique names by qualification, each component of this name is delimited
    HIERARCHY_UNIQUE_NAME The unique name of the hierarchy. If the member belongs to more than one hierarchy, there is one row for each hierarchy to which it belongs. For providers that generate unique names by qualification, each component of this name is delimited
    MEMBER_NAME The name of the member
    PARENT_LEVEL The distance of the member's parent from the root level of the hierarchy. The root level is zero (0)
    PARENT_UNIQUE_NAME The unique name of the member's parent. NULL is returned for any members at the root level
    PARENT_COUNT The number of parents that this member has
    MEMBER_KEY The value of the member's key column. Returns NULL if the member has a composite key
    IS_PLACEHOLDERMEMBER A Boolean that indicates whether a member is a placeholder member for an empty position in a dimension hierarchy
    IS_DATAMEMBER A Boolean that indicates whether the member is a data member. Returns True if the member is a data member
    MEMBER_TYPE The type of the member. It can be one of the following values:
    • MDMEMBER_TYPE_REGULAR (0)
    • MDMEMBER_TYPE_ALL (1)
    • MDMEMBER_TYPE_FORMULA (2)
    • MDMEMBER_TYPE_MEASURE (3)
    • MDMEMBER_TYPE_UNKNOWN (4)

    Examples (based on the Bicycle Sales cube)

    Returns 3, the distance of 'W6000/185' from the root of the hierarchy.

    =XL3PropertyLookup( 1, "[Product]", "[Product].[W6000/185]", "LEVEL_NUMBER" )

    See Also