Create your own SUBIX

Ideas and tips for enhancing your TM1 application
Post Reply
Andy Isola
Posts: 1
Joined: Mon Jul 08, 2013 6:43 pm
OLAP Product: TM1
Version: 9.5.2
Excel Version: 14.0.6129.5000

Create your own SUBIX

Post by Andy Isola » Tue Jul 09, 2013 2:16 pm

I got fed up with not having a Subix function, so I ended up hardcoding one myself. Saves me a lot of time and thought.
This function will return the first instance of the element within the subset. You could search for the last instance by reversing the loop, or include an additional counter to count for the Nth instance in the subset.

Code: Select all

Parameters:
pDim - A valid dimension name.
pSubset - A valid subset name in pDim.
pElement - The name of an element within the dimension.

Code: Select all

## SUBIX ######################################################
## Intended Functionality: SUBIX(dimension, subset, element)
## Usage: ExecuteProcess('SUBIX', 'pDim', DIMENSION, 'pSubset', SUBSET, 'pElement', ELEMENT)
## SUBIX stores the index number of an element within a subset in the global variable 'gSubixReturn'. 
## If the element is not found within the subset, gSubixReturn is set to -1.
##
## Before calling, be sure to include the line 'NumericGlobalVariable('gSubixReturn');' in the parent process.
################################################################

NumericGlobalVariable('gSubixReturn');

i = 1;
nMax = SubsetGetSize(pDim, pSubset);
gSubixReturn = -1;
WHILE(i <= nMax);
	IF( SubsetGetElementName(pDim, pSubset, i) @= pElement);
		gSubixReturn = i;
		i = nMax;
	ENDIF;
	i = i + 1;
END;

User avatar
Steve Vincent
Site Admin
Posts: 1049
Joined: Mon May 12, 2008 8:33 am
OLAP Product: TM1
Version: 10.2.2 FP1
Excel Version: 2010
Location: UK

Re: Create your own SUBIX

Post by Steve Vincent » Mon Jul 29, 2013 1:10 pm

Superb, a nice simple way of filling a glaring functionality hole in the TI editor :) I'll move this to the useful code section, thanks.
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: TM1 64 bit 10.2.2, Windows 2008/2012 Server. Excel 2010, IE11 for t'internet

Post Reply