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)} )');
SubsetCreatebyMDX Union 3 elements or more
-
- MVP
- Posts: 1815
- Joined: Mon Dec 05, 2011 11:51 am
- OLAP Product: Cognos TM1
- Version: PA2.0 and most of the old ones
- Excel Version: All of em
- Location: Manchester, United Kingdom
- Contact:
Re: SubsetCreatebyMDX Union 3 elements or more
Hi,
Union can only handle the joining of 2 statements. But you can nest Unions.
e.g.
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:
You can then drill down into those:
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.
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)}
)}
)}
Code: Select all
{[' | vDim1 | '].[' | vEl | '], [' | vDim1 | '].[' | vEl2 | '], [' | vDim1 | '].[' | vEl3 | '] }
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.
Last edited by declanr on Tue Sep 01, 2020 3:11 pm, edited 2 times in total.
Declan Rodger
-
- Community Contributor
- Posts: 287
- Joined: Fri Feb 15, 2013 5:49 pm
- OLAP Product: TM1
- Version: PA 2.0.9.1
- Excel Version: 365
- Location: Minneapolis, USA
Re: SubsetCreatebyMDX Union 3 elements or more
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]}
)}
-
- MVP
- Posts: 3652
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: SubsetCreatebyMDX Union 3 elements or more
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} }
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.