TM1 Forum

So what I am trying to accomplish is sort my dimension, which includes multiple uneven hierarchies, by both input and name at the same time. What I mean by that is I want all the consolidated elements to be in order of input (from how they were loaded in, same thing as I understand it as index number) but all the level 0 leaf elements to be sorted by name. I am having a lot of trouble trying to accomplish this feat in TM1. I am not sure if it is really easy and I am just not seeing it, if I need to use a more advanced MDX function, or if it is just not currently possible (I am hoping its the first one). I am able to sort the entire dimension by input, or the entire dimension by name, but not a mix and match of the two. I can accomplish my goal when loading it into TM1 via TI or creating a subset, however I would prefer to do this when I load in my dimension from a .txt file.

Any help will be greatly appreciated it.

Thanks,

There is no way to do this natively in TM1. As I see it, you have three options:

1) Create your dimension in an XDI sheet. You can order it any way you want when you do that.
2) Write your own sorting algorithm in a TI process, kind of like the bubble sort you learned in Programming 101
3) Change the order of your input file so that you can set the dimension sort order property to "By Input" and it will still be sorted the way you've asked.

I would choose either 1) or 3). 1) is the easiest but I don't know your source so I don't know how often it will change because that method will require manual steps. Option 3) may very well require manual steps too, I don't know.

Thanks for your help tomok, I will give them a try. The only thing is I never took Programming 101, so I am not sure what a bubble sort is. I will have to look that one up.


Thanks,

4th possibility - write the contents out to a relational store and read it back sorted using ORDER BY.