Sort a TI created subset

[flesh2011]

Hi.

Finaly found out the logic behind creating a subset in TI.

But the last touch is missing. Sort the elements in subset, ascending.

Have tried with mdx like this :

StingMDX=
'TM1SORT({TM1SubsetALL(V_Konto)}, ASC)}'; OR Subset basic

And tried to play with the SubsetElementInsert('V_Konto',subset,element,+1); posistion.



Am I on to something here?

Thanks

[rmackenzie]

For a pre-existing subset, you can create a second, alpha sorted subset just by referencing the first:

Code: Select all

sDimName = 'YOUR_DIMENSION_NAME';
sOriginalSubName = 'YOUR_ORIGINAL_SUBSET_NAME';
sNewSubName = 'YOUR_NEW_SUBSET_NAME';
sMdx = '{TM1SORT( {[' | sDimName | '].[' | sOriginalSubName | ']}, ASC)}';
SubsetCreateByMdx ( sNewSubName, sMdx );

Or, depending on the logic you used in the first place, you could possibly put the sorting logic in there... without seeing your code it is impossible to say !

[ExApplix]

I need to sort the Subset elements within a TI Process.

As advised by rmackenzie we can only use the MDX for the existing Subsets, therefore I think using MDX is not a option for me.

How can we use the Position parameter in the SubsetElementInsert()? or is there any other way to do this?

Please help!

[tomok]

Why not create a temporary MDX sorted subset and then use that to create a permanent subset. Apologies for any typos or syntax errors, but you can get the idea from this:

Code: Select all

sDimName = 'YOUR_DIMENSION_NAME';
sOriginalSubName = 'YOUR_ORIGINAL_SUBSET_NAME';
sMDXSubName = 'NAME_OF_MDX_SUBSET';
sMdx = '{TM1SORT( {[' | sDimName | '].[' | sOriginalSubName | ']}, ASC)}';
SubsetCreateByMdx ( sMDXSubName, sMdx );

sNewSubName = 'NAME_OF_YOUR_SORTED_SUBSET';
SubsetCreate(sDimName,snewSubName);

sz = SubsetGetSize('NAME_OF_MDX_SUBSET');

n=1;

WHILE(n <= sz);
  ElName = SubsetGetElementName(sDimName, sMDXSub, n);
  SubsetElementInsert(sDimName, sNewSubName, ElName, n);
  n = n + 1;
END;

SubsetDestroy(sDimName, sMDXSubName);

[rmackenzie]

I need to sort the Subset elements within a TI Process.

As advised by rmackenzie we can only use the MDX for the existing Subsets, therefore I think using MDX is not a option for me.

The example I gave is one option amongst several - I didn't actually say you can only use MDX in that way. Did you try out the code that I supplied? Or, did you try out what tomok supplied, which is a good extension on the original suggestion?.

How can we use the Position parameter in the SubsetElementInsert()? or is there any other way to do this?

I'm not sure how this would help directly as you would still need to do an alphabetical comparison between two elements in order to know the correct position to insert the element at. Really, this is what the sorting functions in MDX are doing for you. It is possible to implement common sorting functions such as Quick Sort and Bubble Sort in TI, but I am reluctant to recommend this to you as it would appear to be beyond the point where you are at.

Please help!

You need to provide more information... for example, are you using SQL data sources? If so, you have the option of doing sorting in your queries to the source system which may be a more efficient way to produce alphabetically sorted subsets. Have you looked at using the TI command DimensionSortOrder - once your dimension is sorted alphabetically, it may be easier to produce the subsets you want without having to implement independent sorting logic.

[LanceTylor]

Hi All,

I have been searching on the forum for techniques on how to sort a pre-existing static subset after adding an element? I know the answer is here some where but my issue doesn't fit some of the issues posted around sorting subsets so I thought I would throw out here to get some suggestions.

Currently I am using a TI process via an action button to add/delete elements to a static subset and would like to add some logic so that I can sort it alphabetically at the end of the process.

Subset details
1. Users have the ability to add/delete elements from a subset. Currently the process positions all newly added elements at the top of the subset (position 1)
2. The subsets are static and as users have randomly chosen elements to add (meaning there is no attribute to help recreate the subset list) I dont think we can delete and create the subset. Unless, there is a way to overwrite the subset? I tried to logic include in this post and the process was aborted because I used the same name for the "OriginalSubset" & "NewSubset" as I dont want to create a different name everytime

