XL3MemberNavigate

Revision as of 16:57, 17 March 2011 by Colin Overton (talk) (Relationship types)

Returns a member based on its relationship to the member passed in.

Use the Insert Formula > Member Navigate menu or ribbon item to insert the formula using a wizard.

Syntax

XL3MemberNavigate( Connection, Hierarchy, Member or Level, Relationship, [Index] )

Parameters

Parameter Description
Connection Connection number to use
Hierarchy Name of the hierarchy that the member applies to e.g. "Measures" or "[Customer].[Customer Geography]"
Member or Level Members or Level to use as starting position
Relationship Relationship of the desired member to the Member or Level passed in, see below
Index Index of the member in the Relationship, meaning depends on the Relationship

Relationship types

The valid relationship types are as follows, and the meaning of the Index parameter is decribed for each.

Relationship Member or Level Description
FirstMember Level By default returns the first member of the specified level. If the Index parameter is passed it gets that member of the level.
LastMember Level By default returns the last member of the specified level. If the Index parameter is passed it gets the member that distance from the end of the level.
Members Level Equivalent to FirstMember or LastMember. If index is positive it acts like FirstMember, if it is negative it acts like LastMember.
Parent Member Gets the parent of the member passed in. If index is greater than one it gets the ancestor at that distance.
Next Member Gets the next member following the member passed in. If index is greater than one then it skips forwards that distance.
Previous Member Gets the previous member preceding the member passed in. If index is greater than one then it skips backwards that distance.
FirstSibling Member Gets the first member with the same parent as the member passed in. If index is greater than one then it gets the child at that index.
LastSibling Member Gets the last member with the same parent as the member passed in. If index is greater than one then it gets the child at that index from the end of the children.
Siblings Member Equivalent to FirstSibling or LastSibling. If index is positive it acts like FirstSibling, if it is negative it acts like LastSibling.
FirstChild Member Gets the first child of the member passed in. If index is greater than one then it gets the child as that index.
LastChild Member Gets the last child of the member passed in. If index is greater than one then it gets the child at that index from the end of the children.
Children Member Equivalent to FirstChild or LastChild. If index is positive it acts like FirstChild, if it is negative it acts like LastChild.

Examples (based on the Bicycle Sales cube)

Member name 'W6000/185'

=XL3Member( 1, "Product", "[Product].&[W6000/185]" )

Multiple Members

You can specify multiple members for an XL3Member formula by using additional parameters.

When referred to by an XL3Lookup formula, it returns the sum of Allround and Mountain Bikes.

=XL3Member( 1, "Product", "[Product].&[Allround]", "[Product].&[Mountain]" )

XL3Member using MDX

You can specify an MDX calculation for members using the MDX: syntax. These cells can then be referenced by XL3Lookup formulae to use the created calculated members.

The last month in 2005.

=XL3Member( 1, "[Time]", "MDX:[Time].[All].[2005].LastChild" )

Using XL3Member in other Formulae

To allow other formulae to use multiple members for a single hierarchy, it is necessary to use XL3Member to create a calculated member. You can reference the XL3Member directly in the other formula, or reference the cell that the XL3Member is in.

Using XL3Member in-line to perform a multiple member XL3Lookup: the sum of Allround and Road

=XL3Lookup( 1, "[Product]", XL3Member( 1, "[Product]", "[Product].[All].&[Allround]", "[Product].[All].&[Road]" ) )

Sharing an XL3Member between multiple lookups: the members between Q2 2003 and Q1 2004 (inclusive)

  • In A1:
    =XL3Member( 1, "[Time]", "MDX:[Time].[All].&[2003].&[Q2]:[Time].[All].&[2004].&[Q1]" )
  • In A4 (returns the value of the sum):
    =XL3Lookup( 1, "[Time]", A1 )
  • In A5 (returns a chart of products, for that timespan):
    =XL3SparkColumnsM( XL3DataSeriesLookup( 1, XL3MemberSet( 1, "[Product]", "[Product].[All]", "Children" ), "[Measures].[Value]", "[Time]", A1 ) )

See Also