MDX: Filter Parents but sort their Children

Post Reply
Ptec
Posts: 15
Joined: Mon Mar 14, 2016 9:14 am
OLAP Product: TM1
Version: 10.2.2.0
Excel Version: 2013

MDX: Filter Parents but sort their Children

Post by Ptec »

Hey there,

I have a "Problem" Im working on for 2 days now but couldn't find a solution till now.

I have a Dimension with a Top Node, 2 Parent Elements with several Childs. All Elements have a numeric Attribute like 10,11,13...

We now need the Children to be sorted ascending to their Attributes BUT the Children shouldnt be Drilled Down/Expanded in the Subset View.

So in short hand:
1. Filter Level 1 and 2 Elements (Working)
2. Sort these Elements depending on their "Sort" Attribute Ascending (Working)
3. Sort the Child Elements of these Parents depending on their "Sort" Attribute Ascending (Not working)

I provide here an Image so you better understand my needs and hopefully a MDX genius can help me out with this!
Target Subset Filtering and Order.png
Target Subset Filtering and Order.png (55.25 KiB) Viewed 3753 times
If this is not achievable with an MDX Statement maybe a TI Process will do it?

Thanks!
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: MDX: Filter Parents but sort their Children

Post by declanr »

As an easy approach why not concatenate the parent and children sorts... so 11 could be a consolidation and under it 11.1, 11.2 etc.
Declan Rodger
Ptec
Posts: 15
Joined: Mon Mar 14, 2016 9:14 am
OLAP Product: TM1
Version: 10.2.2.0
Excel Version: 2013

Re: MDX: Filter Parents but sort their Children

Post by Ptec »

Does it make a difference because how could I sort the children? The "ORDER" MDX Command just uses the "Filtered" Elements which are the parents, so the Ordering takes no effect on the children...

I tought of using a Subset Drilled down all Elements so I get the correct Attribute Order. That works already.

Now i wanted to use this Subset in TI to Create another Subset with the above MDX Statement (my first post). BUT I think it's not possible to use a Subset with the "TM1SUBSETALL" command right?
ardi
Community Contributor
Posts: 148
Joined: Tue Apr 02, 2013 1:41 pm
OLAP Product: tm1, cognos bi
Version: from TM1 9.4 to PA 2.0.9.6
Excel Version: 2010
Location: Toronto, ON

Re: MDX: Filter Parents but sort their Children

Post by ardi »

Ptec wrote:Does it make a difference because how could I sort the children? The "ORDER" MDX Command just uses the "Filtered" Elements which are the parents, so the Ordering takes no effect on the children...

I tought of using a Subset Drilled down all Elements so I get the correct Attribute Order. That works already.

Now i wanted to use this Subset in TI to Create another Subset with the above MDX Statement (my first post). BUT I think it's not possible to use a Subset with the "TM1SUBSETALL" command right?
If you concatenate the Sort attribute then you can achieve what you want.

Code: Select all

{ORDER ( {TM1SUBSETALL( [dim_Kunden] )} , [dim_Kunden].[Sort], ASC)}
By concatenating the Parent Sort with Children Sort Order you will maintain the "Hierarchy Sort" of your dimension and also within each hierarchy, you will apply your desired sort for the children
Ardian Alikaj
Post Reply