Today I was looking for the MDX to get the leaf level (or N level or Base Level) elements from a dimension after supplying the name of an element further up the tree. What I needed was the weeks from a year in a continuous Time dimension, where the user enters the year as a parameter in a TI.
MDX Syntax to Return the Children of a TM1 Member?
Simply use the syntax:
{TM1FilterByLevel(Descendants( [DimName].[ParentElement]) , 0)}where:
- DimName is the name of the dimension you are workmen with, and
- ParentElement is the element ID of the ancestor you want the leaf level elements from
Example of Required MDX
To illustrate with an example of a Time dimension. We have a consolidation element All Time and underneath that we have years as ‘2020’, ‘2021’, 2022′, ‘2023’ etc. Within each of those we then have periods, so 2022.01, 2022.02, 2022,03 etc and under the periods we have weeks, like 2022.Wk1, 2022.Wk2, 2022.Wk3 etc. So for 2022 there are 52 elements I want to end up with in the set. So here is the MDX I used:
{TM1FilterByLevel(Descendants( [Time].[2022]) , 0)}This would then return only the N level elements of 2022, but not 2022 or the periods like 2022.01 or 2022.02. So the resulting dynamic MDX based set would include only the weeks from 2022
Exporting Values from TM1 using MDX
The nice thing is that this MDX can then be used in a TI to export just these child elements or to transfer them to a different version. Nice!
If you would like to read more about MDX in TM1, please see our primer here.
For more information on how you can export from TM1 using MDX, please get in touch with us at ExploringTM1 and we’ll be delighted to help!
