Variable/Parameters in MDX

[WayneBo]

Hi All:

I am creating a dynamic subset on my time dimension by MDX.

SubsetCreatebyMDX('MDXSet','{intersect(
Filter( TM1SubsetAll( [ContinuousTime] ), [ContinuousTime].[PreviousNumber] >= '|vEndNo|' ),
Filter( TM1SubsetAll( [ContinuousTime] ), [ContinuousTime].[PreviousNumber] <= '|vStartNo|' ) ) } ' ,0 )

I also have 2 variables vStartNo and vEndNo, which will get the relevant attributes from Time Dimension, those attributes are in number format.

vStartNo = ATTRN('Continuous Time', vTodayAU, 'StartNumber');
vEndNo= vStartNo - 21;

But when I pass the variable into the MDX the process says syntax error on or before:" vEndNo|' )Filter ( Invalid operator ".

I guess the problem is at where the variables been signed into MDX, ( >= '|vEndNo|' )
Could any expert provide hint.
Very much appreciated.
W

[gtonkin]

Looks like vStartNo and vEndNo are numeric values and you are trying to join them to a string.
You will need to convert them to string first e.g. NumberToString(vStartNo).

p.s. have not looked too deeply at the rest of the MDX for validity.

[PavoGa]

If you were to use EXPAND instead of string concatenations, the numeric values are handled. For example:

Code: Select all

sMDX = EXPAND('intersect(
    Filter( TM1SubsetAll( [ContinuousTime] ), [ContinuousTime].[PreviousNumber] >= %vEndNo% ),
    Filter( TM1SubsetAll( [ContinuousTime] ), [ContinuousTime].[PreviousNumber] <= %vStartNo% ) )');
SubsetCreate('MDXset', sMDX, 0);

An alternative MDX statement would be this, which may be a bit faster in execution:

Code: Select all

sMDX = EXPAND('Filter( TM1SubsetAll( [ContinuousTime] ), 
        [ContinuousTime].[PreviousNumber] >= %vEndNo% AND
        [ContinuousTime].[PreviousNumber] <= %vStartNo% )');

[WayneBo]

Hi:

Thank you guys for the help.

Great tips and helps.

Very much appreciated.

Best Regards
W