Skip to Content
-1

BPC Logic Script: Erroк from calculated members

Dec 09, 2016 at 03:09 PM

166

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Vadim Kalinin Dec 12, 2016 at 10:45 AM
0

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

Show 9 Share
10 |10000 characters needed characters left characters exceeded

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

0
Alexander Konstantinov

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

Don't use COMMIT!!!!!

1
Alexander Konstantinov

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.

1

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

0
Alexander Konstantinov

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%

1

" *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])

0
Alexander Konstantinov

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

0

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]

0
Alexander Konstantinov

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].[#....]=...!

0
Vadim Kalinin Dec 09, 2016 at 03:44 PM
0

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/

Show 12 Share
10 |10000 characters needed characters left characters exceeded

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

0
Alexander Konstantinov

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?

0

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

0

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

i-all.jpg (12.4 kB)
i-1000.jpg (22.6 kB)
0
Alexander Konstantinov

You can't write to parent, no way!

0

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

0

Accounts 10001210000000 10001220032300 10001220120000

and Flow 999

is a node's

0
Alexander Konstantinov

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

0

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

0
Alexander Konstantinov

No, the whole script is incorrect!

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

0
Show more comments

yes, you are correct.

0
Vadim Kalinin Dec 12, 2016 at 09:58 AM
0

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!

Show 5 Share
10 |10000 characters needed characters left characters exceeded

"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

0

" *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])

0
Alexander Konstantinov

Look on my answer in other branch of discussion.

0

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)

0
Alexander Konstantinov

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

0