MDX - return all root elements

[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?

[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.)

[jim wood]

Doesn't filtering for all level 0 give all root elements???

[lotsaram]

Like I said, depends what the OP meant by root element.

[jim wood]

Indeed.

[rmackenzie]

I was just wondering if there's any possiblity to get all root elements?

- 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

[jim wood]

That's very different from most peoples definition of root members??

[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.

[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 13455 times

[jim wood]

Martin,

If it has no parent it is the bottom level as well,

Jim.

[Martin Ryan]

Yabbut, if it gets children its parent will still be "Root".

[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.

[rmackenzie]

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.

[tomok]

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.

[jim wood]

So you agree with me then Tomok?

[Michel Zijlema]

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

[tomok]

So you agree with me then Tomok?

Yes. I was just boiling down the facts to a simple concise statement.