TM1 supports a standard MDX function ASCENDANTS which returns all the parent elements (up to the dimension root).
Let’s say you have a dimension like:
— World
—- Europe
—— UK
—— France
——– Paris
—- North America
—— USA
—— Canada
——– Toronto
—- South America
—— Brazil
You want to select {Toronto, Paris} and all the parent elements.
ASCENDANTS accepts only a member. To deal with a set use another standard MDX function GENERATE, which accepts element subset and executes some expression against each its element.
{GENERATE( {[dim_location].[Toronto],[dim_location].[Paris]}, ASCENDANTS( [}dim_location].CurrentMember ) )}
and it will return:
Toronto
Canada
World
Paris
France
You can also use HIERARCHIZE function to sort it by hierarchy:
{HIERARCHIZE( {GENERATE( {[dim_location].[Toronto],[dim_location].[Paris]}, ASCENDANTS( [}dim_location].CurrentMember ) )} )}
Cognos/TM1 
thanks
LikeLike
I am trying DESCENDANT function which is similar to ascendant. It works fine however i want to exclude the parent items and only keep the children(Descendants)…is there a way to exclude parent items?
LikeLike
Hi Hassan. Using DESCENDANT function you will get all the child elements. If you want to exclude all the parents (consolidated elements), you can filter the result set by level 0. Also instead of GENERATE I suggest using TM1DRILLDOWNMEMBER function (which is “more stable”):
{TM1FILTERBYLEVEL({TM1DRILLDOWNMEMBER( {[dimName].[parent1], [dimName].[parent2]}, ALL, RECURSIVE )}, 0)}
LikeLike