BPC Logic Script: Erroк from calculated members

[former_member232600]

I need calculate this account's

([ACCOUNT].[10001210000000],[FLOW].[109],[PARTNER].[I_ALL])-([ACCOUNT].[10001210305000],[FLOW].[101],[PARTNER].[I_1000])-([ACCOUNT].[10001210305000],[FLOW].[106],[PARTNER].[I_1000])-([ACCOUNT].[10001210305000],[FLOW].[132],[PARTNER].[I_1000])-([ACCOUNT].[10001210605000],[FLOW].[101],[PARTNER].[I_1000])-([ACCOUNT].[10001210605000],[FLOW].[106],[PARTNER].[I_1000])-([ACCOUNT].[10001210605000],[FLOW].[132],[PARTNER].[I_1000])+([ACCOUNT].[10001220032300],[FLOW].[999],[PARTNER].[I_ALL])-([ACCOUNT].[10001220032300],[FLOW].[109],[PARTNER].[I_ALL])-([ACCOUNT].[10001220032300],[FLOW].[116],[PARTNER].[I_ALL])-([ACCOUNT].[10001220032300],[FLOW].[117],[PARTNER].[I_ALL])-([ACCOUNT].[10001220032300],[FLOW].[149],[PARTNER].[I_ALL])-([ACCOUNT].[10001220032300],[FLOW].[999],[PARTNER].[I_1000])+([ACCOUNT].[10001220090000],[FLOW].[999],[PARTNER].[I_ALL])-([ACCOUNT].[10001220090000],[FLOW].[109],[PARTNER].[I_ALL])-([ACCOUNT].[10001220120000],[FLOW].[149],[PARTNER].[I_ALL]), ACCOUNT = 70000000000011)

I try to use this constuction on Sqript logic, and had error

*XDIM_MEMBERSET PERIOD = %PERIOD_SET% *XDIM_MEMBERSET VERSION = %VERSION_SET% *XDIM_MEMBERSET CONS_GROUP = %CONS_GROUP_SET% *XDIM_MEMBERSET ENTITY=%ENTITY_SET% *XDIM_MEMBERSET ACCOUNT AS %ACLIST%=BAS(10000000000000) *XDIM_ADDMEMBERSET ACCOUNT = 70000000000014 *XDIM_MEMBERSET FLOW = BAS(999) *FOR %AC% = %ACLIST% *WHEN ACCOUNT *IS %AC% *REC( EXPRESSION = (([ACCOUNT].[10001210000000],[FLOW].[109],[PARTNER].[I_ALL])-([ACCOUNT].[10001210305000],[FLOW].[101],[PARTNER].[I_1000])-([ACCOUNT].[10001210305000],[FLOW].[106],[PARTNER].[I_1000])-([ACCOUNT].[10001210305000],[FLOW].[132],[PARTNER].[I_1000])-([ACCOUNT].[10001210605000],[FLOW].[101],[PARTNER].[I_1000])-([ACCOUNT].[10001210605000],[FLOW].[106],[PARTNER].[I_1000])-([ACCOUNT].[10001210605000],[FLOW].[132],[PARTNER].[I_1000])+([ACCOUNT].[10001220032300],[FLOW].[999],[PARTNER].[I_ALL])-([ACCOUNT].[10001220032300],[FLOW].[109],[PARTNER].[I_ALL])-([ACCOUNT].[10001220032300],[FLOW].[116],[PARTNER].[I_ALL])-([ACCOUNT].[10001220032300],[FLOW].[117],[PARTNER].[I_ALL])-([ACCOUNT].[10001220032300],[FLOW].[149],[PARTNER].[I_ALL])-([ACCOUNT].[10001220032300],[FLOW].[999],[PARTNER].[I_1000])+([ACCOUNT].[10001220090000],[FLOW].[999],[PARTNER].[I_ALL])-([ACCOUNT].[10001220090000],[FLOW].[109],[PARTNER].[I_ALL])-([ACCOUNT].[10001220120000],[FLOW].[149],[PARTNER].[I_ALL])), ACCOUNT = 70000000000011) *ENDWHEN *NEXT *COMMIT

[former_member186338 · Accepted solution]

Just to explain you the idea of correct script (simple)

You want to store the:

[ACCOUNT].[10001210000000],[FLOW].[109],[PARTNER].[I_ALL]

Where: 10001210000000 - parent, 109 - base, I_ALL - parent

into:

[ACCOUNT].[70000000000014],[FLOW].[114],[PARTNER].[I_XXX]

