Make subset alphabetical in TI process

[Martin Ryan]

I've got a static subset (the one I used to replace the MDX one in my other post) but I want to make that subset alphabetical at the end of the TI process that populates it. I can't find an appropriate function in the help file - anyone know a hidden feature/work around?

Martin

[LoadzaGrunt]

You could start by putting the base (unsorted) subset into a temporary subset and then using:

Code: Select all

{TM1SORT( {[YOUR_TEMP_SUBSET_NAME]}, ASC)}

And then copy the sorted temporary subset into your real subset.

[Martin Ryan]

Sounds like a good idea - provided the MDX fires this time - will give it a shot.

Martin

[Martin Ryan]

Yeah, that works well, for anyone who might need to do this in the future here is the TI code I've used. It requires two parameters: pDim (the dimension name) and pSubset (the subset that you want to alphabetise).

Code: Select all

# A zero subset will error and one element means it's
# already alphabetised
# Would use ProcessBreak except it throws an error
# so just using a big block if
if(subsetgetsize(pDim, pSubset)>1);

# This process will do nothing to MDX subsets
tempMDXSub='zTemp_MDX_' | numbertostring(rand());
tempStaticSub='zTemp_Static_' | numbertostring(rand());

if(subsetexists(pDim, tempMDXSub)=1);
  SubsetDestroy(pDim, tempMDXSub);
endif;

if(subsetexists(pDim, tempStaticSub)=1);
  SubsetDestroy(pDim, tempStaticSub);
endif;

subsetCreate(pDim, tempStaticSub);
SubsetCreatebyMDX(tempMDXSub, '{TM1SORT( {[' | pDim | '].[' | pSubset | ']}, ASC)}');

sourceSub=tempMDXSub;
destnSub=tempStaticSub;
k=subsetgetsize(pDim, sourceSub);
i=0;
while(i<k);
  i=i+1;
  elem=subsetgetelementname(pDim, sourceSub, i);
  subsetelementinsert(pDim, destnSub, elem, i);
end;

subsetdeleteallelements(pDim, pSubset);

sourceSub=tempStaticSub;
destnSub=pSubset;
k=subsetgetsize(pDim, sourceSub);
i=0;
while(i<k);
  i=i+1;
  elem=subsetgetelementname(pDim, sourceSub, i);
  subsetelementinsert(pDim, destnSub, elem, i);
end;

subsetdestroy(pDim, tempStaticSub);
subsetdestroy(pDim, tempMDXSub);

endif;

[lotsaram]

A bit late but just another thought, rather than the temp MDX subset approach you could also create a temp dim with automatic ordering by name and read from the temp dim into the subset to ensure it is ordered alphabetically.