SubsetCreatebyMDX Union 3 elements or more

[Markuss]

Hi All,

Wonder if someone know how to union 3 elements (or more if needed) in given code? Works with two elements perfect. Does not work when added 3rd element.

Thanks in advance


SubsetCreatebyMDX( vSubset, '{UNION(
{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( { [ ' | vDim1 | ' ].[ ' | vEl | ' ] }, ALL, RECURSIVE )}, 0)},
{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( { [ ' | vDim1 | ' ].[ ' | vEl2 | ' ] }, ALL, RECURSIVE )}, 0)},
{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( { [ ' | vDim1 | ' ].[ ' | vEl3 | ' ] }, ALL, RECURSIVE )}, 0)} )');

[declanr]

Hi,

Union can only handle the joining of 2 statements. But you can nest Unions.

e.g.

Code: Select all

{UNION(
	{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( { [ ' | vDim1 | ' ].[ ' | vEl | ' ] }, ALL, RECURSIVE )}, 0)},
	{Union ( 
		{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( { [ ' | vDim1 | ' ].[ ' | vEl2 | ' ] }, ALL, RECURSIVE )}, 0)},
		{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( { [ ' | vDim1 | ' ].[ ' | vEl3 | ' ] }, ALL, RECURSIVE )}, 0)} 
	)}
)}

And in the case you highlighted you could actually avoid a union altogether by using commas to first create a set of the 3 elements:

Code: Select all

{[' | vDim1 | '].[' | vEl | '], [' | vDim1 | '].[' | vEl2 | '], [' | vDim1 | '].[' | vEl3 | '] }

You can then drill down into those:

Code: Select all

{TM1FILTERBYLEVEL( 
	{TM1DRILLDOWNMEMBER( 
		{[' | vDim1 | '].[' | vEl | '], [' | vDim1 | '].[' | vEl2 | '], [' | vDim1 | '].[' | vEl3 | '] }, 
		ALL, RECURSIVE 
	)}, 
	0
)}

I would also recommend looking at the Expand function, it can make it a bit neater and more readable to construct statements in a TI that have a lot of variables.

[ascheevel]

UNION is for joining 2 sets and 2 sets only AFAIK. You can nest a UNION though:

Code: Select all

{UNION(
	{UNION({[set 1]}, {[set 2]})}
	,{[set 3]}
)}

[lotsaram]

Wonder if someone know how to union 3 elements (or more if needed) in given code? Works with two elements perfect. Does not work when added 3rd element.

The Union function can only merge 2 sets. But MDX also supports a union operator "+" which you can use as shorthand instead of {Union()}.
e.g.
{ {set A} + {set B} + {set C} }