Hi,
Appreciate any help on the following.
Cube 1: CostCenterSelection
Dimensions: Cost Center, Version, Measures (SelectCC as string with value Yes/No)
MDX to filter Cost Centers that have "SelectCC" measure value "Yes":
FILTER(TM1FILTERBYLEVEL( TM1SUBSETALL( [Cost Center] ), 0),[CostCenterSelection].([Version].[Plan],[m_CCEmpSelection].[SelectCC])="Yes")
Cube 2: Employee
Dimensions: Cost Center, Employee, Version, Measures ( ShowEmp as Simple.)
MDX to filter Employees in Employee cube where "ShowEmp" = 1 and every Cost Center within "Total Cost Center" hierarchy:
GENERATE(
{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {TM1FILTERBYPATTERN( {TM1SUBSETALL( [Cost Center] )}, "Total Cost Center")}, ALL, RECURSIVE )}, 0)},
FILTER(TM1FILTERBYLEVEL( TM1SUBSETALL( [Employee] ), 0),[Employee].([Cost Center].currentmember,[Version].[Plan],[m_EmpCCAnnual].[ShowEmp])=1))
)
These MDXs work fine.
I'm trying to pass all Cost Centers which have "SelectCC" measure value = "Yes" from Cube 1 to Cube 2, which doesn't work:
GENERATE(
FILTER(TM1FILTERBYLEVEL( TM1SUBSETALL( [Cost Center] ), 0),[CostCenterSelection].([Version].[Plan],[m_CCEmpSelection].[SelectCC])="Yes")),
{FILTER(TM1FILTERBYLEVEL( TM1SUBSETALL( [Employee] ), 0),[Employee].([Cost Center].currentmember,[Version-Scenario].[Plan],[m_EmpCCAnnual].[ShowEmp])=1)})
BUT the following works if I pass first argument of GENERATE as dynamic subset instead :
GENERATE(
TM1SUBSETTOSET([Cost Center], "subset1"),
{FILTER(TM1FILTERBYLEVEL( TM1SUBSETALL( [Employee] ), 0),[Employee].([Cost Center].currentmember,[Version].[Plan],[m_EmpCCAnnual].[ShowEmp])=1)})
Subset1 MDX: FILTER(TM1FILTERBYLEVEL( TM1SUBSETALL( [Cost Center] ), 0),[CostCenterSelection].([Version].[Plan],[m_CCEmpSelection].[SelectCC])="Yes"))
Please advise..
Thanks,
GENERATE STatement
-
- Community Contributor
- Posts: 292
- Joined: Tue Sep 06, 2016 7:55 am
- OLAP Product: TM1
- Version: 10.1.1
- Excel Version: Excel 2010
Re: GENERATE STatement
Hi,
when you say this doesn't work,
Do you mean it returns the wrong elements or an actual syntax error?
I would expect the above to error because there are too may brackets in the first part. So where the code says [SelectCC])="Yes")), I would think needs to say [SelectCC])="Yes")
cheers, Mark
when you say this doesn't work,
Code: Select all
GENERATE(
FILTER(TM1FILTERBYLEVEL( TM1SUBSETALL( [Cost Center] ), 0),[CostCenterSelection].([Version].[Plan],[m_CCEmpSelection].[SelectCC])="Yes")),
{FILTER(TM1FILTERBYLEVEL( TM1SUBSETALL( [Employee] ), 0),[Employee].([Cost Center].currentmember,[Version-Scenario].[Plan],[m_EmpCCAnnual].[ShowEmp])=1)})
I would expect the above to error because there are too may brackets in the first part. So where the code says [SelectCC])="Yes")), I would think needs to say [SelectCC])="Yes")
cheers, Mark
-
- Regular Participant
- Posts: 159
- Joined: Fri Aug 12, 2016 10:02 am
- OLAP Product: tm1
- Version: 10.2.0 - 10.3.0
- Excel Version: 2010
Re: GENERATE STatement
HI
you do not use dimension in subset for another dimension.
For Dimension Cost Center
you do not use dimension in subset for another dimension.
For Dimension Cost Center
GENERATE(
FILTER(TM1FILTERBYLEVEL( TM1SUBSETALL( [Cost Center] ), 0),[CostCenterSelection].([Version].[Plan],[m_CCEmpSelection].[SelectCC])="Yes")),
{FILTER(TM1FILTERBYLEVEL( TM1SUBSETALL( [Employee] ), 0),[Employee].([Cost Center].currentmember,[Version-Scenario].[Plan],[m_EmpCCAnnual].[ShowEmp])=1)})
Re: GENERATE STatement
Mark:
Extra ")" was a typo. I don't see employee list filtered from this MDX.
DRG:
Intent with TM1subsetall on Employee dimension is to include all N-level employees.
In the attached, I should see E3 filtered as a result.
Also replaced Version with Version-Scenario as is defined in my model.
Extra ")" was a typo. I don't see employee list filtered from this MDX.
DRG:
Intent with TM1subsetall on Employee dimension is to include all N-level employees.
In the attached, I should see E3 filtered as a result.
Also replaced Version with Version-Scenario as is defined in my model.
- Attachments
-
- Generate.PNG (107.43 KiB) Viewed 4176 times
-
- Community Contributor
- Posts: 292
- Joined: Tue Sep 06, 2016 7:55 am
- OLAP Product: TM1
- Version: 10.1.1
- Excel Version: Excel 2010
Re: GENERATE STatement
Hi,
I have created a test cube and used mdx to return any employees whose ProRataDate is 1 in the test cube and the status of the Cost Centre is preparing in the status cube and it seems to be working. Code is below:
Looking at your code can't see what is wrong with it given the information provided. Could the fact that employee cube is same name as employee dimension be an issue. Can you try creating another cube not called employee?
Or are you missing any dimensions from either cube or have any dimension orders incorrect? Though if your other mdx code works then I can't see that being the issue!
cheers, Mark
I have created a test cube and used mdx to return any employees whose ProRataDate is 1 in the test cube and the status of the Cost Centre is preparing in the status cube and it seems to be working. Code is below:
Code: Select all
GENERATE(
FILTER(
TM1FILTERBYLEVEL(
TM1SUBSETALL(
[F_CostCentre] ), 0),
[usr_CCStatus].([EB_StatusMeasure].[Status_Description])="Preparing"),
FILTER(
TM1FILTERBYLEVEL(
TM1SUBSETALL([F_Employee] ), 0),
[ZZTest].(
[F_CostCentre].currentmember,[EB_WFPMeasures].[ProRataDates])=1)
)
Or are you missing any dimensions from either cube or have any dimension orders incorrect? Though if your other mdx code works then I can't see that being the issue!
cheers, Mark
Re: GENERATE STatement
Thanks Mark. It is same name (Employee) for dimension and cube , created a new cube , works fine now.
Thanks again!
Thanks again!