70000000000014 - base, 114 - base, I_XXX - base

Script will be:

*WHEN ACCOUNT
*IS BAS(10001210000000)
*WHEN FLOW
*IS 109
*WHEN PARTNER
*IS BAS(I_ALL)
*REC(EXPRESSION=%VALUE%,ACCOUNT=70000000000014,FLOW = 114,PARTNER=I_XXX)
*ENDWHEN
*ENDWHEN
*ENDWHEN

[former_member232600 · Accepted solution]

That is, I should like to do for each node?

eg

*WHEN ACCOUNT
*IS BAS(10001210000000)
*WHEN FLOW
*IS 109
*WHEN PARTNER
*IS BAS(I_ALL)
*REC(EXPRESSION=%VALUE%,ACCOUNT=70000000000014,FLOW = 114,PARTNER=I_XXX)
*ENDWHEN
*ENDWHEN
*ENDWHEN

*COMMIT
*WHEN ACCOUNT

*IS BAS(10001220032300)

*WHEN FLOW
*IS 109
*WHEN PARTNER
*IS BAS(I_ALL)
*REC(EXPRESSION=%VALUE%,ACCOUNT=70000000000014,FLOW = 114,PARTNER=I_XXX)
*ENDWHEN
*ENDWHEN
*ENDWHEN

[former_member186338 · Accepted solution]

You have to understand relationship between nodes and base members and create your script for correct sets of base members.

Don't use COMMIT!!!!!

[former_member186338 · Accepted solution]

Correct:

*WHEN ACCOUNT
*IS BAS(10001210000000)
*WHEN FLOW
*IS 109
*WHEN PARTNER
*IS BAS(I_ALL)
*REC(EXPRESSION=%VALUE%,ACCOUNT=70000000000014,FLOW = 114,PARTNER=I_XXX)
*ENDWHEN
*ENDWHEN
*IS BAS(10001220032300)
*WHEN FLOW
*IS 109
*WHEN PARTNER
*IS BAS(I_ALL)
*REC(EXPRESSION=%VALUE%,ACCOUNT=70000000000014,FLOW = 114,PARTNER=I_XXX)
*ENDWHEN
*ENDWHEN
*ENDWHEN

You need to have common outer WHEN/ENDWHEN loop to aggregate records! Like here.

[former_member232600 · Accepted solution]

Thanx, i understand .

I.e. my full logic must be like that

*XDIM_MEMBERSET ACCOUNT BAS(10001210000000), BAS(10001220032300), BAS(10001220120000)

*XDIM_ADDMEMBERSET ACCOUNT 10001220090000,10001210305000,10001210605000

*XDIM_MEMBERSET FLOW BAS(999)

*XDIM_ADDMEMBERSET FLOW 116,117,149,109,101,102,132

*WHEN ACCOUNT

*IS BAS(10001210000000)
*WHEN FLOW
*IS 109
*WHEN PARTNER
*IS BAS(I_ALL)
*REC(EXPRESSION=%VALUE%,ACCOUNT=70000000000014,FLOW = 114,PARTNER=I_XXX)
*ENDWHEN
*ENDWHEN

............
*WHEN ACCOUNT
*IS 10001210305000 //base level

*WHEN FLOW
*IS 110
*WHEN PARTNER
*IS BAS(I_1000)
*REC(EXPRESSION=%VALUE%*(-1),ACCOUNT=70000000000014,FLOW = 114,PARTNER=I_XXX)
*ENDWHEN
*ENDWHEN
*ENDWHEN

[former_member232600 · Accepted solution]

code is too long, there are no possibilities to count it all through the MDX, and then write down the amount of the account of ... view that there is such a construction loke this :

