SCRIPT Logic using Parent Value

[former_member640296]

We are having ACCOUNT dimension having

ACC_A

ACC_B,

We also have DEPT dimension where

DEPALL is parent of

DEPA,

DEPB,

DEPC

We want to use value of ACC_A for each base member of DEPT dimension and divide it by value of ACC_A of DEPALL and store the result into ACC_B

*XDIM_MEMBERSET ACCOUNT= ACC_A, ACC_B

*XDIM_MEMBERSET DEPT=BAS(DEPALL)

*WHEN ACCOUNT

*IS ACC_A

*WHEN DEPT

*IS %DPT%

*REC(S_ACCOUNT=ACC_B,EXPRESSION=( %VALUE% / %DPT%.PARENT ))

*ENDWHEN

*ENDWHEN

However not able to get the required result. What we need is ACC_A Value at base level of DEP divided by ACC_A Value at Parent of that DEP and store it into ACC_B.

[former_member640296]

Thanks... will check this out and update.

[former_member186338]

You have to manually create a list of departments:

*XDIM_MEMBERSET S_ACCOUNT= ACC_A 
*FOR %DEP%=DEP_PARA,DEP_PARB //list of departments
*XDIM_MEMBERSET DEPT=BAS(%DEP%) //scope base members of department
*WHEN ACCOUNT 
*IS * 
*REC(EXPRESSION=%VALUE%/[DEPT].[%DEP%],S_ACCOUNT=ACC_B) 
*ENDWHEN
*NEXT

[former_member186338]

As an alternative, you can create a property IS_DEPT and fill it with "Y" for department parent members like: DEP_PARA, DEP_PARB

Then:

*SELECT(%DEPS%,ID,DEPT,IS_DEPT=Y) //%DEPS% - list of departments
*XDIM_MEMBERSET S_ACCOUNT= ACC_A 
*FOR %DEP%=%DEPS% 
*XDIM_MEMBERSET DEPT=BAS(%DEP%) //scope base members of department
*WHEN ACCOUNT 
*IS * 
*REC(EXPRESSION=%VALUE%/[DEPT].[%DEP%],S_ACCOUNT=ACC_B) 
*ENDWHEN
*NEXT

[former_member640296]

Yes I would like to perform it for all departments. We are using SAP Business Planning and Consolidation 11.0 Version for SAP BW/4HANA.

[former_member186338]

Then you have selected absolutely incorrect TAG!!!

[former_member640296]

As shown above, need department wise %tage. Hence need to get dynamically total of parent of respective dep to calculate the %tage as shown in above.

[former_member186338]

Still not clear! Do you want to perform calculations for all departments??

P.S. Can you provide your BPC version and SP?

[former_member640296]

Instead of using REC, tried with

*BEGIN [S_ACCOUNT].[#ACC_B] =

([S_ACCOUNT].[ACC_A])/

([S_ACCOUNT].[ACC_A],[DEPT].CURRENTMEMBER.PARENT)

*END

*COMMIT

But this gives me MDX statement error:"Internal error : no result set"

[former_member640296]

In earlier example I have given plain department dimension. However need to have dynamically use the parent value in the calculation

ACC_A Expected Result in ACC_B

DEPALL 130

DEPA_PAR 40

DEPA_1 15 =15/40

DEPA_2 25 =25/40

DEPB_PAR 90

DEPB_1 40 =40/90

DEPB_2 50 =50/90

Where I need to pick the parent value dynamically as when current member for DEPT is DEPA_1 then automatically it should select it's parent value for division

[former_member186338]

Sorry, but ABSOLUTELY not clear!

What is the user selection?

What do you want to calculate?

[former_member640296]

Thanks a ton for your immediate response. It did work for me when I put the actual parent name DEPTALL here. However I want to dynamically choose the immediate parent of Current member of the department. Is there any way with use of [DEPT].[CURRENT].Parent give that flexibility?

[former_member186338]

Please read help: https://help.sap.com/viewer/ac22cb71e63345288987c4facb96861d/10.1/en-US/4c86c5f7707a6c4fe10000000a15...

"You cannot use other MDX keywords (such as PARENT and DESCENDANTS) in FACTOR or EXPRESSION instructions. The only permitted operations are addition (+), subtraction (-), multiplication (*), and division (/), combinations of these operators, and parenthesis for tuple and priorities of the operations."

May be you can use FOR/NEXT for parents, but the dimension structure and requirements are not clear!

[former_member186338]

Try:

*XDIM_MEMBERSET S_ACCOUNT= ACC_A 
*XDIM_MEMBERSET DEPT=BAS(DEPALL) 
*WHEN ACCOUNT 
*IS * 
*REC(EXPRESSION=%VALUE%/[DEPT].[DEPALL],S_ACCOUNT=ACC_B) 
*ENDWHEN