Page 1 of 1
Hierarchy sorting issue
Posted: Thu Sep 22, 2011 6:42 am
by ravi
Hi,
I need to create a Subset for the hierarchy sorted out names alphabetically-
When I use the sorting icon A-Z- it sorts only few elemements-Example
Parent A- have a elements B C D-this is fine.
but within the B hierarchy- elements does not get sorted alphabetically-
Please advise.
Regards,
Ravi
Re: Hierarchy sorting issue
Posted: Thu Sep 22, 2011 8:01 am
by MSidat
Hmm Interesting, i thought this would be possibly but having tried it, it appears not especially if you want the Hierarchy sort first as it will always structure it according to the original consolidation set up. ( I also tried clicking on the sorting icons a number of items, which sometimes works!!
)
unless someone comes up with a MDX statement to achieve it a work around would be to either:
Write a quick TI to either:
1, Restructure the dimension so all children within each consolidation are firstly removed and then reinserted in alphetical order or
2, Create a shadow dimension which mimics the original dimension but is structured alphetically. If you do a search on the forums or google you should find some script that the TM1 Guru's on here have created to copy a dimension, this can then be tweaked to ensure you add any children in alphatical order.
Both options should relatively be a quick TI and can be set up as a Chore to ensure it is always correct.
Re: Hierarchy sorting issue
Posted: Thu Sep 22, 2011 8:21 am
by jstrygner
If I understand you correctly, running this via TI should do the trick:
Code: Select all
DimensionSortOrder(DimensionName, 'ByName', 'ASCENDING', 'ByHierarchy', 'ASCENDING');
It will apply automatic sorting of elements among this dimension (by hierarchy first and by name among hierarchy second).
Re: Hierarchy sorting issue
Posted: Thu Sep 22, 2011 4:04 pm
by csjean
Hi Ravi,
This should work (although I haven't tested it).
Create an MDX subset with this:
Code: Select all
{
ORDER(
{TM1SUBSETALL([<your dim here>])},
[<your dim here>].Name, BDESC)
}
Hope this helps.