TM1 CHEAT SHEET

STRINGS

• SUBST(string, start, length)
• DELET(string, start, length)
• SCAN(substring, string) 0 if not found
• LONG(string)

---

LOGICAL OPERATORS

• & (AND)
• % (OR)
• ~ (NOT)

---

checks

##  cube exists
IF( DIMIX( '}Cubes', cub )=0 ); ExecuteProcess( '}.log','pID',pID,'pPN',pn,'pMS', 'Cube not found: '''| cub |'''' ); ENDIF;

##  attribute exists
sAttrExists = IF(DIMIX('}Element_Attributes_'| dim, attr) = 0, 'NO', 'YES' ) );

}.update subset

dn = dim_target;
sn = '.export from '| dim_target;
sx = 'el1,el2';
sx = '{.leaves}';
sx = '{ FILTER( {TM1SUBSETALL( ['| dim_target |'] )}, ['| cub_target |'].(['| cub_target |'.Measures].[Measure_Code]) @<>'') }';
sx = '<dim_lookup>{.all}';
sx = '{FILTER( {TM1FILTERBYLEVEL( {TM1SubsetAll([Account_COGS])}, 0)} , [Account_COGS].[isCalc] <> ''Y'')}';
# remove duplicates in MDX subset
sx='{ UNION( '| sx |', '| sx |' ) }';
rc = ExecuteProcess( '}.update subset','pID',pID,'pDG',pDG, 'pDN', dn, 'pSN', sn, 'pSX', sx);
IF( rc <> ProcessExitNormal() ); ProcessError; ENDIF;

File output

##  simple AsciiOutput output
AsciiOutput( file, 'Some text output', textVariable, NumberToString( numVariable ), 'Another text column # 4' );

##  TextOutput output and character encoding
SetOutputCharacterSet( file, 'TM1CS_UTF8' );
TextOutput(file, 'Some UTF8 text');

Logging

##  log data import results
IF( err_cnt > 0 );
    IF( err_cnt = err_cnt_max ); ExecuteProcess( '}.log','pID',pID,'pPN',pn,'pMS', 'Max error limit ('| NumberToString(err_cnt_max) |') was reached, process was stopped! Records loaded: '| NumberToString(row_cnt-err_cnt));
    ELSE; ExecuteProcess( '}.log','pID',pID,'pPN',pn,'pMS', 'Error count: '| NumberToString(err_cnt) |'. Records loaded: '| NumberToString(row_cnt-err_cnt)); ENDIF;
    ProcessError;
ELSE;
    ExecuteProcess( '}.log','pID',pID,'pPN',pn,'pMS', 'Data loaded successfully! Records loaded: '| NumberToString(row_cnt-err_cnt), 'pMT', 'INFO');
ENDIF;

Looping dimension / subset

##  looping subset
n = SubsetGetSize( dim, sub );
i=1; WHILE( i <= n );
    elm = SubsetGetElementName( dim, sub, i );
i=i+1; END;
## looping dimension
n = DIMSIZ( dim ); WHILE (n > 0);
    elm = DIMNM( dim, n );
n=n-1;
END;

##  looping C-element children
this_total = 'Total Year';
n = ElCompN( dim, elmC);
WHILE (n > 0);
    elm= ElComp( dim, elmC, n );
n=n-1;
END;

##  looping elements with >1 parents
n = DIMSIZ( dim );
i=1; WHILE( i <= n ); elm = DIMNM( dim, i ); IF( ElparN(dim, elm) > 1); AsciiOutput( lf, elm ); ENDIF;
i=i+1; END;

looping cube dimensions

## loop cube dimensions
i = 1;
dim = TABDIM( cub, i );
WHILE( dim @<> '' );
    IF(pDG > 1); ExecuteProcess( '}.log','pID',pID,'pPN',pn,'pMS', 'This dimension: '''| dim |'''', 'pMT','INFO' ); ENDIF;
    ## do something with "dim" here

    ## next dim
    i = i + 1;
    dim = TABDIM( dim, i );
END;

Common tasks

##  copy a subset of elements from one dimension to another one
sSrcDim = 'Account';
sSrcDimTtlEl = 'Total Operating Expenses';
sTrgDim = 'Account';
sTrgDimTtlEl = 'All Operating Expenses';

##  use a different total element in target dimension
DimensionElementInsertDirect( sTrgDim, '', sTrgDimTtlEl, 'C' );

##  create subset in source dim
sn = '}.tmp';
sx = '{EXCEPT( {TM1DRILLDOWNMEMBER( {['| sSrcDim |'].['| sSrcDimTtlEl |']}, ALL, RECURSIVE )}, { TM1FILTERBYLEVEL({TM1SUBSETALL( ['| sSrcDim |'])}, 0)} )}';
rc = ExecuteProcess( '}.update subset','pID',pID,'pDG',pDG, 'pDN', sSrcDim, 'pSX', sx, 'pSN', sn );
IF( rc <> ProcessExitNormal() ); ProcessError; ENDIF;

##  loop subset, add consolidate element and all the descendants
n = SubsetGetSize( sSrcDim, sn );
i=1; WHILE( i<=n );
    sElName = SubsetGetElementName( sSrcDim, sn, i );
    nChildren = ElCompN( sSrcDim, sElName );
    nCount = 1; While( nCount <= nChildren );
        sChildElement = ElComp( sSrcDim, sElName, nCount );
        sChildWeight = ElWeight( sSrcDim, sElName, sChildElement );
        sParentElement = IF( sElName@= sSrcDimTtlEl,  sTrgDimTtlEl, sElName );
       DimensionElementComponentAdd( sTrgDim, sParentElement, sChildElement, sChildWeight );
    nCount = nCount + 1; End;
i=i+1; END;

MDX expressions

{TM1SUBSETALL( [DimName] )}
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [DimName] )}, 0)} )}
{HIERARCHIZE( {Subset} )}
{TM1DRILLDOWNMEMBER( {Subset}, {Subset} or ALL, RECURSIVE)}
{EXCEPT( {SubsetAll}, {SubsetExcept})}
##  subset elements + all the ascendants
{GENERATE( {[dim_location].[Toronto],[dim_location].[Paris]}, ASCENDANTS( [}dim_location].CurrentMember ) )}

TI ProcessReturnCode (process exit status codes)

ProcessExitNormal()       = 0
ProcessExitWithMessage()  = 5
ProcessExitMinorError()   = 6
ProcessExitByBreak()      = 7
ProcessExitByQuit()       = 8
ProcessExitSeriousError() = 10
ProcessExitOnInit()       = 11
ProcessExitByChoreQuit()  = 16