TM1 Forum

Posted: **Fri Sep 13, 2013 5:38 am**

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

Posted: **Fri Sep 13, 2013 6:17 am**

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

Thanks

ET

Posted: **Fri Sep 13, 2013 6:41 am**

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;

Posted: **Sun Sep 15, 2013 9:58 pm**

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

Posted: **Sun Sep 15, 2013 10:45 pm**

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

TM1 Forum

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

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

Re: Source MDX from Tm1 Cube text with variable, then create

Re: Source MDX from Tm1 Cube text with variable, then create

Re: Source MDX from Tm1 Cube text with variable, then create

Re: Source MDX from Tm1 Cube text with variable, then create