Difference between revisions of "XL3MemberNavigate"
(→Examples (based on the Bicycle Sales cube)) |
(→Examples (based on the Bicycle Sales cube)) |
||
| Line 94: | Line 94: | ||
{{Code|1==XL3MemberNavigate( 1, "Time", A1, "FirstMember" )}} | {{Code|1==XL3MemberNavigate( 1, "Time", A1, "FirstMember" )}} | ||
| − | * | + | *2002 |
{{Code|1==XL3MemberNavigate( 1, "Time", A1, "LastMember", 2 )}} | {{Code|1==XL3MemberNavigate( 1, "Time", A1, "LastMember", 2 )}} | ||
| − | * | + | *2004 |
===Member Relationships=== | ===Member Relationships=== | ||
| Line 103: | Line 103: | ||
{{Code|1==XL3MemberNavigate( 1, "Time", A1, "Parent" )}} | {{Code|1==XL3MemberNavigate( 1, "Time", A1, "Parent" )}} | ||
| − | * | + | *2003 |
{{Code|1==XL3MemberNavigate( 1, "Time", A1, "Parent", 2 )}} | {{Code|1==XL3MemberNavigate( 1, "Time", A1, "Parent", 2 )}} | ||
| − | * | + | *All |
{{Code|1==XL3MemberNavigate( 1, "Time", A1, "Next" )}} | {{Code|1==XL3MemberNavigate( 1, "Time", A1, "Next" )}} | ||
| − | * | + | *Q3 |
{{Code|1==XL3MemberNavigate( 1, "Time", A1, "Previous", 2 )}} | {{Code|1==XL3MemberNavigate( 1, "Time", A1, "Previous", 2 )}} | ||
| − | * | + | *Q4 (from 2002) |
{{Code|1==XL3MemberNavigate( 1, "Time", A1, "FirstSibling" )}} | {{Code|1==XL3MemberNavigate( 1, "Time", A1, "FirstSibling" )}} | ||
| − | * | + | *Q1 |
{{Code|1==XL3MemberNavigate( 1, "Time", A1, "LastSibling", 2 )}} | {{Code|1==XL3MemberNavigate( 1, "Time", A1, "LastSibling", 2 )}} | ||
| − | * | + | *Q3 |
{{Code|1==XL3MemberNavigate( 1, "Time", A1, "FirstChild" )}} | {{Code|1==XL3MemberNavigate( 1, "Time", A1, "FirstChild" )}} | ||
| − | * | + | *April 2003 |
{{Code|1==XL3MemberNavigate( 1, "Time", A1, "LastChild", 2 )}} | {{Code|1==XL3MemberNavigate( 1, "Time", A1, "LastChild", 2 )}} | ||
| − | * | + | *May 2003 |
==See Also== | ==See Also== | ||
Revision as of 17:27, 17 March 2011
Returns a member based on its relationship to the member passed in. Allows you to navigate a hierarchy, moving between members and levels using formulae alone.
You can combine formulae, for example to move up a hierarchy, then along the level you get to.
Use the Insert Formula > Member Navigate menu or ribbon item to insert the formula using a wizard.
Contents
[hide]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 as 'x' it gets the xth member of the level. |
| LastMember | Level | By default returns the last member of the specified level. If the Index parameter is passed as 'x' it gets the xth member 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 2, it will retreive the grandparent (parent of the parent), 3 the great-grandparent etc. |
| Next | Member | Gets the next member following the member passed in. If index is greater than one then it skips forwards that number of members. |
| Previous | Member | Gets the previous member preceding the member passed in. If index is greater than one then it skips backwards that number of members. |
| 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 last sibling. |
| 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 last child. |
| 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)
Level Relationships
These examples assume that [Time].[Year] is in cell A1
=XL3MemberNavigate( 1, "Time", A1, "FirstMember" )
- 2002
=XL3MemberNavigate( 1, "Time", A1, "LastMember", 2 )
- 2004
Member Relationships
These examples assume that [Time].[All].&[2003].&[Q2] is in cell A1
=XL3MemberNavigate( 1, "Time", A1, "Parent" )
- 2003
=XL3MemberNavigate( 1, "Time", A1, "Parent", 2 )
- All
=XL3MemberNavigate( 1, "Time", A1, "Next" )
- Q3
=XL3MemberNavigate( 1, "Time", A1, "Previous", 2 )
- Q4 (from 2002)
=XL3MemberNavigate( 1, "Time", A1, "FirstSibling" )
- Q1
=XL3MemberNavigate( 1, "Time", A1, "LastSibling", 2 )
- Q3
=XL3MemberNavigate( 1, "Time", A1, "FirstChild" )
- April 2003
=XL3MemberNavigate( 1, "Time", A1, "LastChild", 2 )
- May 2003
