Page 1 of 1
MDX to eliminate consolidations without children
Posted: Thu Apr 05, 2012 11:40 am
by vladkon
I have a dimension with 6-levels unbalanced hierarchy. Is it possible to write an MDX to create dynamic subset that includes only those parts of hierarchy tree that has all 6 levels, and not those that have less levels? Any advise greatly appreciated.
Re: MDX to eliminate consolidations without children
Posted: Fri Apr 06, 2012 7:57 am
by rmackenzie
Reading your post title and your actual post, I'm thinking that you are experiencing the problem of asking for level-0 members of the dimension but having the consolidations with no children returned in that set. Is that correct? If so, you need to post-process your dimension after update, and use an attribute to flag the elements that are genuine leaves - you can use DTYPE to do this (in TI, not in MDX). Then your MDX can use FILTER and this attribute, rather than TM1FILTERBYLEVEL on level 0 which will return the unwanted empty consolidations.