SCRIPT LOGIC ISSUE

[Former Member]

Hi Experts,

We are looking into BPC 11.

The Requirement is as follows,

In row Axis would expand based on property.

To meet the above requirement i write a logic as followes,



*XDIM_MEMBERSET ACCOUNT=GL_PROD 
*XDIM_MEMBERSET CATEGORY = ACTUAL 
*SELECT (%TIME% , "[CURRENT_MONTH]",CATEGORY,"[ID]='PLAN' ")
*XDIM_MEMBERSET TIME=%TIME%
*XDIM_MEMBERSET SCENARIO = ACTUAL 
*SELECT(%PRODUCTS%,"[ID]",PRODUCT,"[PL]='Y'") 
*XDIM_MEMBERSET PRODUCT = %PRODUCTS% 


*WHEN PRODUCT
*IS * 
*FOR %ALL_PROD% = %PRODUCTS%
*REC(EXPRESSION=%VALUE%/%ALL_PROD%,ACCOUNT=STAT_PROD,CATEGORY=PLAN) 
*NEXT
*ENDWHEN

When i run the above logic the package was failed.

Kindly suggest me.

Regards,

Uma

[former_member186338 · Accepted solution]

Sorry, but you can't aggregate values by property - hierarchy parent has to be used for total value!

Without parent you can perform calculations in 2 steps - first aggregate all base members with PL property=Y to some special base member like ALLPLY, then use it:

*XDIM_MEMBERSET ACCOUNT=GL_PROD 
*XDIM_MEMBERSET CATEGORY = ACTUAL 
*SELECT (%TIME% , "[CURRENT_MONTH]",CATEGORY,"[ID]='PLAN' ")
*XDIM_MEMBERSET TIME=%TIME%
*XDIM_MEMBERSET SCENARIO = ACTUAL 
*SELECT(%PRODUCTS%,"[ID]",PRODUCT,"[PL]='Y'")
*XDIM_MEMBERSET PRODUCT = %PRODUCTS% 

//Aggregation to ALLPLY
*WHEN PRODUCT
*IS * 
*REC(EXPRESSION=%VALUE%,PRODUCT=ALLPLY) 
*ENDWHEN

//Divide by ALLPLY
*WHEN PRODUCT
*IS * 
*REC(EXPRESSION=%VALUE%/[PRODUCT].[ALLPLY],ACCOUNT=STAT_PROD,CATEGORY=PLAN) 
*ENDWHEN

this code is less efficient then code with real parent

[Former Member · Accepted solution]

Hi Vadim,

Thank You so much for your Reply.

I write logic With out Creating Dummy Member.

Its Working Fine.

Please Find the logic and suggest me if i am wrong.

*XDIM_MEMBERSET ACCOUNT=GL_PROD , STAT_PROD
*XDIM_MEMBERSET CATEGORY = ACTUAL 
*SELECT (%TIME% , "[CURRENT_MONTH]",CATEGORY,"[ID]='PLAN' ")
*XDIM_MEMBERSET TIME=%TIME%
*XDIM_MEMBERSET SCENARIO = ACTUAL 
*SELECT(%PRODUCTS%,"[ID]",PRODUCT,"[PL]='Y'")
*XDIM_MEMBERSET PRODUCT = %PRODUCTS% 

*WHEN ACCOUNT
*IS GL_PROD
*FOR %PROD% = %PRODUCTS%
*REC(EXPRESSION=%VALUE%,ACCOUNT=STAT_PROD,PRODUCT=%PROD%) 
*ENDWHEN

*WHEN ACCOUNT
*IS GL_PROD 
*REC(EXPRESSION=%VALUE%/[ACCOUNT].[STAT_PROD],ACCOUNT=STAT_PROD,CATEGORY=PLAN) 
*ENDWHEN

[former_member186338]

The alternative solution can be used for huge data volumes with RUNALLOCATION!

Also special PRODUCT member to be created: PROD1 to store value: 1

