SubsetCreatebyMDX in combination with parameters

[MathiasBeckers]

Hi,

Quick question on the use of the SubsetCreatebyMDX function in TI processes: Is it possible in some way to include parameters from your process in the create statement?
For example: I want my user to be able to indicate a certain (consolidated) element in the parameters of the process and have the process create a subset taking all level 0 children of that selected element.

I tried: SubsetCreateByMDX (vSub, '{TM1FILTERBYLEVEL( { DRILLDOWNMEMBER( {TM1SubsetBasis()}, { [Dimension].[pConsolidatedLevelElement] }) }, 0)}' ); , with pConsolidatedLevelElement being the parameter but this returns a process aborted and an error indicating he cannot resolve this statement or register the subset.
So just for my information, is what I'm trying to do even possible and if so, how can I model this in the process?

Thanks,
Mathias.

[jrizk]

Try

SubsetCreateByMDX (vSub, '{TM1FILTERBYLEVEL( { DRILLDOWNMEMBER( {TM1SubsetBasis()}, { [Dimension].[' | pConsolidatedLevelElement | '] }) }, 0)}' );

[MathiasBeckers]

Jrizk,

Thanks for your reply. I've tried your suggestions but I still receive an 'unable to resolve TM1 subset expression' (see attached screenshot).
The good news however is that in the expression in the error, you can see that he has replaced the parameter with the correct value from the prompt.
(There are some other manipulations going on but in general it should work, I've tried it in the expression window of the subset).

Do you see any other problem?

Thanks,
Mathias

[Michel Zijlema]

Hi Mathias,

i think your problem is with the TM1SubsetBasis() function. You should replace this with the actual 'subset base' selection (probably [Kostenplaats].[101 - Staf Onderwijs]).

Michel

[jrizk]

Hi.

Michel is right - it's the TM1SubsetBasis that's a problem - sorry didn't notice that.

Looks like you want the 0 level of the consolidation '101 - Staf Onderwijs' in the dimension 'Kostenplaats'. Try the following - just substitute what you need to in the first 3 lines:

DimName = 'Kostenplaats';
SubName = 'Test';
SubConsol = '101 - Staf Onderwijs';
If ( SubsetExists (DimName, SubName) = 1 );
SubsetDestroy (DimName, SubName );
EndIf;
SubMDX = '{TM1FILTERBYLEVEL( TM1DRILLDOWNMEMBER( { [ ' | DimName | '].[' | SubConsol | '] }, ALL, RECURSIVE ) , 0)}';
SubsetCreatebyMDX (SubName, SubMDX );

[BigG]

I find the TM1SubsetBasis() issue occurs when you record MDx in subset editor and dont select all at start. I also find it a problem when I record MDX and have mutiple hirearchies. As long as you are aware of this issue you can review the recorded expression and modify.