TM1 Forum

Posted: **Tue Oct 16, 2012 6:08 pm**

Hello All

I want to create an MDX driven report containing all parents of a set of n-level elements that are filtered by attributes.

Code: Select all

HIERARCHIZE(FILTER ( GENERATE(Filter( TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[DIM].[PARENT]}, ALL, RECURSIVE )}, 0), [DIM].[ATTRIBUTE] = "XXXX"),[DIM].CurrentMember.Ancestors ),[DIM].Currentmember.Level.ordinal =1))

I would like the result to show just the top level in the first line with drill down capability to the n-level that have filters applied to them. So far all I generate is the hierarchy I need but it does not apply the n-level filters. I suspect the Generate statement is over-riding the filters at the n-level. I have seen a number of similar examples to what I need on this forum but none seem to work.

Any suggestions are welcome.

Note: This is the first time I have posted so I hope I have done it correctly.

Thanks!

Posted: **Wed Oct 17, 2012 12:22 pm**

After more research and assistance from a colleague I have the answer:

Code: Select all

{HIERARCHIZE(
{EXCEPT({DESCENDANTS([DIM].[Parent])},{TM1FILTERBYLEVEL({DESCENDANTS([DIM].[Parent])},0)})} +
{TM1FILTERBYLEVEL({FILTER({FILTER({DESCENDANTS([DIM].[Parent])},[DIM].[Attribute1]='XXXX')},[DIM].[Attribute2]='XXXX')},0)})}

TM1 Forum

MDX returning parents but not filtered n-Level results

MDX returning parents but not filtered n-Level results

Re: MDX returning parents but not filtered n-Level results