*XDIM_MEMBERSET ACCOUNT=GL_PROD 
*XDIM_MEMBERSET CATEGORY = ACTUAL 
*SELECT (%TIME% , "[CURRENT_MONTH]",CATEGORY,"[ID]='PLAN' ")
*XDIM_MEMBERSET TIME=%TIME%
*XDIM_MEMBERSET SCENARIO = ACTUAL 
*SELECT(%PRODUCTS%,"[ID]",PRODUCT,"[PL]='Y'")
*XDIM_MEMBERSET PRODUCT=PROD1 //Special member

*WHEN_REF_DATA = MASTER_DATA //to work for empty record
*WHEN PRODUCT
*IS *
*REC(EXPRESSION=1) //fill PROD1 with 1
*ENDWHEN

*RUNALLOCATION
*FACTOR=USING/TOTAL
*DIM PRODUCT WHAT=PROD1; WHERE=%PRODUCTS%; USING=%PRODUCTS%; TOTAL=%PRODUCTS%
*DIM CATEGORY WHAT=ACTUAL; WHERE=PLAN; USING=ACTUAL; TOTAL=ACTUAL
*DIM ACCOUNT WHAT=GL_PROD; WHERE=STAT_PROD; USING=GL_PROD; TOTAL=GL_PROD
*ENDALLOCATION

[former_member186338]

Sorry, but I do not understand you logic at all! The script provided is incorrect - missing NEXT after FOR, please be accurate!

Second: about FOR/NEXT!

Let's assume the code with NEXT is:

*WHEN ACCOUNT
*IS GL_PROD
*FOR %PROD% = %PRODUCTS%
*REC(EXPRESSION=%VALUE%,ACCOUNT=STAT_PROD,PRODUCT=%PROD%) 
*NEXT //missing NEXT!
*ENDWHEN

This code will result in:

*WHEN ACCOUNT
*IS GL_PROD
*REC(EXPRESSION=%VALUE%,ACCOUNT=STAT_PROD,PRODUCT=TM) 
*REC(EXPRESSION=%VALUE%,ACCOUNT=STAT_PROD,PRODUCT=DH) 
*REC(EXPRESSION=%VALUE%,ACCOUNT=STAT_PROD,PRODUCT=LH) 
*REC(EXPRESSION=%VALUE%,ACCOUNT=STAT_PROD,PRODUCT=TS) 
*REC(EXPRESSION=%VALUE%,ACCOUNT=STAT_PROD,PRODUCT=LGT) 
*ENDWHEN

Total will be calculated for each product - as a result a lot of useless calculations will be done and the code will be very slow in production environments! This is an example how NOT to write script logic!

[Former Member]

Hi vadim,

Ya that's Correct.Thank You

Regards,

Uma

[former_member186338]

For sure the logic is incorrect!

You have to define parent member in the PRODUCT hierarchy like ALLPROD with base members TM, DH... under it

then the script will be:

*XDIM_MEMBERSET ACCOUNT=GL_PROD 
*XDIM_MEMBERSET CATEGORY = ACTUAL 
*SELECT (%TIME% , "[CURRENT_MONTH]",CATEGORY,"[ID]='PLAN' ")
*XDIM_MEMBERSET TIME=%TIME%
*XDIM_MEMBERSET SCENARIO = ACTUAL 
*XDIM_MEMBERSET PRODUCT = BAS(ALLPROD) 

*WHEN PRODUCT
*IS * 
*REC(EXPRESSION=%VALUE%/[PRODUCT].[ALLPROD],ACCOUNT=STAT_PROD,CATEGORY=PLAN) 
*ENDWHEN

[Former Member]

Hi Vadim,

Thanks for your reply.

In product dimension we maintain one property Called PL.

PL property =Y those values we maintained in row axis.

Calculation like as follows,

1)Product=TM, category =actual, time=2019.01/sum of products those products have PL property =Y

2)Product=DH, category =actual, time=2019.01/sum of products those products have PL property =Y

Etc

How to define only properties members in rec expression

Kindly suggest how to write that logic.

Regards.

Uma