MDX with parameter for Attribute

[cognos4321]

Hello,

I am trying for create an MDX in a TI process but it gives me the error
"cAttrValueStart" : object not found expression.

Basically the MDX tries to filter the values in the dimension 'Time' base on a numeric attribute value.
Not sure how do I put 'cAttrValueStart' in my MDX so that it takes the value of it. There is something wrong how I am putting it in the MDX because when I just try to do ASCIIOUTPUT of it like ASCIIOUTPUT(sTargetFileName, NumberToString(cAttrValueStart) ); it gives me a value.

Please help.

cDimName = 'Time' ;
cAttr = 'Month Index Num' ;
cElNameStart = pStartPeriod ;
cElNameEnd = pEndPeriod ;
cAttrValueStart = ATTRN(cDimName, cElNameStart, cAttr) ;
cAttrValueEnd = ATTRN(cDimName, cElNameEnd, cAttr) ;

sMDX= '{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Time] )}, 0)}, [Time].[Month Index Num] >= cAttrValueStart AND [Time].[Month Index Num] <= cAttrValueEnd )}' ;


Thanks a lot.

[gtonkin]

Your sMDX is simply a string - your variable is part of that string and is not being evaluated.

Try putting percentage signs around your variables and using the Expand() function e.g.

Code: Select all

sMDX= EXPAND('{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Time] )}, 0)}, [Time].[Month Index Num] >= %cAttrValueStart% AND [Time].[Month Index Num] <= %cAttrValueEnd% )}' );

Have not tested but would recommend and AsciiOutput on sMDX to see that it is doing what you expect.
Also assuming that you have tried the MDX with values in the subset editor and it works as anticipated.

[cognos4321]

Can't thank you enough I had spent a lot of time in this and needed this urgently.
Thank you so much. It works perfect.

Just for my knowledge sake I have 1 more question if you have time to answer.
Why we didn't need to use Expand in the below MDX since here also we are using a parameter for Element name.
cElName = pYear ;
sMDX = '{ TM1FILTERBYLEVEL({ DESCENDANTS([' | cDimName |'].['| cElName |']) }, 0) }' ;

[gtonkin]

You were joining variables to the string in your new example. This is seen where the string is closed off with the apostrophe and variable joined using the pipes (vertical bars)

Either way will combine the value of the variable into the string.

[cognos4321]

I tried joining with pipes earlier in the MDX with attributes but it didn't work.
Anyways, Expand works perfect . Thank you.

[gtonkin]

Expand may be preferable as you should not need to convert numerics to strings first, as you would need to for joining with pipes into a string variable.

[cognos4321]

ok thank you