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!
If this is not achievable with an MDX Statement maybe a TI Process will do it?
Thanks!
MDX: Filter Parents but sort their Children
-
- 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
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
-
- 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
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?
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?
-
- 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
If you concatenate the Sort attribute then you can achieve what you want.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?
Code: Select all
{ORDER ( {TM1SUBSETALL( [dim_Kunden] )} , [dim_Kunden].[Sort], ASC)}
Ardian Alikaj