Page 1 of 1
SubsetCreatebyMDX Union 3 elements or more
Posted: Tue Sep 01, 2020 2:44 pm
by 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)} )');
Re: SubsetCreatebyMDX Union 3 elements or more
Posted: Tue Sep 01, 2020 3:09 pm
by 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.
Re: SubsetCreatebyMDX Union 3 elements or more
Posted: Tue Sep 01, 2020 3:10 pm
by 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]}
)}
Re: SubsetCreatebyMDX Union 3 elements or more
Posted: Tue Sep 01, 2020 8:25 pm
by lotsaram
Markuss wrote: ↑Tue Sep 01, 2020 2:44 pm
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} }