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