Combining 2 mdx statements in a string to create subset

[gabriele]

In TI Process I am trying to combine 2 MDX statements into a string to create the subset and get the error "Invalid String Expression".
I am using generic code to create subsets that are filtered by level zero, and then overriding some subsets because I do not want these at Level zero.

If using a simple MDX statement, it works and creates the subset correctly such as this one:

#OVERRIDE SUBSETS CREATED WITH SPECIFIC ELEMENT VALUES OR MDX STRINGS
#CAPITAL_DEPRECIATION_M
OverrideDim = 'Capital_Depreciation_M';

StringMDX = '{TM1SUBSETALL( [ ' | OverrideDim | ' ] )}';

IF (SubsetExists (OverrideDim, SubsetName) <> 0);
SubsetDestroy (OverrideDim, SubsetName);
SubsetCreatebyMDX (SubsetName, StringMDX);
ELSE;
SubsetCreatebyMDX (SubsetName, StringMDX);
ENDIF;

When I try to combine 2 MDX statements into a string, I get an error:

#OVERRIDE SUBSETS CREATED WITH SPECIFIC ELEMENT VALUES OR MDX STRINGS
#TIME_PERIOD-FORECAST
OverrideDim = 'Time_Period-Forecast';

StringMDX =
IF (vTargetScenario @= 'Test2',
SubsetCreatebyMDX ( OverrideDim, ' {TM1FILTERBYLEVEL ( { DESCENDANTS ( { FILTER ( { TM1SUBSETALL ( [ ' | OverrideDim | ' ] ) } , [ ' | OverrideDim | ' ].[ PlanYear ] = "Y" ) } ) } , 0 ) } ' ) ,
SubsetCreatebyMDX ( OverrideDim, ' { TM1FILTERBYLEVEL ( { DESCENDANTS ( { FILTER ( { TM1SUBSETALL ( [ ' | OverrideDim | ' ] ) } , [ ' | OverrideDim | ' ].[FcstYears] = "Y" ) } ) } , 0 ) } ' ) ) );

IF (SubsetExists (OverrideDim, SubsetName) <> 0);
SubsetDestroy (OverrideDim, SubsetName);
SubsetCreatebyMDX (SubsetName,StringMDX);
ELSE;
SubsetCreatebyMDX (SubsetName,StringMDX);
ENDIF;

It complains that the MDX string is invalid.

Does anyone know how this can be corrected?

Thank you in advance!
Gabriele

[Wim Gielis]

For instance:

#OVERRIDE SUBSETS CREATED WITH SPECIFIC ELEMENT VALUES OR MDX STRINGS
#TIME_PERIOD-FORECAST
OverrideDim = 'Time_Period-Forecast';

IF (vTargetScenario @= 'Test2');
StringMDX =' {TM1FILTERBYLEVEL ( { DESCENDANTS ( { FILTER ( { TM1SUBSETALL ( [ ' | OverrideDim | ' ] ) } , [ ' | OverrideDim | ' ].[ PlanYear ] = "Y" ) } ) } , 0 ) } ';
Else;
StringMDX=' { TM1FILTERBYLEVEL ( { DESCENDANTS ( { FILTER ( { TM1SUBSETALL ( [ ' | OverrideDim | ' ] ) } , [ ' | OverrideDim | ' ].[FcstYears] = "Y" ) } ) } , 0 ) } ';
EndIf;

SubsetDestroy (OverrideDim, SubsetName);
SubsetCreatebyMDX (SubsetName,StringMDX);

[gabriele]

Thanks for the reply.

However, I want to select both attributes IF (vTargetScenario @= 'Test2');:

PlanYear:
StringMDX =' {TM1FILTERBYLEVEL ( { DESCENDANTS ( { FILTER ( { TM1SUBSETALL ( [ ' | OverrideDim | ' ] ) } , [ ' | OverrideDim | ' ].[ PlanYear ] = "Y" ) } ) } , 0 ) } ';

and FcstYear:

StringMDX=' { TM1FILTERBYLEVEL ( { DESCENDANTS ( { FILTER ( { TM1SUBSETALL ( [ ' | OverrideDim | ' ] ) } , [ ' | OverrideDim | ' ].[FcstYears] = "Y" ) } ) } , 0 ) } ';

in one string

and not select PlanYear if (vTargetScenario @= 'Test2');

and select FcstYear if (vTargetScenario does not equal 'Test2');

so the Else statement is only applicable if the subset does not exist, and then have TI process create it.

This may not be possible.

The code works fine when I am not using the looping technique with generic code, such as

If (vTargetSenario @='Plan',
SubsetCreatebyMDX('zzTime_Period','{TM1FILTERBYLEVEL ({DESCENDANTS ({FILTER ({TM1SUBSETALL([Time_Period])},[Time_Period].[PlanYear] = "Y")})}, 0)}'),
SubsetCreatebyMDX('zzTime_Period','{TM1FILTERBYLEVEL ({DESCENDANTS ({FILTER ({TM1SUBSETALL([Time_Period])},[Time_Period].[FcstYear] = "Y")})}, 0)}') );

I may need 2 variables when using generic code with my looping technique, because the error I get is that the StringMDX statement is invalid. I am still trying to figure out how to use a second variable.

Thank you!

[Wim Gielis]

Hi,

Check out the "Union", here:
http://www.bihints.com/book/export/html/68

Also, it might be interesting to use another variable to condense the notation.

Wigi

[gabriele]

Thank you, I will check this out.