xpna.MEMBER FUNCTION

Usage

The MEMBER function is one of the two core functions in XPNA. It is used to retrieve a member and its attributes from the database. It will return a single cell as a rich entity card.

The relation argument allows you to retrieve a related member.

For example, if you want to retrieve the parent of a the June 2023 member, you would use the following syntax:

=xpna.MEMBER("Period", "202306", "parent")

This function would return the member for FY 2023

The ordinal argument allows you to retrieve a specific related member.

For example, if you want to retrieve the previous sibling of the June 2023 member, you would use the following syntax:

=xpna.MEMBER("Period", "FY 2023", "sibling", -1)

This function would return the member for May 2023

The returned MEMBER cell will contain a number of attributes and related MEMBERS which can be referenced using Excel's dot notation.

This is a context-aware function, so when selected, the xpna task pane will show actions for Choose a a different member, Drill down (direction) and Collapse.

Syntax

Argument	Description
hierarchyOrMember	The name of the hierarchy this member belongs to, or another xpna.MEMBER to reference to get the Hierarchy and Code.
memberCode	The code for this member. Note: Leave empty or blank if using a xpna.MEMBER reference in the hierarchyOrMember argument
[relation]	The member's relationship to the member code. Valid options are "child", "children", "leaf", "leaves", "parent", "sibling", "siblings". (optional)
[ordinal]	An integer indicating the number of members to traverse from the member specified in memberCode via the specified relationship. Relations that support ordinal are "child", "leaf", "sibling" (optional) Note: Attribute hierarchies do not support ordinal based relations as they have no set sort order

Note:

The ordinal argument supports a (+/-) integer for the "sibling" relation as it indicates the movement away from the specified member.

An example is if the current member is a month, e.g. March. Specifying -1 will return February and +1 will return April.

Relations

Relation	Description
child	Using the provided ordinal, retrieves the child of the current member at that position as designated by the order of members as seen in Hierarchy Management
children	Retrieves all of the children (direct descendants) of the current member, returned as an Excel array in the order as designated by the order of members as seen in Hierarchy Management
leaf	Using the provided ordinal, retrieves the leaf (lowest level descendant) of the current member at that position as designated by the order of members as seen in Hierarchy Management
leaves	Retrieves all of the leaves (lowest level descendants) of the current member, returned as an Excel array in the order as designated by the order of members as seen in Hierarchy Management
parent	Retrieves the parent of the current member
sibling	Using the provided ordinal, retrieves the member from all the members that share the same direct partent member at that position as designated by the order as designated by the order of members as seen in Hierarchy Management
siblings	Retrieves all the members at the same logical level sa the current member, returned as an Excel array in the order as designated by the order of members as seen in Hierarchy Management

Relations for Attribute hierarchies

Relation	Description
child	Unsupported for attribute hierarchies as they have no sort order
children	Retrieves all of the children (direct descendants) of the current attribute. If the current attribute is the All, it will retrieve all the attributes. If the current attribute not the All, it will retrieve all the real members that are assigned to this attribute. Returned as an Excel array in alphabetic order
leaf	Unsupported for attribute hierarchies as they have no sort order
leaves	Retrieves all of the leaves (real members) of the current attribute. If the current attribute is the All, it will retrieve all the real members assigned to all the attributes. If the current member is not the All, it will return those real members assigned to the specific attribute. Returned as an Excel array in alphabetic order
parent	Returns the All member when given an attribute as attribute hierarchies are a single level. Is invalid if passed the All
sibling	Unsupported for attribute hierarchies as they have no sort order
siblings	Retrieves all the attributes at the same logical level as the current attribute. Returned as an Excel array in alphabetic order

Common problems

• The function will return an error if you do not provide both a hierarchy and member code argument.
• The hierarchy must be a valid hierarchy in your workspace.
• The membercode must be a valid member code in the chosen hierarchy.
• Relations of "child", "leaf" and "sibling" require an integer ordinal argument to be provided. Child ordinals must be positive, sibling ordinals may be positive or negative.

See also

Glossary