TM1Subsettoset help

Post Reply
gujsa01
Posts: 20
Joined: Wed Jan 07, 2009 7:02 pm

TM1Subsettoset help

Post by gujsa01 » 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.

User avatar
PavoGa
Community Contributor
Posts: 227
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0
Excel Version: 2013
Location: Cleveland, Tennessee

Re: TM1Subsettoset help

Post by PavoGa » Mon Feb 19, 2018 5:14 pm

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.
Ty
Cleveland, TN

gujsa01
Posts: 20
Joined: Wed Jan 07, 2009 7:02 pm

Re: TM1Subsettoset help

Post by gujsa01 » Mon Feb 19, 2018 7:25 pm

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"))
)
}

User avatar
PavoGa
Community Contributor
Posts: 227
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0
Excel Version: 2013
Location: Cleveland, Tennessee

Re: TM1Subsettoset help

Post by PavoGa » Tue Feb 20, 2018 1:06 pm

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" ))
Ty
Cleveland, TN

Post Reply