Page 1 of 1
MDX - return all root elements
Posted: Mon Oct 10, 2011 7:36 am
by andreask
I was just wondering if there's any possiblity to get all root elements?
.members gives me the complete hierarchy back and with filebylevel I need to know the exact level - which can change.
Any other ideas?
Re: MDX - return all root elements
Posted: Mon Oct 10, 2011 8:38 am
by lotsaram
You need to be clear on what exactly you mean by "all root elements". Do you mean ...
- All elements in the dimension? (TM1SubsetAll function)
- All leaf elements? (Nest TM1FilterByLevel with TM1SubsetAll)
- All "root hierarchy" nodes? (that is members with descendants but no ancestors, MDX is more tricky but can also be done.)
Re: MDX - return all root elements
Posted: Mon Oct 10, 2011 1:55 pm
by jim wood
Doesn't filtering for all level 0 give all root elements???
Re: MDX - return all root elements
Posted: Mon Oct 10, 2011 2:11 pm
by lotsaram
Like I said, depends what the OP meant by root element.
Re: MDX - return all root elements
Posted: Mon Oct 10, 2011 2:48 pm
by jim wood
Indeed.
Re: MDX - return all root elements
Posted: Mon Oct 10, 2011 11:17 pm
by rmackenzie
andreask wrote:I was just wondering if there's any possiblity to get all root elements?
lotsaram wrote:- All "root hierarchy" nodes? (that is members with descendants but no ancestors, MDX is more tricky but can also be done.)
My read on the OP's question is that he wants all the top-level consolidations:
Code: Select all
{Filter(
{TM1SubsetAll([YOUR_DIMENSION_NAME])},
[YOUR_DIMENSION_NAME].CurrentMember.Parent.Name="")
}
Robin
Re: MDX - return all root elements
Posted: Tue Oct 11, 2011 2:21 pm
by jim wood
That's very different from most peoples definition of root members??
Re: MDX - return all root elements
Posted: Tue Oct 11, 2011 8:48 pm
by rmackenzie
Jim, interesting... I guess the 'root' is sort of the opposite of the 'leaf' (n-level) if we are talking tree analogies... so I often refer to the top consolidations as root elements.
Re: MDX - return all root elements
Posted: Tue Oct 11, 2011 9:34 pm
by Martin Ryan
For what it's worth, if you add in an element into the Dimension Editor with no parent (i.e. top level) then the little dialog box calls the parent "Root"
- toplevel.JPG (25.69 KiB) Viewed 13488 times
Re: MDX - return all root elements
Posted: Wed Oct 12, 2011 1:08 pm
by jim wood
Martin,
If it has no parent it is the bottom level as well,
Jim.
Re: MDX - return all root elements
Posted: Wed Oct 12, 2011 7:51 pm
by Martin Ryan
Yabbut, if it gets children its parent will still be "Root".
Re: MDX - return all root elements
Posted: Wed Oct 12, 2011 8:03 pm
by jim wood
Will it though? By putting Root in parent it is actually suggesting it has no parent as it is a root object. This a dialogue box for adding elements. If you insert a child to this it will say the element you first created as a parent. How ever if you select add element instead of insert child the parent will say root. You could argue it's right in both cases. When an element has no parent and is level 0. If it has no parent and is level 6 it is still root, therefore root is neither level 0 or level6, it is any element that has no parent. If we are holding with this definition that is.
Re: MDX - return all root elements
Posted: Wed Oct 12, 2011 9:32 pm
by rmackenzie
jim wood wrote:When an element has no parent and is level 0. If it has no parent and is level 6 it is still root, therefore root is neither level 0 or level6, it is any element that has no parent. If we are holding with this definition that is.
Generally, my definition of root element is any consolidation at the top of the hierarchy. I agree, technically speaking, that an orphan n-level is still a root element; and will be picked up by the MDX query I posted above.
The flip-side of this point can be seen when using the following well-known MDX which returns all 0-level elements. As well as all the n-level elements (which is what people generally want) this will also return consolidated elements that have no children:
Code: Select all
{TM1FilterByLevel({TM1SubsetAll([YOUR_DIMENSION_NAME])}, 0)}
One challenge that has always puzzled me is how to exclude the c-level elements from this query (without using attributes). In other words, how is it possible to get the same functionality as DTYPE, but in an MDX statement?
I think this conversation just goes to show the subtle differences between the level of an element in a dimension and the definition of n- and c-level elements.
Re: MDX - return all root elements
Posted: Thu Oct 13, 2011 12:26 pm
by tomok
rmackenzie wrote:Generally, my definition of root element is any consolidation at the top of the hierarchy. I agree, technically speaking, that an orphan n-level is still a root element; and will be picked up by the MDX query I posted above.
In TM1, a root element is any element that doesn't have a parent. It can be either a bottom level element or a consolidation.
Re: MDX - return all root elements
Posted: Thu Oct 13, 2011 1:56 pm
by jim wood
So you agree with me then Tomok?
Re: MDX - return all root elements
Posted: Thu Oct 13, 2011 4:04 pm
by Michel Zijlema
jim wood wrote:So you agree with me then Tomok?
Hi Jim,
I thought that the root of something that is not branching out should be called a carrot
Michel
Re: MDX - return all root elements
Posted: Thu Oct 13, 2011 4:24 pm
by tomok
jim wood wrote:So you agree with me then Tomok?
Yes. I was just boiling down the facts to a simple concise statement.