Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.4K Intelligent Advisor
- 75 Insurance
- 537.7K On-Premises Infrastructure
- 138.7K Analytics Software
- 38.6K Application Development Software
- 6.1K Cloud Platform
- 109.6K Database Software
- 17.6K Enterprise Manager
- 8.8K Hardware
- 71.3K Infrastructure Software
- 105.4K Integration
- 41.6K Security Software
CrossJoin - Is there a way to exclude a member from results?

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;
Best 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
-
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;