Page 1 of 1
T.I. SubsetCreatebyMDX variable
Posted: Wed Sep 21, 2011 10:19 am
by 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 ?
Re: T.I. SubsetCreatebyMDX variable
Posted: Wed Sep 21, 2011 10:23 am
by 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.
Re: T.I. SubsetCreatebyMDX variable
Posted: Wed Sep 21, 2011 11:01 am
by mw20000
thanks a million, you saved my life
Re: T.I. SubsetCreatebyMDX variable
Posted: Thu Nov 17, 2011 9:40 am
by 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
Re: T.I. SubsetCreatebyMDX variable
Posted: Thu Nov 17, 2011 9:55 am
by 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.
Re: T.I. SubsetCreatebyMDX variable
Posted: Thu Nov 17, 2011 10:11 am
by 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.
Re: T.I. SubsetCreatebyMDX variable
Posted: Thu Nov 17, 2011 10:20 am
by mags88
Thanks for clarifying
Re: T.I. SubsetCreatebyMDX variable
Posted: Thu Nov 17, 2011 10:28 am
by 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.