Source MDX from Tm1 Cube text with variable, then create sub

[BigG]

Hi there, This is a curly.

I am testing a concept by where I enter my MDX string into a cube and then extract the MDX string into a TI with CellGetS, then SubsetCreatebyMDX

Due to MDX being long strings I also break my MDX into parts using concatenated variables eg.
vMDX = '{ TM1FILTERBYLEVEL( {TM1SUBSETALL( [' | vDimName | '] ) }, 0 ) }';
vMDX = '{ TM1SORT( ' | vMDX | ', ASC) }';

I have discovered if I have the string '{ TM1FILTERBYLEVEL( {TM1SUBSETALL( [' | vDimName | '] ) }, 0 ) }' in my cube CellGetS does not recognise the concatenate and variable vDimName and subsequent SubsetCreatebyMDX will error as "Syntax error at or near: '| vDimName | "'] ) }'"

Does anyone know a method to parse the string parts from the variable so the SubsetCreatebyMDX will not error? I have searched but there is nothing aparent

Cheers
GG

[EvgenyT]

Can you please screendump mdx sting stored in the cube and your cellgets code.

Thanks

ET

[rmackenzie]

You can solve this by using placeholders, rather than storing the MDX statement with the TI variable concatenation which, as you've found, doesn't just work itself out the way you expect. Here's an example:

MDX statement stored in cube:

Code: Select all

{ TM1FILTERBYLEVEL( {TM1SUBSETALL( [%DIM_NAME%] ) }, 0 ) }

TI to apply MDX to a specific dimension:

Code: Select all

# get generic MDX statement, name of placeholder and real dimension name
sMdx = CellGetS ( 'YOUR_CUBE', 'X', 'Y', 'MDX' );
sDimName = 'plan_chart_of_accounts';
sPlaceholder = '%DIM_NAME%';

# isolate the placeholder
nStartPos = SCAN ( sPlaceHolder, sMdx );
nEndPos = nStartPos + LONG ( sPlaceHolder );
sLeftHandPart = SUBST ( sMdx, 1, nStartPos - 1 );
sRightHandPart = SUBST ( sMdx, nEndPos, LONG ( sMdx ) - 1 );

# rebuild the MDX with the real dimension name instead of the placeholder
sUsableMdx = sLeftHandPart | sDimName | sRightHandPart;

[BigG]

brilliant rmackenzie

Thanks ET too, I think rmackenzie has resolved the issue.

'{ TM1FILTERBYLEVEL( {TM1SUBSETALL( [' | vDimName | '] ) }, 0 ) }'

should be

{ TM1FILTERBYLEVEL( {TM1SUBSETALL( [%DIM_NAME%] ) }, 0 ) }

and then use TI code to strip and replace information

[EvgenyT]

neat solution rmackenzie. thanks for sharing. Glad it worked out for you BigG