CrossJoin - Is there a way to exclude a member from results?

User_BP7C5

HI,

Appreciate any help or suggestions.

I'm doing an allocation ASO MaxL script.  However, I have a member that has a formula on it and need to exclude it.  Problem is 'BalanceSheet' is an Ancestor of it.  Is there a way to exclude it in my CrossJoin statement?

Basically I want to include all Lev0 members of BalanceSheet EXCEPT AC_322000101 as it has a formula on it:

Error:

   ERROR - 1260052 - Syntax error in input MDX query on line 11 at token 'Account.AC_322000101' .

   ERROR - 1241192 - Allocation terminated with Essbase error 1260052 in POV.

My Code I'm trying.  Not sure if 'Except' is correct or not:

execute allocation process on database APP.DB with

pov

"                                                           

CrossJoin(

CrossJoin(

CrossJoin(

CrossJoin(

CrossJoin(

CrossJoin(

CrossJoin(

{Descendants([Entity].TotalEMEA,10,LEAVES)},

{Except(Descendants([BalanceSheet],ACCOUNT.Levels(0),[Account].[AC_322000101]))}),

{Descendants([Size].[Size],10,LEAVES)}),

{Descendants([Customer].[Total_Customers],10,LEAVES)}),

{Descendants([Type].[Total_Types],10,LEAVES)}),

{[GLAmt]}),

{[&Period]}),

{[&Year]})"

amount "([Periodic - EUR])"

amountcontext "([FC])"

target "([Periodic])"

range "{([EUR])}"

spread;

User_BP7C5

Resolved. and added filter to skip Shared Member too

Thanks Pete at https://www.pivot2.com.au/

execute allocation process on database App.Db with

pov

"                                                           

CrossJoin(

CrossJoin(

CrossJoin(

CrossJoin(

CrossJoin(

CrossJoin(

CrossJoin(

{Descendants([Entity].TotEMEA,10,LEAVES)},

{Filter(Descendants([Account].[Balance Sheet],20,LEAVES),

[ACCOUNT].CurrentMember.Member_Type <>2

AND NOT Account.CurrentMember.Shared_Flag)}),

{Descendants([Size].[Size],10,LEAVES)}),

{Descendants([Customer].[Total_Customers],10,LEAVES)}),

{Descendants([Type].[Total_Types],10,LEAVES)}),

{[GL_Load]}),

{[&Load_Period]}),

{[&Load_Year]})"

amount "([Periodic - EUR])"

amountcontext "([FC])"

target "([Periodic])"

range "{([EUR])}"

spread;

User_BP7C5

Resolved. and added filter to skip Shared Member too

Thanks Pete at https://www.pivot2.com.au/

execute allocation process on database App.Db with

pov

"                                                           

CrossJoin(

CrossJoin(

CrossJoin(

CrossJoin(

CrossJoin(

CrossJoin(

CrossJoin(

{Descendants([Entity].TotEMEA,10,LEAVES)},

{Filter(Descendants([Account].[Balance Sheet],20,LEAVES),

[ACCOUNT].CurrentMember.Member_Type <>2

AND NOT Account.CurrentMember.Shared_Flag)}),

{Descendants([Size].[Size],10,LEAVES)}),

{Descendants([Customer].[Total_Customers],10,LEAVES)}),

{Descendants([Type].[Total_Types],10,LEAVES)}),

{[GL_Load]}),

{[&Load_Period]}),

{[&Load_Year]})"

amount "([Periodic - EUR])"

amountcontext "([FC])"

target "([Periodic])"

range "{([EUR])}"

spread;