MEMBERGET

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

See Dual output of hyperblocks.

Syntax

MEMBERGET("alias","cube","hierarchy","element","param1","param2")
Note: The third argument of the function is Hierarchy, not Dimension. This is significant when you are using CPM 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.

See MEMBERGET 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.

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)

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].[2002]</MemberUName>
    </Member>
    <Member>
      <MemberUName>[period].[all years].[2003]</MemberUName>
    </Member>
  </StructureSelection>
</MultiSelect>
You create this MEMBERGET formula: 
=MEMBERGET( "Best Practices OLAP", "Analysis", "[Period]", reportvariables.rv_multi.text, "child",2)

The MEMBERGET formula returns: 

<?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].[2002].[2002_Q3]</MemberUName>
    </Member>
    <Member>
      <MemberUName>[Period].[All Years].[2003].[2003_Q3]</MemberUName>
    </Member>
  </StructureSelection>
</MultiSelect>
Note: The cell which 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.

See these topics:

MULTISELECTGETMEMBERS

MULTISELECTGETMEMBERSSIMPLE

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

See Specifying number formats.

These are other formulas that support multiselect:

Parent topic: Formulas