T.I. SubsetCreatebyMDX variable

[mw20000]

Hi there,

i want to add elements to subset by using SubsetcreatebyMDX within a T.I. process.
i have a variable sPeriod = '2011*'
when i use

SubsetCreatebyMDX(ThisSubset, '{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Period] )}, 0)}, ' | sPeriod | ' )} ' );

i get an error: Syntax error at or near: ' )} ', character position 79 expression: {TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Period] )}, 0)}, 2011* )}

but when i use it w/o a variable it works fine: SubsetCreatebyMDX(ThisSubset, '{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Period] )}, 0)}, "2011*")} ');

any ideas ?

[qml]

Change this:

Code: Select all

SubsetCreatebyMDX(ThisSubset, '{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Period] )}, 0)}, ' | sPeriod | ' )} ' );

to this:

Code: Select all

SubsetCreatebyMDX(ThisSubset, '{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Period] )}, 0)}, "' | sPeriod | '" )} ' );

You need the double quotes around your pattern string.

[mw20000]

thanks a million, you saved my life

[mags88]

Hi,

I would like to create a dynamic subset by using a T.I.

I understand that you need to use the function "subsetcreatebyMDX()"

I currently have a dynamic subset for one my dimensions that was created by recording the expression within the subset editor, but when I use this expression as the MDX expression in the above function the T.I. fails.

Name of dynamic subset I wish to create "Subset_A"
Name of dimension in question "Cost_Comp"
Name of Element Attribute "Active_Cost"
The expression filters the list of elements to only show the ones that have the value "Active" for the element attribute "Active_Cost"

Below is my T.I. code

Code: Select all

subsetcreatebymdx('Subset_A", '{FILTER( {TM1SubsetBasis()}, [Cost_Comp].[Active_Cost] = "Active")}');

What do I need to add to subsetcreatebymdx for this expression to work?

Regards,
Magnus

[mags88]

I changed it to this,

Code: Select all

SubsetCreateByMdx('Subset_A', '{FILTER( {TM1SUBSETALL( [Cost_Comp] )}, [Cost_Comp].[Active_Costs] = "Active" )}');

and it has worked. Not sure what is different other than swapping TM1SUBSETALL with TM1SubsetBasis.

[qml]

TM1SubsetBasis only works in Subset Editor, it won't work when creating a dynamic subset from TI.

Also, in your earlier code you have a double quote at the end of your subset name, which would not work either.

[mags88]

Thanks for clarifying

[qml]

To further clarify - TM1SubsetBasis is not even telling TM1 which dimension you have in mind (just look at your first syntax, how is the engine meant to "know" which dimension you want to create this subset for?). That's why it only works in Subset Editor as it works in the context of the dimension you opened the SE window for.