[ACCOUNT].[#7000000000014],[FLOW].[114],[PARTNER].[I_3100] = [account].[1],[flow].[1],[partner].[1] + [account].[2],[flow].[2],[partner].[1] + [account].[3],[flow].[1],[partner].[2]

[former_member186338 · Accepted solution]

Something like!

...
*IS 10001210305000 //base member
*WHEN FLOW
*IS 101,106,132 //base members
*WHEN PARTNER
*IS BAS(I_1000) //I_1000 is a parent???
*REC(EXPRESSION=-%VALUE%,ACCOUNT=70000000000014,FLOW = 114,PARTNER=I_XXX)
*ENDWHEN
*ENDWHEN

🙂 what for: %VALUE%*(-1) simply -%VALUE%

[former_member186338 · Accepted solution]

Code is not very long, just you have to be accurate! And this code will run 10 times faster then MDX code with [xxx].[#....]

I do not recommend using [xxx].[#....]=...!

[former_member232600 · Accepted solution]

" *IS BAS(I_1000) //I_1000 is a parent??? - Yes it's parent in I_aLL node "
I'm beginner, that's why I use this construction :

" %VALUE%*(-1) "

Logic I can use multiple sampling like in your example :

*IS 101,106,132 ???
How corretly use it in formula must be
-([ACCOUNT].[10001210305000],[FLOW].[101],[PARTNER].[I_1000])
+ ([ACCOUNT].[10001210305000],[FLOW].[106],[PARTNER].[I_1000])
-([ACCOUNT].[10001210305000],[FLOW].[132],[PARTNER].[I_1000])

[former_member186338 · Accepted solution]

I have already told you that I can show you the full correct script ONLY if you explain everything about 3 dimensions hierarchies! Please provide full info!

*WHEN ACCOUNT
...
*IS 10001210305000 //base
*WHEN FLOW
*IS 101,132 //base - condition for negative records
*WHEN PARTNER
*IS BAS(I_1000) //parent
*REC(EXPRESSION=-%VALUE%,ACCOUNT=70000000000014,FLOW = 114,PARTNER=I_XXX)
*ENDWHEN //PAPTNER
*IS 106 //base - condition for positive records
*WHEN PARTNER
*IS BAS(I_1000) //parent
*REC(EXPRESSION=%VALUE%,ACCOUNT=70000000000014,FLOW = 114,PARTNER=I_XXX)
*ENDWHEN //PARTNER
*ENDWHEN //FLOW
*IS .... // another account
...

*ENDWHEN //ACCOUNT

[former_member186338]

In general you have to rethink the whole process:

You have to aggregate base members, not parents! To understand the relationships I need to see the hierarchy for 3 dimensions involved!

[former_member186338]

"The amount we want to store on the base member account (700000000014), partner and flow - is base level too, unfortunately I do not understand the question about hierarchies (if I will create a new hierarchy, I will take error like that:

Base member '10001210000000' cannot be the parent of '10001210010000' in PARENTHH1"

"partner and flow - is base level too" FLOW = 114? PARTNER=????

You don't need to create any new hierarchy, please show existing!

ACCOUNT

FLOW

PARTNER

[former_member232600]

" *IS BAS(I_1000) //I_1000 is a parent??? - Yes it's parent in I_aLL node "
I'm beginner, that's why I use this construction :

" %VALUE%*(-1) "

Logic I can use multiple sampling like in your example :

*IS 101,106,132 ???
How corretly use it in formula must be
-([ACCOUNT].[10001210305000],[FLOW].[101],[PARTNER].[I_1000])
+ ([ACCOUNT].[10001210305000],[FLOW].[106],[PARTNER].[I_1000])
-([ACCOUNT].[10001210305000],[FLOW].[132],[PARTNER].[I_1000])

[former_member186338]

Look on my answer in other branch of discussion.

[former_member232600]

THANK YOU!!!! script is working good!!!! But I think that mdx(member formulas calculating this business rule faster, but we can't release writeback from mdx)

[former_member186338]

"But I think that mdx member formulas calculating this business rule faster" - in general - no! And it's hard to compare...

[former_member186338]

You are posting unreadable question - do you look on the result after you post something? Or "it's not my problem..."?

Also for questions about script logic full information has to be provided in line with:

https://blogs.sap.com/2014/01/31/how-to-ask-questions-about-script-logic-issues/

[former_member232600]

I'm sorry, I didn't look the result after posting. Can You help me to find error in my sqript logic.

Our company have consolidation model, which has dimension like ACCOUNT(A),AUDITID(D),CONS_GROUP(G),CURRENCY(R),CURRENCY_TRANS(R),ENTITY(E),FLOW(S),MATURING(U),PARTNER(I),PERIOD(T),VERSION(C) and i need aggregate the values posted on specific combinations of accounts , flows, and partner and the aggregated amount under alternate distination account 70000000000014, flow 114 combination.

eg amount = [account].[1],[flow].[1],[partner].[1] + [account].[2],[flow].[2],[partner].[1] + [account].[3],[flow].[1],[partner].[2]

I use this logic

*XDIM_MEMBERSET PERIOD = %PERIOD_SET%

*XDIM_MEMBERSET VERSION = %VERSION_SET%

*XDIM_MEMBERSET CONS_GROUP = %CONS_GROUP_SET%

*XDIM_MEMBERSET ENTITY=%ENTITY_SET%

*XDIM_MEMBERSET ACCOUNT=10001210000000,10001210305000,10001210605000,10001220032300,10001220090000,10001220120000,70000000000014

*XDIM_MEMBERSET FLOW=BAS(999)

*XDIM_MEMBERSET PARTNER=BAS(I_ALL)

*WHEN ACCOUNT

*IS 70000000000014

*WHEN FLOW

*IS 114

*REC( EXPRESSION = %VALUE% + (([ACCOUNT].[10001210000000],[FLOW].[109],[PARTNER].[I_ALL])-([ACCOUNT].[10001210305000],[FLOW].[101],[PARTNER].[I_1000])-([ACCOUNT].[10001210305000],[FLOW].[106],[PARTNER].[I_1000])-([ACCOUNT].[10001210305000],[FLOW].[132],[PARTNER].[I_1000])-([ACCOUNT].[10001210605000],[FLOW].[101],[PARTNER].[I_1000])-([ACCOUNT].[10001210605000],[FLOW].[106],[PARTNER].[I_1000])-([ACCOUNT].[10001210605000],[FLOW].[132],[PARTNER].[I_1000])+([ACCOUNT].[10001220032300],[FLOW].[999],[PARTNER].[I_ALL])-([ACCOUNT].[10001220032300],[FLOW].[109],[PARTNER].[I_ALL])-([ACCOUNT].[10001220032300],[FLOW].[116],[PARTNER].[I_ALL])-([ACCOUNT].[10001220032300],[FLOW].[117],[PARTNER].[I_ALL])-([ACCOUNT].[10001220032300],[FLOW].[149],[PARTNER].[I_ALL])-([ACCOUNT].[10001220032300],[FLOW].[999],[PARTNER].[I_1000])+([ACCOUNT].[10001220090000],[FLOW].[999],[PARTNER].[I_ALL])-([ACCOUNT].[10001220090000],[FLOW].[109],[PARTNER].[I_ALL])-([ACCOUNT].[10001220120000],[FLOW].[149],[PARTNER].[I_ALL])), ACCOUNT = 70000000000014, FLOW = 114)

*ENDWHEN

*ENDWHEN

*COMMIT

when I launch DM packed I have message: "0 RECORD GENERATED"

my LOG

APPLICATION:Consolidation [INFO] GET_DIM_LIST(): I_APPL_ID="Consolidation", #dimensions=13 ACCOUNT,AUDITID,CONS_GROUP,CURRENCY,CURRENCY_TRANS,ENTITY,FLOW,MATURING,MEASURES,PARTNER,PERIOD,SEGMENT,VERSION #dim_memberset=6 PERIOD:2016.05,1 in total. VERSION:V01,1 in total. ENTITY:2230,2270,2 in total. ACCOUNT:10001210000000,10001210305000,10001210605000,10001220032300,10001220090000,...7 in total. FLOW:001,101,102,103,104,...89 in total. PARTNER:1000011215,1000013180,1000013522,1000013571,1000013572,...584 in total.

REC :%VALUE% + (([ACCOUNT].[10001210000000],[FLOW].[109],[PARTNER].[I_ALL])-([ACCOUNT].[10001210305000],[FLOW].[101],[PARTNER].[I_1000])-([ACCOUNT].[10001210305000],[FLOW].[106],[PARTNER].[I_1000])-([ACCOUNT].[10001210305000],[FLOW].[132],[PARTNER].[I_1000])-([ACCOUNT].[10001210605000],[FLOW].[101],[PARTNER].[I_1000])-([ACCOUNT].[10001210605000],[FLOW].[106],[PARTNER].[I_1000])-([ACCOUNT].[10001210605000],[FLOW].[132],[PARTNER].[I_1000])+([ACCOUNT].[10001220032300],[FLOW].[999],[PARTNER].[I_ALL])-([ACCOUNT].[10001220032300],[FLOW].[109],[PARTNER].[I_ALL])-([ACCOUNT].[10001220032300],[FLOW].[116],[PARTNER].[I_ALL])-([ACCOUNT].[10001220032300],[FLOW].[117],[PARTNER].[I_ALL])-([ACCOUNT].[10001220032300],[FLOW].[149],[PARTNER].[I_ALL])-([ACCOUNT].[10001220032300],[FLOW].[999],[PARTNER].[I_1000])+([ACCOUNT].[10001220090000],[FLOW].[999],[PARTNER].[I_ALL])-([ACCOUNT].[10001220090000],[FLOW].[109],[PARTNER].[I_ALL])-([ACCOUNT].[10001220120000],[FLOW].[149],[PARTNER].[I_ALL]))

CALCULATION BEGIN:

QUERY PROCESSING DATA QUERY TIME : 1.00 ms.

0 RECORDS QUERIED OUT.

QUERY REFERENCE DATA QUERY TIME : 6.00 ms.

1137 RECORDS QUERIED OUT.

CALCULATION TIME IN TOTAL :0.00 ms.

NO RECORDS GENERATED.

CALCULATION END.

ENDWHEN ACCUMULATION: 0

RECORDS ARE GENERATED.

SCRIPT RUNNING TIME IN TOTAL:8.00 s.

LOG END TIME:2016-12-12 11:19:19 File path: \ROOT\WEBFOLDERS\T2_BPC\Consolidation\PRIVATEPUBLICATIONS\007684\tempfiles\20161212111911\006NZJ9W8EFKHZWWOUO4CGG1Y_CAPEXTEST.LOG

[former_member186338]

Actually you want to sum the following:

+[ACCOUNT].[10001210000000],[FLOW].[109],[PARTNER].[I_ALL]
-[ACCOUNT].[10001210305000],[FLOW].[101],[PARTNER].[I_1000]
-[ACCOUNT].[10001210305000],[FLOW].[106],[PARTNER].[I_1000]
-[ACCOUNT].[10001210305000],[FLOW].[132],[PARTNER].[I_1000]
-[ACCOUNT].[10001210605000],[FLOW].[101],[PARTNER].[I_1000]
-[ACCOUNT].[10001210605000],[FLOW].[106],[PARTNER].[I_1000]
-[ACCOUNT].[10001210605000],[FLOW].[132],[PARTNER].[I_1000]
+[ACCOUNT].[10001220032300],[FLOW].[999],[PARTNER].[I_ALL]
-[ACCOUNT].[10001220032300],[FLOW].[109],[PARTNER].[I_ALL]
-[ACCOUNT].[10001220032300],[FLOW].[116],[PARTNER].[I_ALL]
-[ACCOUNT].[10001220032300],[FLOW].[117],[PARTNER].[I_ALL]
-[ACCOUNT].[10001220032300],[FLOW].[149],[PARTNER].[I_ALL]
-[ACCOUNT].[10001220032300],[FLOW].[999],[PARTNER].[I_1000]
+[ACCOUNT].[10001220090000],[FLOW].[999],[PARTNER].[I_ALL]
-[ACCOUNT].[10001220090000],[FLOW].[109],[PARTNER].[I_ALL]
-[ACCOUNT].[10001220120000],[FLOW].[149],[PARTNER].[I_ALL]

And store result in:

[ACCOUNT].[70000000000014],[FLOW].[149],[PARTNER].[I_ALL]

Correct?

[former_member186338]

Sorry, store not to I_ALL (parent) but to what PARTNER ID?

[former_member186338]

Also:

Are the following accounts are base members:

10001210000000
10001210305000
10001210605000
10001220032300
10001220090000
10001220120000

Are the following flows are base members?

109
101
106
132
999
116
117
149

[former_member232600]

yes, you are correct.

[former_member232600]

Partner ID - I_ALL, it's a parent... a can't write to I_ALL ???

[former_member232600]

Accounts 10001210000000 10001220032300 10001220120000

and Flow 999

is a node's

[former_member186338]

You can't write to parent, no way!

[former_member186338]

You need to provide hierarchy for ACCOUNT dimension (used accounts) and for FLOW dimension

[former_member232600]

if I understand correctly, I have to change the start script:

*XDIM_MEMBERSET ACCOUNT BAS(10001210000000), BAS(10001220032300), BAS(10001220120000)

*XDIM_ADDMEMBERSET ACCOUNT 10001220090000,10001210305000,10001210605000

*XDIM_MEMBERSET FLOW BAS(999)

*XDIM_ADDMEMBERSET FLOW 116,117,149,109,101,102,132

[former_member186338]

No, the whole script is incorrect!

Please answer my questions about hierarchy and where to store the result!

[former_member232600]

The amount we want to store on the base member account (700000000014), partner and flow - is base level too, unfortunately I do not understand the question about hierarchies (if I will create a new hierarchy, I will take error like that:

Base member '10001210000000' cannot be the parent of '10001210010000' in PARENTHH1