Skip navigation.
spilling the beans

Parent, Children, FirstChild, LastChild, Ancestors, Descendants, DrillDownLevel and TM1DrilldownMember

developers
| |

Children returns the set of members one level below a named parent.
{Product.[Demand Loan].Children}

FirstChild returns the… first child one level below a named parent.
{[Product].[Customer Lending].FirstChild}
Returns “Call Participation Purchased”.

LastChild returns the last child one level below a named parent. This is excellent for finding the last day in a month, since they can vary from 28 to 31. Another example is when a consolidation is set up to track a changing set of members (e.g. “Easter”, or “Strategic Customers”).
{[Product].[Customer Lending].LastChild}
Returns “Term Participation Purchased”.

Parent returns the first parent of a given member. If a member has more than one parent, and the full “unique path” to the member is not specified then the first parent according to the dimension order is returned.
{[Product].[MidasTPIS].Parent}
Returns “Bonds”.
{[Product].[External - Bonds].[MidasTPIS].Parent}
Would force TM1 to return the second parent, “External – Bonds”.

Descendants returns the named parent and all of its descendant children – i.e. the hierarchy down to the leaf level:
{Descendants(Product.[Customer Lending]) }

TM1DrilldownMember returns the same thing as descendants:
{TM1DRILLDOWNMEMBER( {[Product].[Customer Lending]}, ALL, RECURSIVE )}

DrillDownLevel just returns the parent and its immediate children:
{DRILLDOWNLEVEL( {[Product].[Customer Lending]})}

DrillDownLevel can be extended with a parameter to say which level to return the members from, rather than the level immediately below, but this doesn’t appear to work in TM1 v9.0 SP2 through to 9.1.1.36.

The common requirement to return a list of just leaf-level descendants of a given consolidated member just needs a level filter applied to the TM1DrillDownMember example above:
{TM1FILTERBYLEVEL({TM1DRILLDOWNMEMBER({[Product].[Customer Lending]},ALL,RECURSIVE)}, 0)}
Or:
{TM1FILTERBYLEVEL({DESCENDANTS(Product.[Customer Lending]) }, 0)}

Ancestors is like a more powerful version of Parent; it returns a set of all the parents of a member, recursively up though the hierarchy including any multiple parents, grandparents, etc.
{[Date].[2006-10-01].Ancestors}
Returns “2006 – October”, “2006 – Q4”, “2006 – H2”, “2006”, “All Dates”.

The Ancestor function returns a single member, either itself (,0) or its first parent (,1), first parent’s first parent (,2), etc. depending on the value given as a parameter.
{ancestor([Date].[2006-10-01], 0)}
Returns “2006-10-01”.
{ancestor([Date].[2006-10-01], 1)}
Returns “2006 – October”.
{ancestor([Date].[2006-10-01], 2)}
Returns “2006 – Q4”.
{ancestor([Date].[2006-10-01], 3)}
Returns “2006 – H2”.
{ancestor([Date].[2006-10-01], 4)}
Returns “2006”.
{ancestor([Date].[2006-10-01], 5)}
Returns “All Dates”.


Average rating
(1 vote)