GENERATE STatement

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

GENERATE STatement

Post by gujsa01 » Thu Mar 01, 2018 4:47 am

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,

Mark RMBC
Regular Participant
Posts: 158
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: GENERATE STatement

Post by Mark RMBC » Thu Mar 01, 2018 1:52 pm

Hi,

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

Drg
Posts: 82
Joined: Fri Aug 12, 2016 10:02 am
OLAP Product: tm1
Version: 10.2.0 - 10.3.0
Excel Version: 2010

Re: GENERATE STatement

Post by Drg » Thu Mar 01, 2018 2:09 pm

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

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

Re: GENERATE STatement

Post by gujsa01 » Thu Mar 01, 2018 3:07 pm

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.
Attachments
Generate.PNG
Generate.PNG (107.43 KiB) Viewed 714 times

Mark RMBC
Regular Participant
Posts: 158
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: GENERATE STatement

Post by Mark RMBC » Thu Mar 01, 2018 4:01 pm

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:

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

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

Re: GENERATE STatement

Post by gujsa01 » Thu Mar 01, 2018 5:18 pm

Thanks Mark. It is same name (Employee) for dimension and cube , created a new cube , works fine now.

Thanks again!

Post Reply