Forum Stats

  • 3,768,296 Users
  • 2,252,772 Discussions
  • 7,874,520 Comments

Discussions

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

User_BP7C5
User_BP7C5 Member Posts: 29 Green Ribbon
edited Jul 6, 2020 9:30PM in Planning and Budgeting

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;

Tagged:

Best Answer

  • User_BP7C5
    User_BP7C5 Member Posts: 29 Green Ribbon
    edited Jul 6, 2020 9:30PM Accepted Answer

    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;

Answers

  • User_BP7C5
    User_BP7C5 Member Posts: 29 Green Ribbon
    edited Jul 6, 2020 9:30PM Accepted Answer

    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;