TM1 Forum

Hi! Again,

I wonder why this does happen, I have a dynamic subset which means it has an expression recorded in it. My intention for this subset is to display all leaf elements (n elements/ zero level) only and I also intend not the consolidated elements with no child/ren to be dispalyed. But they just remain on my subset. Is there any way I can retrieve only the leaf elements excluding the consolidated elements without child/ren? I think it's on my MDX, but don't know how.

Advance Thanks for your help!

bunchukokoy

Orphan consolidations are level 0 elements which is why they are returned in the subset (presumably you are filtering on level 0).

A consolidation with no children has no reason to exist. Usually you would run a TI script to remove orphan consolidations.

First, my simple workaround proposal:
1. Define a text attribute (e.g. Type) for the dim you want to filter on.
2. Create a rule in Control Object Cube that stores those attributes values where you calculate element type (C or N).
3. Expand your filter by level = 0 MDX with filter by attribute where 'Type' = 'N'.

Second, with my whole respect, I dare to disagree with lotsaram's statement

I used consolidations with no children in one project and found it very useful.
It was a dimension where every department had potentially two types of projects it could budget.
Sometimes departments had no projects of one or both types, so it looked like this:

In the above example Dep B had no projects of type X, but still it was very helpful that such an element (Dep B Projects X) existed (we could use it in many reports, TI, dynamic subsets, parallel hierarchies and technical consolidations). It was also very useful, that no one could write into it, because it was a consolidation.

Regards,