This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,085 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

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

User_BP7C5
User_BP7C5 Member Posts: 30 Red 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: 30 Red Ribbon
    edited Jul 6, 2020 9:30PM 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: 30 Red Ribbon
    edited Jul 6, 2020 9:30PM 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;