Any help is much appreciated

P.S. I am using Cognos Express 9.5

[Martin Ryan]

You can use tomok's suggestion above, but there's a few steps to it in your case
- create a sorted MDX subset based on your subset with a new name
- copy the elements from the MDX subset into another static subset
- copy from that final static subset back into your original subset
- delete the intermediate MDX and static subsets

Or as another alternative you could create a temporary dimension that was set up to always be alphabetised (see "DimensionSortOrder") and copy all the elements from the subset into that dimension, then back to the subset. Then delete the temporary dimension.

Martin

[LanceTylor]

Hi Martin,

thanks for the response. I will give it go.

[LanceTylor]

Hi Martin,

So after a few tweeks I go the subset sort issue resolved. Thanks for your help. Hopefully, TM1 10.1 (or future versions) have "SubsetSortOrder" function which wouls save a lot of time/.

I included the TI script to share the knowledge.

Regards
Lance

#--Inserts Element to Orignal Subset
SubsetElementInsert(sDimName, pDept, ElName,1);

sDimName = 'Programs';
sOriginalSubName = pDept;
sMDXSubName = pDept|'-'|'Temp1';
sMdx = '{TM1SORT( {[' | sDimName | '].[' | sOriginalSubName | ']}, ASC)}';

#Create MDX Subset which sorts the Original Subset
SubsetCreateByMdx ( sMDXSubName, sMdx );

#Create Temp subset based on MDX subset that is static then Delete MDX subset and Original
sNewTempSubName = pDept|'-'|'Temp2';
SubsetCreate(sDimName,sNewTempSubName);

sz = SubsetGetSize(sDimName,sMDXSubName);

n=1;

WHILE(n <= sz);
MDXElName = SubsetGetElementName(sDimName, sMDXSubName, n);
SubsetElementInsert(sDimName, sNewTempSubName, MDXElName, n);
n = n + 1;
END;

SubsetDestroy(sDimName, sMDXSubName);
SubsetDestroy(sDimName, pDept);

#Create Original Subset and copy the Static Temp subset (which is now sorted) and then destory the Static temp subset
sNewSubName = pDept;
SubsetCreate(sDimName,sNewSubName);

sz = SubsetGetSize(sDimName,sNewTempSubName);

n=1;

WHILE(n <= sz);
ElName = SubsetGetElementName(sDimName, sNewTempSubName, n);
SubsetElementInsert(sDimName, sNewSubName, ElName, n);
n = n + 1;
END;

SubsetDestroy(sDimName, sNewTempSubName);

Viola!

[Martin Ryan]

Nice work Lance, I'm genuinely impressed that you've taken a very vague suggestion and managed to make some good looking TI code out of it.

As for your hope that IBM will fix that up in 10, I'm sorry to burst your bubble but it won't happen. I'm still waiting for my subix function after about six years of requests. They'll say MDX does the job - and actually I don't disagree. The only problem is that MDX subsets get re-evaluated every single time you look at it.

Alphabetising subsets is also a surprisingly rare requirement. In over eight years developing TI I've only needed to do it once.

[ParisHilton]

I suspect IBM would argue there is no need for TM1 to order subsets and the client tool should be responsible, probably via MDX?

[LanceTylor]

Thanks Martin. This has been a client requirement for 2 weeks so its nice to have it done!!

[Michel Zijlema]

You can use tomok's suggestion above, but there's a few steps to it in your case
- create a sorted MDX subset based on your subset with a new name
- copy the elements from the MDX subset into another static subset
- copy from that final static subset back into your original subset
- delete the intermediate MDX and static subsets

Or as another alternative you could create a temporary dimension that was set up to always be alphabetised (see "DimensionSortOrder") and copy all the elements from the subset into that dimension, then back to the subset. Then delete the temporary dimension.

Martin

Another option would be to:
- create a sorted MDX subset
- use the trick described here to change the subset from dynamic to static
No intermediate subset and copying of elements are required in this case.

Michel

[lav4you]

Hi Michel,

Very Nice trick... I'll try n see if it works...