Page 1 of 1

TM1Subsettoset help

Posted: Mon Feb 19, 2018 4:08 pm
by gujsa01
Hi,

I have a 4 dimension cube Employee:
Cost Center
Version
Employee
Measures.

In an Active Form, using the following MDX expresssion, when I filter Employees on any measure in Employee cube by specifying value from each dimension, query displays correct employee count and names.
For e.g. FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Employee] )}, 0)},
[Employee].([Cost Center].[12345],[Version-Scenario].[Plan],[m_EmpCCAnnual].[Employee Category])="Exempt")
Cost Center 12345 is N level value.

I'm trying to filter all employees for C level Cost Center trying this:
For e.g.: FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Employee] )}, 0)},
[Employee].(TM1subsettoset([Cost Center],"nlevel"),[Version-Scenario].[Plan],[m_EmpCCAnnual].[Employee Category])="Exempt")
Errors out with Invalid character.

Is there a way to pass subset name to filter from as above without TI process?

Appreciate any help.

Re: TM1Subsettoset help

Posted: Mon Feb 19, 2018 5:14 pm
by PavoGa
gujsa01 wrote: Mon Feb 19, 2018 4:08 pm Hi,

I have a 4 dimension cube Employee:
Cost Center
Version
Employee
Measures.

In an Active Form, using the following MDX expresssion, when I filter Employees on any measure in Employee cube by specifying value from each dimension, query displays correct employee count and names.
For e.g. FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Employee] )}, 0)},
[Employee].([Cost Center].[12345],[Version-Scenario].[Plan],[m_EmpCCAnnual].[Employee Category])="Exempt")
Cost Center 12345 is N level value.

I'm trying to filter all employees for C level Cost Center trying this:
For e.g.: FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Employee] )}, 0)},
[Employee].(TM1subsettoset([Cost Center],"nlevel"),[Version-Scenario].[Plan],[m_EmpCCAnnual].[Employee Category])="Exempt")
Errors out with Invalid character.

Is there a way to pass subset name to filter from as above without TI process?

Appreciate any help.
This mdx:

Code: Select all

FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Employee] )}, 0)},
                        [Employee].(TM1subsettoset([Cost Center],"nlevel"),[Version-Scenario].[Plan],[m_EmpCCAnnual].[Employee Category])="Exempt")
will not work because the query against the [Employee] cube in the FILTER requires members, not sets. Something like this:

Code: Select all

GENERATE(TM1SUBSETTOSET([Cost Center], "nLevel"),
FILTER(TM1FILTERBYLEVEL( TM1SUBSETALL( [Employee] ), 0),
                        [Employee].([Cost Center].currentmember,[Version-Scenario].[Plan],[m_EmpCCAnnual].[Employee Category])="Exempt"))
should work. GENERATE will take a subset and use every member of that subset to evaluate in the subsequent FILTER.

Re: TM1Subsettoset help

Posted: Mon Feb 19, 2018 7:25 pm
by gujsa01
Thanks! This worked , also I added another requirement which worked as well.

Here pick only valid employees for that cost center by resolving all cost centers. For each Cost Center read , check if Emp is valid an Home Country is Germany. Thanks again!!!
{
INTERSECT
(
GENERATE(TM1SUBSETTOSET([Cost Center], "nLevel"),
FILTER(TM1FILTERBYLEVEL( TM1SUBSETALL( [Employee] ), 0),[Employee].([Cost Center].currentmember,[Version-Scenario].[Plan],[m_EmpCCAnnual].[ShowEmp])=1)),
GENERATE(TM1SUBSETTOSET([Cost Center], "nLevel"),
FILTER(TM1FILTERBYLEVEL( TM1SUBSETALL( [Employee] ), 0),[Employee].([Cost Center].currentmember,[Version-Scenario].[Plan],[m_EmpCCAnnual].[Home Country])="GER"))
)
}

Re: TM1Subsettoset help

Posted: Tue Feb 20, 2018 1:06 pm
by PavoGa
GENERATE can have some performance issues because it is running queries for each member of the set in the filter. In your solution, you're running GENERATE twice, plus the intersect. I believe that logically, the second set of code below accomplishes the same thing in a single pass through the nLevel subset.

Code: Select all

{ 
 INTERSECT
 (
 GENERATE(TM1SUBSETTOSET([Cost Center], "nLevel"),
 FILTER(TM1FILTERBYLEVEL( TM1SUBSETALL( [Employee] ), 0),[Employee].([Cost Center].currentmember,[Version-Scenario].[Plan],[m_EmpCCAnnual].[ShowEmp])=1)),
 GENERATE(TM1SUBSETTOSET([Cost Center], "nLevel"),
 FILTER(TM1FILTERBYLEVEL( TM1SUBSETALL( [Employee] ), 0),[Employee].([Cost Center].currentmember,[Version-Scenario].[Plan],[m_EmpCCAnnual].[Home Country])="GER"))
 )
 }
Do it like this:

Code: Select all

 
 GENERATE(TM1SUBSETTOSET([Cost Center], "nLevel"),
 FILTER(TM1FILTERBYLEVEL( TM1SUBSETALL( [Employee] ), 0),
    [Employee].([Cost Center].currentmember, [Version-Scenario].[Plan], [m_EmpCCAnnual].[ShowEmp]) = 1 AND
    [Employee].([Cost Center].currentmember, [Version-Scenario].[Plan], [m_EmpCCAnnual].[Home Country]) = "GER" ))