GENERATE STatement

[gujsa01]

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]

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]

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]

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.

[Mark RMBC]

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]

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

Thanks again!