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.
TM1Subsettoset help
- PavoGa
- MVP
- Posts: 618
- Joined: Thu Apr 18, 2013 6:59 pm
- OLAP Product: TM1
- Version: 10.2.2 FP7, PA2.0.9.1
- Excel Version: 2013 PAW
- Location: Charleston, Tennessee
Re: TM1Subsettoset help
This mdx: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.
Code: Select all
FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Employee] )}, 0)},
[Employee].(TM1subsettoset([Cost Center],"nlevel"),[Version-Scenario].[Plan],[m_EmpCCAnnual].[Employee Category])="Exempt")
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"))
Ty
Cleveland, TN
Cleveland, TN
Re: TM1Subsettoset help
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"))
)
}
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"))
)
}
- PavoGa
- MVP
- Posts: 618
- Joined: Thu Apr 18, 2013 6:59 pm
- OLAP Product: TM1
- Version: 10.2.2 FP7, PA2.0.9.1
- Excel Version: 2013 PAW
- Location: Charleston, Tennessee
Re: TM1Subsettoset help
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.
Do it like this:
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"))
)
}
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
Cleveland, TN