Script logic help??

[former_member225916]

Hello Team,

I have 3 dimension members H9, H11 and H15. i want to calculate H15 value by adding H9 and H11 values (H15 = H9 + H11). here the condition is, If either H9 or H11 value is zero, logic should have to be skipped. i want to calculate H15 value only if both H9 and H11 values are exist.

I have used IIF command to do this. is there any alternative instead of MDX syntax??

*XDIM_MEMBERSET GLACCOUNT = H9,H11

*XDIM_MEMBERSET AUDITTRAIL_N = Input

*XDIM_MEMBERSET ENTITY_N = ABC

*XDIM_MEMBERSET CATEGORY_N = Actual

*XDIM_MEMBERSET FLOW_N = Balance

*XDIM_MEMBERSET GROUPUS = S_IE05

*XDIM_MEMBERSET RPTCURRENCY_N = INR

*XDIM_MEMBERSET TIME_N = 2015.01

*XDIM_MEMBERSET TRADINGPARTNER = I_NONE

[GLACCOUNT].[#H15] = IIF([GLACCOUNT].[H9] = 0,NULL,IIF([GLACCOUNT].[H11] = 0,NULL,[GLACCOUNT].[H9]+[GLACCOUNT].[H11]))

*COMMIT

Thanks,

Naidu

[former_member186338 · Accepted solution]

Hi Naidu,

Please, answer the questions here

The script is version and SP dependent. Calculation engine info is also required.

Vadim

[former_member186338 · Accepted solution]

In case of BPC 10, some recent SP (using ternary operator):

*XDIM_MEMBERSET AUDITTRAIL_N = Input

*XDIM_MEMBERSET ENTITY_N = ABC

*XDIM_MEMBERSET CATEGORY_N = Actual

*XDIM_MEMBERSET FLOW_N = Balance

*XDIM_MEMBERSET GROUPUS = S_IE05

*XDIM_MEMBERSET RPTCURRENCY_N = INR

*XDIM_MEMBERSET TIME_N = 2015.01

*XDIM_MEMBERSET TRADINGPARTNER = I_NONE

*XDIM_MEMBERSET GLACCOUNT = H9

*WHEN GLACCOUNT

*IS *

*REC(EXPRESSION=[GLACCOUNT].[H11]*%VALUE%==0 ? [GLACCOUNT].[H15] : [GLACCOUNT].[H11]+%VALUE%,GLACCOUNT=H15)

*ENDWHEN

Vadim

Explanation:

1. We scope only H9, if no record for H11 - no write to H15

2. [GLACCOUNT].[H11]*%VALUE% = [GLACCOUNT].[H11]*[GLACCOUNT].[H9] - if any of H9, H11 is zero then result is zero

3. If zero - write same destination value [GLACCOUNT].[H15] over destination GLACCOUNT=H15.

4. If not zero - write [GLACCOUNT].[H11]+[GLACCOUNT].[H9] to GLACCOUNT=H15