cancel
Showing results for 
Search instead for 
Did you mean: 

Script Logic

Former Member
0 Kudos

Hi Experts,

I written a script to calculate the closing balance of a flow for all the accounts which has account type AST,and for all the period selected by user, and also it should be write the closing balance & the closing balance to opening Balance of Next Month.

*WHEN P_ACCT.ACCTYPE

*IS "AST"

*WHEN TIME

*IS %TIME_SET%

*WHEN FLOW

*IS "F_100"

*REC(EXPRESSION=%VALUE%+[FLOW].[F_120]-[FLOW].[F_130],FLOW=F_999,TIME=TMVL(0,%TIME_SET%))

*REC(EXPRESSION=%VALUE%+[FLOW].[F_120]-[FLOW].[F_130],FLOW=F_100,TIME=TMVL(1,%TIME_SET%))

*ENDWHEN

*ENDWHEN

*ENDWHEN

*COMMIT

i look forward the solution for the following.

1. If the F_100 having the value then only it is calculating the closing balance and writing the opening balance for the next period.

IF the F_100 is not having value then it is not performing. ( my expectation is it should check the calculation of balance F_120-F_130) To do the same if i give ELSE commend it is calculation twice.

Another Option i tried is

//*REC(EXPRESSION=IF([FLOW].[F_100] > 0,[FLOW].[F_100]+[FLOW].[F_120]-[FLOW].[F_130],[FLOW].[F_120]-[FLOW].[F_130],FLOW=F_999,TIME=TMVL(0,%TIME_SET%)))

it is validating but i am getting an error "Root Element Missing." Error in Convertion".

Your Help in this regard is highly appreciated.

Thanks in advance.

Regards,

Guru Rajan.V

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi,

Check this code:


*WHEN P_ACCT.ACCTYPE
*IS "AST"
*WHEN TIME
*IS %TIME_SET%
*WHEN FLOW
*IS "F_100"
*REC(EXPRESSION=TIME=TMVL(0,%TIME_SET%),FLOW="F_999",%VALUE%+ [FLOW].[F_120] - [FLOW].[F_130] )
*REC(EXPRESSION=TIME=TMVL(1,%TIME_SET%),FLOW="F_100",%VALUE%+ [FLOW].[F_120] - [FLOW].[F_130] )
*ENDWHEN
*ENDWHEN
*ENDWHEN
*COMMIT

Regards

Lucas

Former Member
0 Kudos

Hi Lucas,

Thanks for the reply.

It is not working when i send the data even in F_120 & F_130

when i send the data for F_100 it is throwing error : Root Element Missing. I checked by validating and save option for the logics.

can u help me in any other solution.

IF i use mdx it is working but if i mix with mdx and sql in second line is not working.

[FLOW].[#F_999]=[FLOW].[F_100][FLOW].[F_120][FLOW].[F_130]

The above code is working fine but i am not able to use for writing the values to next month as F_100

Or can u help the code how to restrict the data with time dimension.

Thanks in Advance.

Guru Rajan.V

Former Member
0 Kudos

Hi GURURAJAN20,

Try below codes, another way to do this is to use Account Transformation Business Rules, it would be easier and performance wise it would be better.

//Scopes all flows except F_999
*SELECT(%MVTS%,"ID","FLOW","CALC='N' AND ID<>'F_999'")

*XDIM_MEMBERSET FLOW=%MVTS%
*XDIM_MEMBERSET P_ACCT=<ALL>

*WHEN P_ACCT.ACCTYPE
   *IS "AST"
      *WHEN FLOW
         *IS "F_130" //THIS CAN BE CHANGED TO A FLOW PROPERTY TO TAG ALL FLOW THAT SHOULD BE SUBTRACTED
            *REC(FACTOR=-1,FLOW="F_999")
            *REC(FACTOR=-1,FLOW="F_100",TIME=TMVL(1,%TIME_SET%))
         ELSE
            *REC(FACTOR=1,FLOW="F_999")
            *REC(FACTOR=1,FLOW="F_100",TIME=TMVL(1,%TIME_SET%))
      *ENDWHEN
*ENDWHEN
*COMMIT

Hope this helps,

Marvin