MEMBERGET

This function returns the unique name of an element by reference to its relationship with a consolidated element. This function works with ODBO-compliant databases only. To return the caption use the tilde (~) number format.

Syntax

MEMBERGET("data_connection","cube","hierarchy","element_name","param" {,"param"})

Note: The third argument of the function is Hierarchy, not Dimension. This is significant when you are using relational or Analysis Services databases which can have multiple hierarchies in a dimension. You must specify the unique name of the hierarchy.

There has been a temporary change to the way in which MEMBERGET handles invalid or mismatched hierarchy and element parameters.

If the same element name exists in multiple dimensions, the error Dimensionality mismatch can occur. To avoid this, specify the unique name of the element.
Note: If you specify an empty string as the element name then the default element of the specified hierarchy is assumed.

Example

This example uses the Adventure Works database for Analysis Services. The Geography dimension has multiple hierarchies. This example uses the City hierarchy. Its unique name is [Geography].[City]. 

=MEMBERGET("Adventure Works","Adventure Works","[Geography].[City]",
"[Geography].[City].&[Abingdon]&[ENG]","Sibling",2)
This example returns the Alexandria [NSW] element. This is the second sibling (counting from zero) of Abingdon [ENG]. Replace 2 with 1 to return Albany [OR], which is the 1st sibling of Abingdon [ENG].
Possible values for Param1 are:
Child
Returns the (zero based) child of the parent, specified by Param2. Param2 must be a number.
Next
Returns the next element in the hierarchy at the same level as the specified element. Param2 is not required.
Note:  Memberget ("...,"Next") is equivalent to Memberget ("...,"Sibling",1)
Prev
Returns the element in the hierarchy that is before the specified element and at the same level. Param2 is not required.
Sibling
Returns the sibling of the parent, specified by param2. Param2 must be a positive or negative number or zero.
Note:  
Memberget ("...,"sibling",-1) is equivalent to Memberget ("...,"Prev")
Parent
Returns the parent of the specified element. Param2 is not required. 
=MEMBERGET("Adventure Works","Adventure Works","[Geography].[City]",
"[Geography].[City].&[Abingdon]&[ENG]","Parent")
Self and Verify
Self and Verify both return the unique name of the specified element. Param2 is not required. If the element does not exist, the formula returns the default element of the specified hierarchy.
Verify checks that the specified hierarchy exists. If it does not, the formula returns #NA!
Self does not check that the specified hierarchy exists. That is, if the element exists, the formula returns its unique name.
Note:  Memberget ("...,"Self") and Memberget ("...,"Verify") are both equivalent to ("...,"Sibling",0)

Example

The default element of a hierarchy is not necessarily the top-level element. To find which is the default element you can use the self parameter, and an empty string as the element name:

=MEMBERGET("data_connection","cube","hierarchy","","self")

Multiselect

MEMBERGET supports multiselect. That is, you can use MEMBERGET to return the unique names of multiple elements that are stored in MLS-XML.

For example, a report variable called rv_multi contains this MLS-XML: 
<?xml version="1.0" encoding="utf-16"?>
<MultiSelect xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Hierarchy>[Period]</Hierarchy>
  <AggregationFunction>Sum</AggregationFunction>
  <StructureSelection>
    <Member>
      <MemberUName>[period].[all years].[2016]</MemberUName>
    </Member>
    <Member>
      <MemberUName>[period].[all years].[2017]</MemberUName>
    </Member>
  </StructureSelection>
</MultiSelect>
You create this MEMBERGET formula: 
=MEMBERGET( "BestPracticesOLAP", "Analysis", "[Period]", reportvariables.rv_multi.text, "child",2)

The MEMBERGET formula returns this MLS-XML: 

<?xml version="1.0" encoding="utf-16"?>
<MultiSelect xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Hierarchy>[Period]</Hierarchy>
  <AggregationFunction>Sum</AggregationFunction>
  <StructureSelection>
    <Member>
      <MemberUName>[Period].[All Years].[2016].[2016_Q3]</MemberUName>
    </Member>
    <Member>
      <MemberUName>[Period].[All Years].[2017].[2017_Q3]</MemberUName>
    </Member>
  </StructureSelection>
</MultiSelect>
Note: The cell that contains the MEMBERGET formula displays </Multiselect>. Expand the height of the cell to display the full MLS-XML definition.

You could use MULTISELECTGETMEMBERS or MULTISELECTGETMEMBERSSIMPLE to extract the unique names of the elements from the MLS-XML.

Or, to display the captions of the elements in the cell, you could specify the tilde (~) character as the number format of the cell.

Related topics