Skip to Content

Script logic help??

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Jan 13, 2015 at 11:14 AM

    Hi Naidu,

    Please, answer the questions here How To: Ask questions about Script Logic issues

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

    Vadim

    Add comment
    10|10000 characters needed characters exceeded

    • 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