BPC 10 Script Logic error when validating "Comma without preceding colon ( After RET ? )"

[former_member288457]

Good day Experts

I have an issue with a Logic Script in BPC 10.0 for NetWeaver, using the Logic Script Editor in the Administration Workspace

I have a TIME Dimension with Several Properties, amongst which "INPUTVALUE5" and "INPUTVALUE6" which contain TIME member IDs of INPUT Members for years 3 ahead and Year 4 ahead for the Base- member selected for the calculation.

I have a piece of script logic that needs to run after the user have captured values for "INPUTVALUE5" and "INPUTVALUE6"

*SELECT(%TIMEVAR5%,"[INPUTVALUE5]",TIME,"[ID]='%TIME_SET%'")
*SELECT(%TIMEVAR6%,"[INPUTVALUE6]",TIME,"[ID]='%TIME_SET%'")
//




*XDIM_MEMBERSET ACCOUNT = <ALL>
*XDIM_MEMBERSET CATEGORY = ACT_YTD_DEC
*XDIM_MEMBERSET DATASRC = COG_YTD_INP
*XDIM_MEMBERSET DETAIL = NO_DETAIL
*XDIM_MEMBERSET ENTITY = %ENTITY_SET%
*XDIM_MEMBERSET FLOW = F99
*XDIM_MEMBERSET INTCO = I_NONE
*XDIM_MEMBERSET RPTCURRENCY = LC
*XDIM_MEMBERSET TIME = %TIMEVAR5%
*XDIM_ADDMEMBERSET TIME = %TIMEVAR6%
//
*WHEN ENTITY
*IS <> "GLOBAL"
*WHEN TIME
*IS %TIMEVAR5%
*WHEN ACCOUNT.BALANCESHEET
*IS "Y"
*REC(EXPRESSION=(((([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[%TIMEVAR5%])+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[%TIMEVAR6%]))/2)),CATEGORY="PLAN_YR5",DATASRC="MAN_INPUT",TIME=%TIME_SET%)
*ELSE
*WHEN ACCOUNT.ADDBALANCESHEET
*IS "Y"
*REC(EXPRESSION=((([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[%TIMEVAR5%])+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[%TIMEVAR6%]))/2),CATEGORY="PLAN_YR5",DATASRC="MAN_INPUT",TIME=%TIME_SET%)
*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN


*COMMIT

When validating the script I receive the error description "Comma without preceding colon ( after RET ? )" on the first *REC instruction (when commented the error appears for the next *REC statement.

Please, can somebody tell me what I am doing wrong?

Thanks in advance.

Regards,

Marthinus Viljoen

[former_member288457 · Accepted solution]

Hi Vadim

As per you referred requirement for Logic Questions

1. CPMBPC Ver801 SP0004 - SAPK-80104INCPMBPC - CPM Business Planning and Consolidation. (I have requested that our SAP BASIS administrators update to the latest SP)
2. the K2 calculation engine used: ABAP
3. The Model consists of 10 Dimensions Measures is Periodic and the other 9 Dimensions gets specified in the *XDIM definitions. The ACCOUNT members are limited to Balance Sheet and Additional Balance Sheet members indicated by ACCOUNT member-list properties labelled BALANCESHEET and ADDBALANCESHEET. In order for us to identify the Input year for the Month member selected, the TIME member-list Properties labelled INPUTVALUE5 (2018.INP for 2015.SEP), INPUTVALUE6 (2019.INP for 2015.SEP) and INPUTVALUE7 (2020.INP for 2015.SEP) have been created. It is not the account types that are causing the issues so these won’t be elaborated upon in this message.
4. The script is to be activated using a DM package, passing back the ENTITY member that was modified and also the TIME member which would always be a month-level member (i.e. 2015.SEP) after the user have captured Estimated planning values against TIME Dimension members 4 and 5 years Ahead (i.e. 2019.INP and 2020.INP) on an Input Schedule.
5. The calculation is very simple: Planning value is calculated and stored against the appropriate Category (I.e. PLAN_YR5 or PLAN_YR6) of the selected TIME Member (i.e. 2015.SEP) and is an average value of the values entered against the two INP TIME member instances of the previous year (2018.INP for PLAN_YR5) plus the target year (2019.INP for PLAN_YR5) on the Input Schedule. (2015.SEP,PLAN_YR5) = ( 2018.INP + 2019.INP ) / 2 . Same goes for PLAN_YR6. (2015.SEP,PLAN_YR6) = (2019.INP + 2020.INP ) / 2
6. Non Functioning Logic (Calcs are duplicated because when no value was captured against an account for example 2018.INP the calculation is not performed and it would even if 2018.INP was populated and not 2019.INP. I don’t yet know how to correct that)

//

//Calculate the closing position for the 2 new Planing Years added (PLAN_YR5 & PLAN_YR6)

//and posting results to the TOT_PLAN Datasrc hierachy

//

//PROCESS INFORMATION

//

//LOGIC PLAN YEAR 5 AVERAGE

// = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

*SELECT(%ACCTVAR1%,"[ID]",ACCOUNT,"[BALANCESHEET]=Y")

*SELECT(%ACCTVAR2%,"[ID]",ACCOUNT,"[ADDBALANCESHEET]=Y")

*SELECT(%TIMEVAR5%,"[INPUTVALUE5]",TIME,"[ID]='%TIME_SET%'")

*SELECT(%TIMEVAR6%,"[INPUTVALUE6]",TIME,"[ID]='%TIME_SET%'")

*SELECT(%TIMEVAR7%,"[INPUTVALUE7]",TIME,"[ID]='%TIME_SET%'")

//

*XDIM_MEMBERSET ACCOUNT = %ACCTVAR1%

*XDIM_ADDMEMBERSET ACCOUNT = %ACCTVAR2%

*XDIM_MEMBERSET CATEGORY = ACT_YTD_DEC

*XDIM_MEMBERSET DATASRC = COG_YTD_INP

*XDIM_MEMBERSET DETAIL = NO_DETAIL

*XDIM_MEMBERSET ENTITY = %ENTITY_SET%

*XDIM_MEMBERSET FLOW = F99

*XDIM_MEMBERSET INTCO = I_NONE

*XDIM_MEMBERSET RPTCURRENCY = LC

*XDIM_MEMBERSET TIME = %TIMEVAR5%

*XDIM_ADDMEMBERSET TIME = %TIMEVAR6%

//

*WHEN TIME

*IS %TIMEVAR5%

*WHEN ACCOUNT.BALANCESHEET

*IS "Y"

*REC(EXPRESSION=((([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[%TIMEVAR5%])+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[%TIMEVAR6%]))/2),CATEGORY="PLAN_YR5",DATASRC="MAN_INPUT",TIME="%TIME_SET%")

*ELSE

*WHEN ACCOUNT.ADDBALANCESHEET

*IS "Y"

*REC(EXPRESSION=((([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[%TIMEVAR5%])+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[%TIMEVAR6%]))/2),CATEGORY="PLAN_YR5",DATASRC="MAN_INPUT",TIME=%TIME_SET%)

*ENDWHEN

*ENDWHEN

*ENDWHEN

//

*COMMIT

//

*XDIM_MEMBERSET ACCOUNT = %ACCTVAR1%

*XDIM_ADDMEMBERSET ACCOUNT = %ACCTVAR2%

*XDIM_MEMBERSET CATEGORY = ACT_YTD_DEC

*XDIM_MEMBERSET DATASRC = COG_YTD_INP

*XDIM_MEMBERSET DETAIL = NO_DETAIL

*XDIM_MEMBERSET ENTITY = %ENTITY_SET%

*XDIM_MEMBERSET FLOW = F99

*XDIM_MEMBERSET INTCO = I_NONE

*XDIM_MEMBERSET RPTCURRENCY = LC

*XDIM_MEMBERSET TIME = %TIMEVAR5%

*XDIM_ADDMEMBERSET TIME = %TIMEVAR6%

//

*WHEN TIME

*IS %TIMEVAR6%

*WHEN ACCOUNT.BALANCESHEET

*IS "Y"

*REC(EXPRESSION=((([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[%TIMEVAR5%])+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[%TIMEVAR6%]))/2),CATEGORY="PLAN_YR5",DATASRC="MAN_INPUT",TIME="%TIME_SET%")

*ELSE

*WHEN ACCOUNT.ADDBALANCESHEET

*IS "Y"

*REC(EXPRESSION=((([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[%TIMEVAR5%])+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[%TIMEVAR6%]))/2),CATEGORY="PLAN_YR5",DATASRC="MAN_INPUT",TIME=%TIME_SET%)

*ENDWHEN

*ENDWHEN

*ENDWHEN

//

*COMMIT

//

//LOGIC PLAN YEAR 6 AVERAGE

// = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

*XDIM_MEMBERSET ACCOUNT = %ACCTVAR1%

*XDIM_ADDMEMBERSET ACCOUNT = %ACCTVAR2%

*XDIM_MEMBERSET CATEGORY = ACT_YTD_DEC

*XDIM_MEMBERSET DATASRC = COG_YTD_INP

*XDIM_MEMBERSET DETAIL = NO_DETAIL

*XDIM_MEMBERSET ENTITY = %ENTITY_SET%

*XDIM_MEMBERSET FLOW = F99

*XDIM_MEMBERSET INTCO = I_NONE

*XDIM_MEMBERSET RPTCURRENCY = LC

//*XDIM_MEMBERSET TIME = %TIME_SET%, %TIMEVAR6%, %TIMEVAR7%

*XDIM_MEMBERSET TIME = %TIMEVAR6%

*XDIM_ADDMEMBERSET TIME = %TIMEVAR7%

//

*WHEN TIME

*IS %TIMEVAR6%

*WHEN ACCOUNT.BALANCESHEET

*IS "Y"

*REC(EXPRESSION=((([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[%TIMEVAR6%])+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[%TIMEVAR7%]))/2),CATEGORY="PLAN_YR6",DATASRC="MAN_INPUT",TIME=%TIME_SET%)

*ELSE

*WHEN ACCOUNT.ADDBALANCESHEET

*IS "Y"

*REC(EXPRESSION=((([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[%TIMEVAR6%])+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[%TIMEVAR7%]))/2),CATEGORY="PLAN_YR6",DATASRC="MAN_INPUT",TIME=%TIME_SET%)

*ENDWHEN

*ENDWHEN

*ENDWHEN

//

*COMMIT

//

*XDIM_MEMBERSET ACCOUNT = %ACCTVAR1%

*XDIM_ADDMEMBERSET ACCOUNT = %ACCTVAR2%

*XDIM_MEMBERSET CATEGORY = ACT_YTD_DEC

*XDIM_MEMBERSET DATASRC = COG_YTD_INP

*XDIM_MEMBERSET DETAIL = NO_DETAIL

*XDIM_MEMBERSET ENTITY = %ENTITY_SET%

*XDIM_MEMBERSET FLOW = F99

*XDIM_MEMBERSET INTCO = I_NONE

*XDIM_MEMBERSET RPTCURRENCY = LC

//*XDIM_MEMBERSET TIME = %TIME_SET%, %TIMEVAR6%, %TIMEVAR7%

*XDIM_MEMBERSET TIME = %TIMEVAR6%

*XDIM_ADDMEMBERSET TIME = %TIMEVAR7%

//

*WHEN TIME

*IS %TIMEVAR7%

*WHEN ACCOUNT.BALANCESHEET

*IS "Y"

*REC(EXPRESSION=((([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[%TIMEVAR6%])+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[%TIMEVAR7%]))/2),CATEGORY="PLAN_YR6",DATASRC="MAN_INPUT",TIME=%TIME_SET%)

*ELSE

*WHEN ACCOUNT.ADDBALANCESHEET

*IS "Y"

*REC(EXPRESSION=((([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[%TIMEVAR6%])+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[%TIMEVAR7%]))/2),CATEGORY="PLAN_YR6",DATASRC="MAN_INPUT",TIME=%TIME_SET%)

*ENDWHEN

*ENDWHEN

*ENDWHEN

//

*COMMIT

// = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

7. The user will select a TIME Member and an Entity Member (TIME = 2015.SEP, ENTITY = 7432)

8. The Logic Script would not validate, But performs as expected when excecuted:

LGX:

*XDIM_MEMBERSET ACCOUNT = 1101_0608,1101_0610,1101_0615,1101_0727,1101_0728,1101_0800,1101_0801,1101_0802,1101_0826,1101_0866,1101_0867,1101_0870,1101_0871,1101_0875,1101_0880,1101_0890,1101_0935,1101_1020,1101_1053,1101_1070,1101_1190,1101_1220,1101_1300,1101_1380,1101_1640,1101_1920,1101_2140,1101_2160,1101_2210,1101_2240,1101_2650,BS_B_ADV_OTHER,BS_B_LIAB_OTHER,BS_B_OTHER_DEP,BS_B_OTH_ASSETS,BS_IMPAIRMENTS,BS_MORTG_LOANS

*XDIM_ADDMEMBERSET ACCOUNT = 0004_1300,1101_0620,1101_0625,1101_0630,1101_0635,1101_0637,1101_0655,1101_0962,1101_1890,1101_2080,1101_2135,1101_2720,1300_4800,1350_0365,A_BS_B_OTH_ASSETS,A_GRLOANADV,A_GRLOANADV_ZAR,A_IEBA,A_STAFF_FTE,BANKASSETELM,BANKASSETIAS,BANKASSETTRD,DLAA_0100,FUMB_3000,FUMO_3000,PERF_LOANS

*XDIM_MEMBERSET CATEGORY = ACT_YTD_DEC

*XDIM_MEMBERSET DATASRC = COG_YTD_INP

*XDIM_MEMBERSET DETAIL = NO_DETAIL

*XDIM_MEMBERSET ENTITY = 7432

*XDIM_MEMBERSET FLOW = F99

*XDIM_MEMBERSET INTCO = I_NONE

*XDIM_MEMBERSET RPTCURRENCY = LC

*XDIM_MEMBERSET TIME = 2018.INP

*XDIM_ADDMEMBERSET TIME = 2019.INP

*WHEN TIME

*IS 2018.INP

*WHEN ACCOUNT.BALANCESHEET

*IS Y

*REC(EXPRESSION=((([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[2018.INP])+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[2019.INP]))/2),CATEGORY=PLAN_YR5,DATASRC=MAN_INPUT,TIME=2015.SEP)

*ELSE

*WHEN ACCOUNT.ADDBALANCESHEET

*IS Y

*REC(EXPRESSION=((([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[2018.INP])+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[2019.INP]))/2),CATEGORY=PLAN_YR5,DATASRC=MAN_INPUT,TIME=2015.SEP)

*ENDWHEN

*ENDWHEN

*ENDWHEN

*COMMIT

*XDIM_MEMBERSET ACCOUNT = 1101_0608,1101_0610,1101_0615,1101_0727,1101_0728,1101_0800,1101_0801,1101_0802,1101_0826,1101_0866,1101_0867,1101_0870,1101_0871,1101_0875,1101_0880,1101_0890,1101_0935,1101_1020,1101_1053,1101_1070,1101_1190,1101_1220,1101_1300,1101_1380,1101_1640,1101_1920,1101_2140,1101_2160,1101_2210,1101_2240,1101_2650,BS_B_ADV_OTHER,BS_B_LIAB_OTHER,BS_B_OTHER_DEP,BS_B_OTH_ASSETS,BS_IMPAIRMENTS,BS_MORTG_LOANS

*XDIM_ADDMEMBERSET ACCOUNT = 0004_1300,1101_0620,1101_0625,1101_0630,1101_0635,1101_0637,1101_0655,1101_0962,1101_1890,1101_2080,1101_2135,1101_2720,1300_4800,1350_0365,A_BS_B_OTH_ASSETS,A_GRLOANADV,A_GRLOANADV_ZAR,A_IEBA,A_STAFF_FTE,BANKASSETELM,BANKASSETIAS,BANKASSETTRD,DLAA_0100,FUMB_3000,FUMO_3000,PERF_LOANS

*XDIM_MEMBERSET CATEGORY = ACT_YTD_DEC

*XDIM_MEMBERSET DATASRC = COG_YTD_INP

*XDIM_MEMBERSET DETAIL = NO_DETAIL

*XDIM_MEMBERSET ENTITY = 7432

*XDIM_MEMBERSET FLOW = F99

*XDIM_MEMBERSET INTCO = I_NONE

*XDIM_MEMBERSET RPTCURRENCY = LC

*XDIM_MEMBERSET TIME = 2018.INP

*XDIM_ADDMEMBERSET TIME = 2019.INP

*WHEN TIME

*IS 2019.INP

*WHEN ACCOUNT.BALANCESHEET

*IS Y

*REC(EXPRESSION=((([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[2018.INP])+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[2019.INP]))/2),CATEGORY=PLAN_YR5,DATASRC=MAN_INPUT,TIME=2015.SEP)

*ELSE

*WHEN ACCOUNT.ADDBALANCESHEET

*IS Y

*REC(EXPRESSION=((([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[2018.INP])+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[2019.INP]))/2),CATEGORY=PLAN_YR5,DATASRC=MAN_INPUT,TIME=2015.SEP)

*ENDWHEN

*ENDWHEN

*ENDWHEN

*COMMIT

*XDIM_MEMBERSET ACCOUNT = 1101_0608,1101_0610,1101_0615,1101_0727,1101_0728,1101_0800,1101_0801,1101_0802,1101_0826,1101_0866,1101_0867,1101_0870,1101_0871,1101_0875,1101_0880,1101_0890,1101_0935,1101_1020,1101_1053,1101_1070,1101_1190,1101_1220,1101_1300,1101_1380,1101_1640,1101_1920,1101_2140,1101_2160,1101_2210,1101_2240,1101_2650,BS_B_ADV_OTHER,BS_B_LIAB_OTHER,BS_B_OTHER_DEP,BS_B_OTH_ASSETS,BS_IMPAIRMENTS,BS_MORTG_LOANS

*XDIM_ADDMEMBERSET ACCOUNT = 0004_1300,1101_0620,1101_0625,1101_0630,1101_0635,1101_0637,1101_0655,1101_0962,1101_1890,1101_2080,1101_2135,1101_2720,1300_4800,1350_0365,A_BS_B_OTH_ASSETS,A_GRLOANADV,A_GRLOANADV_ZAR,A_IEBA,A_STAFF_FTE,BANKASSETELM,BANKASSETIAS,BANKASSETTRD,DLAA_0100,FUMB_3000,FUMO_3000,PERF_LOANS

*XDIM_MEMBERSET CATEGORY = ACT_YTD_DEC

*XDIM_MEMBERSET DATASRC = COG_YTD_INP

*XDIM_MEMBERSET DETAIL = NO_DETAIL

*XDIM_MEMBERSET ENTITY = 7432

*XDIM_MEMBERSET FLOW = F99

*XDIM_MEMBERSET INTCO = I_NONE

*XDIM_MEMBERSET RPTCURRENCY = LC

*XDIM_MEMBERSET TIME = 2019.INP

*XDIM_ADDMEMBERSET TIME = 2020.INP

*WHEN TIME

*IS 2019.INP

*WHEN ACCOUNT.BALANCESHEET

*IS Y

*REC(EXPRESSION=((([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[2019.INP])+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[2020.INP]))/2),CATEGORY=PLAN_YR6,DATASRC=MAN_INPUT,TIME=2015.SEP)

*ELSE

*WHEN ACCOUNT.ADDBALANCESHEET

*IS Y

*REC(EXPRESSION=((([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[2019.INP])+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[2020.INP]))/2),CATEGORY=PLAN_YR6,DATASRC=MAN_INPUT,TIME=2015.SEP)

*ENDWHEN

*ENDWHEN

*ENDWHEN

*COMMIT

*XDIM_MEMBERSET ACCOUNT = 1101_0608,1101_0610,1101_0615,1101_0727,1101_0728,1101_0800,1101_0801,1101_0802,1101_0826,1101_0866,1101_0867,1101_0870,1101_0871,1101_0875,1101_0880,1101_0890,1101_0935,1101_1020,1101_1053,1101_1070,1101_1190,1101_1220,1101_1300,1101_1380,1101_1640,1101_1920,1101_2140,1101_2160,1101_2210,1101_2240,1101_2650,BS_B_ADV_OTHER,BS_B_LIAB_OTHER,BS_B_OTHER_DEP,BS_B_OTH_ASSETS,BS_IMPAIRMENTS,BS_MORTG_LOANS

*XDIM_ADDMEMBERSET ACCOUNT = 0004_1300,1101_0620,1101_0625,1101_0630,1101_0635,1101_0637,1101_0655,1101_0962,1101_1890,1101_2080,1101_2135,1101_2720,1300_4800,1350_0365,A_BS_B_OTH_ASSETS,A_GRLOANADV,A_GRLOANADV_ZAR,A_IEBA,A_STAFF_FTE,BANKASSETELM,BANKASSETIAS,BANKASSETTRD,DLAA_0100,FUMB_3000,FUMO_3000,PERF_LOANS

*XDIM_MEMBERSET CATEGORY = ACT_YTD_DEC

*XDIM_MEMBERSET DATASRC = COG_YTD_INP

*XDIM_MEMBERSET DETAIL = NO_DETAIL

*XDIM_MEMBERSET ENTITY = 7432

*XDIM_MEMBERSET FLOW = F99

*XDIM_MEMBERSET INTCO = I_NONE

*XDIM_MEMBERSET RPTCURRENCY = LC

*XDIM_MEMBERSET TIME = 2019.INP

*XDIM_ADDMEMBERSET TIME = 2020.INP

*WHEN TIME

*IS 2020.INP

*WHEN ACCOUNT.BALANCESHEET

*IS Y

*REC(EXPRESSION=((([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[2019.INP])+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[2020.INP]))/2),CATEGORY=PLAN_YR6,DATASRC=MAN_INPUT,TIME=2015.SEP)

*ELSE

*WHEN ACCOUNT.ADDBALANCESHEET

*IS Y

*REC(EXPRESSION=((([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[2019.INP])+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[2020.INP]))/2),CATEGORY=PLAN_YR6,DATASRC=MAN_INPUT,TIME=2015.SEP)

*ENDWHEN

*ENDWHEN

*ENDWHEN

*COMMIT

-------------------------------------------------------------------------------------------------------------------------------------

LOG:

LOG BEGIN TIME:2015-09-14 10:45:51

FILE:\ROOT\WEBFOLDERS\NEDPENN \ADMINAPP\Financial\TEST.LGF

USER:NB186919

APPSET:NEDPENN

APPLICATION:Financial

[INFO] GET_DIM_LIST(): I_APPL_ID="Financial", #dimensions=10

ACCOUNT,CATEGORY,DATASRC,DETAIL,ENTITY,FLOW,INTCO,MEASURES,RPTCURRENCY,TIME

#dim_memberset=9

ACCOUNT:1101_0608,1101_0610,1101_0615,1101_0727,1101_0728,...63 in total.

CATEGORY:ACT_YTD_DEC,1 in total.

DATASRC:COG_YTD_INP,1 in total.

DETAIL:NO_DETAIL,1 in total.

ENTITY:7432,1 in total.

FLOW:F99,1 in total.

INTCO:I_NONE,1 in total.

RPTCURRENCY:LC,1 in total.

TIME:2018.INP,2019.INP,2 in total.

REC :((([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[2018.INP])+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[2019.INP]))/2)

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 952.87 ms. 33 RECORDS QUERIED OUT.

QUERY REFERENCE DATA

QUERY TIME : 946.09 ms. 65 RECORDS QUERIED OUT.

CALCULATION TIME IN TOTAL :560.98 ms.

33 RECORDS ARE GENERATED.

CALCULATION END.

#dim_memberset=9

ACCOUNT:1101_0608,1101_0610,1101_0615,1101_0727,1101_0728,...63 in total.

CATEGORY:ACT_YTD_DEC,1 in total.

DATASRC:COG_YTD_INP,1 in total.

DETAIL:NO_DETAIL,1 in total.

ENTITY:7432,1 in total.

FLOW:F99,1 in total.

INTCO:I_NONE,1 in total.

RPTCURRENCY:LC,1 in total.

TIME:2018.INP,2019.INP,2 in total.

REC :((([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[2018.INP])+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[2019.INP]))/2)

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 1085.02 ms. 25 RECORDS QUERIED OUT.

QUERY REFERENCE DATA

QUERY TIME : 1002.47 ms. 50 RECORDS QUERIED OUT.

CALCULATION TIME IN TOTAL :546.69 ms.

25 RECORDS ARE GENERATED.

CALCULATION END.

ENDWHEN ACCUMULATION: 58 RECORDS ARE GENERATED.

DATA TO WRITE BACK:

ACCOUNT CATEGORY DATASRC DETAIL ENTITY FLOW INTCO RPTCURRENCY TIME SIGNEDDATA

1101_0608 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 49.00

1101_0610 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 58.00

1101_0615 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 33.50

1101_0727 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 610.00

1101_0728 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 849.50

1101_0800 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 294.50

1101_0801 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 299.50

1101_0802 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 27.50

1101_0826 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 32.50

1101_0866 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 81.00

1101_0867 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 344.00

1101_0870 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 654.00

1101_0871 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 738.50

1101_0875 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 88.00

1101_0880 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 77.00

1101_0890 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 24.50

1101_0935 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 85.00

1101_1020 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 32.00

1101_1053 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 527.50

1101_1070 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 355.00

1101_1190 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 26.00

1101_1300 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 358.50

1101_1380 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 1205.00

1101_1920 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 678.00

1101_2140 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 33.00

1101_2160 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 3214.00

1101_2210 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 65.00

1101_2240 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 646.00

1101_2650 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 654.00

BS_B_ADV_OTHER PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 5389.00

BS_B_LIAB_OTHER PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 46.00

BS_B_OTHER_DEP PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 9987.00

BS_B_OTH_ASSETS PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 366.00

0004_1300 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 245.00

1101_0620 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 13.00

1101_0625 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 1321.00

1101_0630 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 1321.00

1101_0635 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 633.00

1101_0637 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 12434.00

1101_0655 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 5322.00

1101_0962 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 3299.00

1101_1890 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 346534.00

1101_2080 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 345.00

1101_2135 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 764.00

1101_2720 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 765.00

A_BS_B_OTH_ASSETS PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 78.00

A_GRLOANADV PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 786.00

A_GRLOANADV_ZAR PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 3420.50

A_IEBA PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 13.00

A_STAFF_FTE PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 89756.00

BANKASSETELM PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 4352.00

BANKASSETIAS PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 3822930.00

BANKASSETTRD PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 856.00

DLAA_0100 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 563674.00

FUMB_3000 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 535.00

FUMO_3000 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 975.00

PERF_LOANS PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 359.00

1350_0365 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 8967.00

58 RECORDS HAVE BEEN WRITTEN BACK.

WRITING TIME :5928.30 ms.

[INFO] GET_DIM_LIST(): I_APPL_ID="Financial", #dimensions=10

ACCOUNT,CATEGORY,DATASRC,DETAIL,ENTITY,FLOW,INTCO,MEASURES,RPTCURRENCY,TIME

#dim_memberset=9

ACCOUNT:1101_0608,1101_0610,1101_0615,1101_0727,1101_0728,...63 in total.

CATEGORY:ACT_YTD_DEC,1 in total.

DATASRC:COG_YTD_INP,1 in total.

DETAIL:NO_DETAIL,1 in total.

ENTITY:7432,1 in total.

FLOW:F99,1 in total.

INTCO:I_NONE,1 in total.

RPTCURRENCY:LC,1 in total.

TIME:2018.INP,2019.INP,2 in total.

REC :((([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[2018.INP])+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[2019.INP]))/2)

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 334.69 ms. 32 RECORDS QUERIED OUT.

QUERY REFERENCE DATA

QUERY TIME : 364.36 ms. 65 RECORDS QUERIED OUT.

CALCULATION TIME IN TOTAL :21.84 ms.

32 RECORDS ARE GENERATED.

CALCULATION END.

#dim_memberset=9

ACCOUNT:1101_0608,1101_0610,1101_0615,1101_0727,1101_0728,...63 in total.

CATEGORY:ACT_YTD_DEC,1 in total.

DATASRC:COG_YTD_INP,1 in total.

DETAIL:NO_DETAIL,1 in total.

ENTITY:7432,1 in total.

FLOW:F99,1 in total.

INTCO:I_NONE,1 in total.

RPTCURRENCY:LC,1 in total.

TIME:2018.INP,2019.INP,2 in total.

REC :((([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[2018.INP])+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[2019.INP]))/2)

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 445.40 ms. 25 RECORDS QUERIED OUT.

QUERY REFERENCE DATA

QUERY TIME : 501.97 ms. 50 RECORDS QUERIED OUT.

CALCULATION TIME IN TOTAL :19.85 ms.

25 RECORDS ARE GENERATED.

CALCULATION END.

ENDWHEN ACCUMULATION: 57 RECORDS ARE GENERATED.

DATA TO WRITE BACK:

ACCOUNT CATEGORY DATASRC DETAIL ENTITY FLOW INTCO RPTCURRENCY TIME SIGNEDDATA

1101_0608 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 49.00

1101_0610 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 58.00

1101_0615 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 33.50

1101_0727 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 610.00

1101_0728 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 849.50

1101_0800 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 294.50

1101_0801 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 299.50

1101_0802 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 27.50

1101_0826 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 32.50

1101_0866 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 81.00

1101_0867 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 344.00

1101_0870 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 654.00

1101_0871 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 738.50

1101_0875 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 88.00

1101_0880 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 77.00

1101_0890 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 24.50

1101_0935 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 85.00

1101_1053 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 527.50

1101_1070 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 355.00

1101_1190 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 26.00

1101_1300 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 358.50

1101_1380 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 1205.00

1101_1920 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 678.00

1101_2140 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 33.00

1101_2160 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 3214.00

1101_2210 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 65.00

1101_2240 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 646.00

1101_2650 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 654.00

BS_B_ADV_OTHER PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 5389.00

BS_B_LIAB_OTHER PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 46.00

BS_B_OTHER_DEP PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 9987.00

BS_B_OTH_ASSETS PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 366.00

0004_1300 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 245.00

1101_0620 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 13.00

1101_0625 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 1321.00

1101_0630 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 1321.00

1101_0635 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 633.00

1101_0637 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 12434.00

1101_0655 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 5322.00

1101_0962 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 3299.00

1101_1890 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 346534.00

1101_2080 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 345.00

1101_2135 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 764.00

1101_2720 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 765.00

A_BS_B_OTH_ASSETS PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 78.00

A_GRLOANADV PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 786.00

A_GRLOANADV_ZAR PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 3420.50

A_IEBA PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 13.00

A_STAFF_FTE PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 89756.00

BANKASSETELM PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 4352.00

BANKASSETIAS PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 3822930.00

BANKASSETTRD PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 856.00

DLAA_0100 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 563674.00

FUMB_3000 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 535.00

FUMO_3000 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 975.00

PERF_LOANS PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 359.00

1350_0365 PLAN_YR5 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 8967.00

57 RECORDS HAVE BEEN WRITTEN BACK.

WRITING TIME :5004.28 ms.

[INFO] GET_DIM_LIST(): I_APPL_ID="Financial", #dimensions=10

ACCOUNT,CATEGORY,DATASRC,DETAIL,ENTITY,FLOW,INTCO,MEASURES,RPTCURRENCY,TIME

#dim_memberset=9

ACCOUNT:1101_0608,1101_0610,1101_0615,1101_0727,1101_0728,...63 in total.

CATEGORY:ACT_YTD_DEC,1 in total.

DATASRC:COG_YTD_INP,1 in total.

DETAIL:NO_DETAIL,1 in total.

ENTITY:7432,1 in total.

FLOW:F99,1 in total.

INTCO:I_NONE,1 in total.

RPTCURRENCY:LC,1 in total.

TIME:2019.INP,2020.INP,2 in total.

REC :((([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[2019.INP])+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[2020.INP]))/2)

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 466.54 ms. 32 RECORDS QUERIED OUT.

QUERY REFERENCE DATA

QUERY TIME : 396.90 ms. 65 RECORDS QUERIED OUT.

CALCULATION TIME IN TOTAL :22.06 ms.

32 RECORDS ARE GENERATED.

CALCULATION END.

#dim_memberset=9

ACCOUNT:1101_0608,1101_0610,1101_0615,1101_0727,1101_0728,...63 in total.

CATEGORY:ACT_YTD_DEC,1 in total.

DATASRC:COG_YTD_INP,1 in total.

DETAIL:NO_DETAIL,1 in total.

ENTITY:7432,1 in total.

FLOW:F99,1 in total.

INTCO:I_NONE,1 in total.

RPTCURRENCY:LC,1 in total.

TIME:2019.INP,2020.INP,2 in total.

REC :((([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[2019.INP])+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[2020.INP]))/2)

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 430.72 ms. 25 RECORDS QUERIED OUT.

QUERY REFERENCE DATA

QUERY TIME : 500.87 ms. 50 RECORDS QUERIED OUT.

CALCULATION TIME IN TOTAL :23.68 ms.

25 RECORDS ARE GENERATED.

CALCULATION END.

ENDWHEN ACCUMULATION: 57 RECORDS ARE GENERATED.

DATA TO WRITE BACK:

ACCOUNT CATEGORY DATASRC DETAIL ENTITY FLOW INTCO RPTCURRENCY TIME SIGNEDDATA

1101_0608 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 49.00

1101_0610 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 58.00

1101_0615 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 33.50

1101_0727 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 610.00

1101_0728 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 849.50

1101_0800 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 294.50

1101_0801 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 299.50

1101_0802 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 27.50

1101_0826 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 32.50

1101_0866 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 81.00

1101_0867 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 344.00

1101_0870 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 654.00

1101_0871 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 738.50

1101_0875 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 88.00

1101_0880 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 77.00

1101_0890 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 24.50

1101_0935 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 85.00

1101_1053 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 527.50

1101_1070 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 355.00

1101_1190 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 26.00

1101_1300 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 358.50

1101_1380 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 1205.00

1101_1920 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 678.00

1101_2140 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 33.00

1101_2160 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 3214.00

1101_2210 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 65.00

1101_2240 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 646.00

1101_2650 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 654.00

BS_B_ADV_OTHER PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 5389.00

BS_B_LIAB_OTHER PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 46.00

BS_B_OTHER_DEP PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 9987.00

BS_B_OTH_ASSETS PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 366.00

0004_1300 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 245.00

1101_0620 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 13.00

1101_0625 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 1321.00

1101_0630 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 1321.00

1101_0635 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 633.00

1101_0637 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 12434.00

1101_0655 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 5322.00

1101_0962 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 3299.00

1101_1890 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 346534.00

1101_2080 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 345.00

1101_2135 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 764.00

1101_2720 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 765.00

A_BS_B_OTH_ASSETS PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 78.00

A_GRLOANADV PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 786.00

A_GRLOANADV_ZAR PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 3420.50

A_IEBA PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 13.00

A_STAFF_FTE PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 89756.00

BANKASSETELM PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 4352.00

BANKASSETIAS PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 3822930.00

BANKASSETTRD PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 856.00

DLAA_0100 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 563674.00

FUMB_3000 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 535.00

FUMO_3000 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 975.00

PERF_LOANS PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 359.00

1350_0365 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 8967.00

57 RECORDS HAVE BEEN WRITTEN BACK.

WRITING TIME :5044.00 ms.

[INFO] GET_DIM_LIST(): I_APPL_ID="Financial", #dimensions=10

ACCOUNT,CATEGORY,DATASRC,DETAIL,ENTITY,FLOW,INTCO,MEASURES,RPTCURRENCY,TIME

#dim_memberset=9

ACCOUNT:1101_0608,1101_0610,1101_0615,1101_0727,1101_0728,...63 in total.

CATEGORY:ACT_YTD_DEC,1 in total.

DATASRC:COG_YTD_INP,1 in total.

DETAIL:NO_DETAIL,1 in total.

ENTITY:7432,1 in total.

FLOW:F99,1 in total.

INTCO:I_NONE,1 in total.

RPTCURRENCY:LC,1 in total.

TIME:2019.INP,2020.INP,2 in total.

REC :((([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[2019.INP])+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[2020.INP]))/2)

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 312.65 ms. 33 RECORDS QUERIED OUT.

QUERY REFERENCE DATA

QUERY TIME : 227.15 ms. 65 RECORDS QUERIED OUT.

CALCULATION TIME IN TOTAL :32.64 ms.

33 RECORDS ARE GENERATED.

CALCULATION END.

#dim_memberset=9

ACCOUNT:1101_0608,1101_0610,1101_0615,1101_0727,1101_0728,...63 in total.

CATEGORY:ACT_YTD_DEC,1 in total.

DATASRC:COG_YTD_INP,1 in total.

DETAIL:NO_DETAIL,1 in total.

ENTITY:7432,1 in total.

FLOW:F99,1 in total.

INTCO:I_NONE,1 in total.

RPTCURRENCY:LC,1 in total.

TIME:2019.INP,2020.INP,2 in total.

REC :((([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[2019.INP])+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[2020.INP]))/2)

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 474.41 ms. 25 RECORDS QUERIED OUT.

QUERY REFERENCE DATA

QUERY TIME : 284.23 ms. 50 RECORDS QUERIED OUT.

CALCULATION TIME IN TOTAL :21.82 ms.

25 RECORDS ARE GENERATED.

CALCULATION END.

ENDWHEN ACCUMULATION: 58 RECORDS ARE GENERATED.

DATA TO WRITE BACK:

ACCOUNT CATEGORY DATASRC DETAIL ENTITY FLOW INTCO RPTCURRENCY TIME SIGNEDDATA

1101_0608 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 49.00

1101_0610 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 58.00

1101_0615 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 33.50

1101_0727 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 610.00

1101_0728 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 849.50

1101_0800 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 294.50

1101_0801 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 299.50

1101_0802 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 27.50

1101_0826 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 32.50

1101_0866 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 81.00

1101_0867 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 344.00

1101_0870 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 654.00

1101_0871 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 738.50

1101_0875 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 88.00

1101_0880 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 77.00

1101_0890 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 24.50

1101_0935 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 85.00

1101_1020 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 32.00

1101_1053 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 527.50

1101_1070 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 355.00

1101_1190 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 26.00

1101_1300 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 358.50

1101_1380 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 1205.00

1101_1920 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 678.00

1101_2140 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 33.00

1101_2160 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 3214.00

1101_2210 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 65.00

1101_2240 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 646.00

1101_2650 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 654.00

BS_B_ADV_OTHER PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 5389.00

BS_B_LIAB_OTHER PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 46.00

BS_B_OTHER_DEP PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 9987.00

BS_B_OTH_ASSETS PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 366.00

0004_1300 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 245.00

1101_0620 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 13.00

1101_0625 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 1321.00

1101_0630 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 1321.00

1101_0635 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 633.00

1101_0637 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 12434.00

1101_0655 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 5322.00

1101_0962 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 3299.00

1101_1890 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 346534.00

1101_2080 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 345.00

1101_2135 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 764.00

1101_2720 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP - 765.00

A_BS_B_OTH_ASSETS PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 78.00

A_GRLOANADV PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 786.00

A_GRLOANADV_ZAR PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 3420.50

A_IEBA PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 13.00

A_STAFF_FTE PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 89756.00

BANKASSETELM PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 4352.00

BANKASSETIAS PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 3822930.00

BANKASSETTRD PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 856.00

DLAA_0100 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 563674.00

FUMB_3000 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 535.00

FUMO_3000 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 975.00

PERF_LOANS PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 359.00

1350_0365 PLAN_YR6 MAN_INPUT NO_DETAIL 7432 F99 I_NONE LC 2015.SEP 8967.00

58 RECORDS HAVE BEEN WRITTEN BACK.

WRITING TIME :5529.70 ms.

SCRIPT RUNNING TIME IN TOTAL:32.39 s.

LOG END TIME:2015-09-14 10:46:24

9. In this instance it is not the calc that is wrong per-se but rather the way in which the CALC was set up. So no report included

10. No questions on advanced DM scripts thanks.

I have been trying and found the following method of assigning the Variable in the calc to allow the script to be validated.

Working Logic Script

//

//Calculate the closing position for the 2 new Planing Years added (PLAN_YR5 & PLAN_YR6)

//and posting results to the TOT_PLAN Datasrc hierachy

//

//PROCESS INFORMATION

//

//LOGIC PLAN YEAR 5 AVERAGE

// = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

*SELECT(%ACCTVAR1%,"[ID]",ACCOUNT,"[BALANCESHEET]=Y")

*SELECT(%ACCTVAR2%,"[ID]",ACCOUNT,"[ADDBALANCESHEET]=Y")

*SELECT(%TIMEVAR5%,"[INPUTVALUE5]",TIME,"[ID]='%TIME_SET%'")

*SELECT(%TIMEVAR6%,"[INPUTVALUE6]",TIME,"[ID]='%TIME_SET%'")

*SELECT(%TIMEVAR7%,"[INPUTVALUE7]",TIME,"[ID]='%TIME_SET%'")

//

*XDIM_MEMBERSET ACCOUNT = %ACCTVAR1%

*XDIM_ADDMEMBERSET ACCOUNT = %ACCTVAR2%

*XDIM_MEMBERSET CATEGORY = ACT_YTD_DEC

*XDIM_MEMBERSET DATASRC = COG_YTD_INP

*XDIM_MEMBERSET DETAIL = NO_DETAIL

*XDIM_MEMBERSET ENTITY = %ENTITY_SET%

*XDIM_MEMBERSET FLOW = F99

*XDIM_MEMBERSET INTCO = I_NONE

*XDIM_MEMBERSET RPTCURRENCY = LC

*XDIM_MEMBERSET TIME = %TIMEVAR5%

*XDIM_ADDMEMBERSET TIME = %TIMEVAR6%

//

*WHEN TIME

*IS %TIMEVAR5%

*WHEN ACCOUNT.BALANCESHEET

*IS "Y"

//[CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[%TIMEVAR5%]

*REC(EXPRESSION=(((%VALUE%)+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[TMVL(0,%TIMEVAR6%)]))/2),CATEGORY="PLAN_YR5",DATASRC="MAN_INPUT",TIME="%TIME_SET%")

*ELSE

*WHEN ACCOUNT.ADDBALANCESHEET

*IS "Y"

//[CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[%TIMEVAR5%]

*REC(EXPRESSION=(((%VALUE%)+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[TMVL(0,%TIMEVAR6%)]))/2),CATEGORY="PLAN_YR5",DATASRC="MAN_INPUT",TIME=%TIME_SET%)

*ENDWHEN

*ENDWHEN

*ENDWHEN

//

*COMMIT

//

*XDIM_MEMBERSET ACCOUNT = %ACCTVAR1%

*XDIM_ADDMEMBERSET ACCOUNT = %ACCTVAR2%

*XDIM_MEMBERSET CATEGORY = ACT_YTD_DEC

*XDIM_MEMBERSET DATASRC = COG_YTD_INP

*XDIM_MEMBERSET DETAIL = NO_DETAIL

*XDIM_MEMBERSET ENTITY = %ENTITY_SET%

*XDIM_MEMBERSET FLOW = F99

*XDIM_MEMBERSET INTCO = I_NONE

*XDIM_MEMBERSET RPTCURRENCY = LC

*XDIM_MEMBERSET TIME = %TIMEVAR5%

*XDIM_ADDMEMBERSET TIME = %TIMEVAR6%

//

*WHEN TIME

*IS %TIMEVAR6%

*WHEN ACCOUNT.BALANCESHEET

*IS "Y"

//[CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[%TIMEVAR6%]

*REC(EXPRESSION=((([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[TMVL(0,%TIMEVAR5%)])+(%VALUE%))/2),CATEGORY="PLAN_YR5",DATASRC="MAN_INPUT",TIME="%TIME_SET%")

*ELSE

*WHEN ACCOUNT.ADDBALANCESHEET

*IS "Y"

//[CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[%TIMEVAR6%]

*REC(EXPRESSION=((([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[TMVL(0,%TIMEVAR5%)])+(%VALUE%))/2),CATEGORY="PLAN_YR5",DATASRC="MAN_INPUT",TIME="%TIME_SET%")

*ENDWHEN

*ENDWHEN

*ENDWHEN

//

*COMMIT

//

//LOGIC PLAN YEAR 6 AVERAGE

// = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

*XDIM_MEMBERSET ACCOUNT = %ACCTVAR1%

*XDIM_ADDMEMBERSET ACCOUNT = %ACCTVAR2%

*XDIM_MEMBERSET CATEGORY = ACT_YTD_DEC

*XDIM_MEMBERSET DATASRC = COG_YTD_INP

*XDIM_MEMBERSET DETAIL = NO_DETAIL

*XDIM_MEMBERSET ENTITY = %ENTITY_SET%

*XDIM_MEMBERSET FLOW = F99

*XDIM_MEMBERSET INTCO = I_NONE

*XDIM_MEMBERSET RPTCURRENCY = LC

//*XDIM_MEMBERSET TIME = %TIME_SET%, %TIMEVAR6%, %TIMEVAR7%

*XDIM_MEMBERSET TIME = %TIMEVAR6%

*XDIM_ADDMEMBERSET TIME = %TIMEVAR7%

//

*WHEN TIME

*IS %TIMEVAR6%

*WHEN ACCOUNT.BALANCESHEET

*IS "Y"

//[CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[%TIMEVAR6%]

*REC(EXPRESSION=(((%VALUE%)+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[TMVL(0,%TIMEVAR7%)]))/2),CATEGORY="PLAN_YR6",DATASRC="MAN_INPUT",TIME=%TIME_SET%)

*ELSE

*WHEN ACCOUNT.ADDBALANCESHEET

*IS "Y"

//[CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[%TIMEVAR6%]

*REC(EXPRESSION=(((%VALUE%)+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[TMVL(0,%TIMEVAR7%)]))/2),CATEGORY="PLAN_YR6",DATASRC="MAN_INPUT",TIME=%TIME_SET%)

*ENDWHEN

*ENDWHEN

*ENDWHEN

//

*COMMIT

//

*XDIM_MEMBERSET ACCOUNT = %ACCTVAR1%

*XDIM_ADDMEMBERSET ACCOUNT = %ACCTVAR2%

*XDIM_MEMBERSET CATEGORY = ACT_YTD_DEC

*XDIM_MEMBERSET DATASRC = COG_YTD_INP

*XDIM_MEMBERSET DETAIL = NO_DETAIL

*XDIM_MEMBERSET ENTITY = %ENTITY_SET%

*XDIM_MEMBERSET FLOW = F99

*XDIM_MEMBERSET INTCO = I_NONE

*XDIM_MEMBERSET RPTCURRENCY = LC

//*XDIM_MEMBERSET TIME = %TIME_SET%, %TIMEVAR6%, %TIMEVAR7%

*XDIM_MEMBERSET TIME = %TIMEVAR6%

*XDIM_ADDMEMBERSET TIME = %TIMEVAR7%

//

*WHEN TIME

*IS %TIMEVAR7%

*WHEN ACCOUNT.BALANCESHEET

*IS "Y"

//[CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[%TIMEVAR7%]

*REC(EXPRESSION=((([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[TMVL(0,%TIMEVAR6%)])+(%VALUE%))/2),CATEGORY="PLAN_YR6",DATASRC="MAN_INPUT",TIME=%TIME_SET%)

*ELSE

*WHEN ACCOUNT.ADDBALANCESHEET

*IS "Y"

//[CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[%TIMEVAR7%]

*REC(EXPRESSION=((([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[TMVL(0,%TIMEVAR6%)])+(%VALUE%))/2),CATEGORY="PLAN_YR6",DATASRC="MAN_INPUT",TIME=%TIME_SET%)

*ENDWHEN

*ENDWHEN

*ENDWHEN

//

*COMMIT

// = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

I have another issue though (hence code duplication)

When I code the script as follows

Wrong script

// = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

*SELECT(%ACCTVAR1%,"[ID]",ACCOUNT,"[BALANCESHEET]=Y")

*SELECT(%ACCTVAR2%,"[ID]",ACCOUNT,"[ADDBALANCESHEET]=Y")

*SELECT(%TIMEVAR5%,"[INPUTVALUE5]",TIME,"[ID]='%TIME_SET%'")

*SELECT(%TIMEVAR6%,"[INPUTVALUE6]",TIME,"[ID]='%TIME_SET%'")

*SELECT(%TIMEVAR7%,"[INPUTVALUE7]",TIME,"[ID]='%TIME_SET%'")

//

*XDIM_MEMBERSET ACCOUNT = %ACCTVAR1%

*XDIM_ADDMEMBERSET ACCOUNT = %ACCTVAR2%

*XDIM_MEMBERSET CATEGORY = ACT_YTD_DEC

*XDIM_MEMBERSET DATASRC = COG_YTD_INP

*XDIM_MEMBERSET DETAIL = NO_DETAIL

*XDIM_MEMBERSET ENTITY = %ENTITY_SET%

*XDIM_MEMBERSET FLOW = F99

*XDIM_MEMBERSET INTCO = I_NONE

*XDIM_MEMBERSET RPTCURRENCY = LC

*XDIM_MEMBERSET TIME = %TIMEVAR5%

*XDIM_ADDMEMBERSET TIME = %TIMEVAR6%

//

*WHEN ACCOUNT.BALANCESHEET

*IS "Y"

//[CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[%TIMEVAR5%]

*REC(EXPRESSION=(((%VALUE%)+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[TMVL(0,%TIMEVAR6%)]))/2),CATEGORY="PLAN_YR5",DATASRC="MAN_INPUT",TIME="%TIME_SET%")

*ELSE

*WHEN ACCOUNT.ADDBALANCESHEET

*IS "Y"

//[CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[%TIMEVAR5%]

*REC(EXPRESSION=(((%VALUE%)+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[TMVL(0,%TIMEVAR6%)]))/2),CATEGORY="PLAN_YR5",DATASRC="MAN_INPUT",TIME=%TIME_SET%)

*ENDWHEN

*ENDWHEN

//

*COMMIT

//

-	-	-	Calculated result	Expected Result
ACCOUNT	“2018.INP”	“2019.INP”	PLAN_YR5, 2015.SEP	PLAN_YR5, 2015.SEP
1101_0608	50	-50	-100	-50
1101_0610		50	50	25
1101_0615	50		50	25

Account 1101_0608 contains data at the intersections for both 2018.INP and 2019.INP so it would find 2 values to pass to the calculations, however the values are not found simultaneously and there is no NOADD expression in the *REC function and it is the same process (i.e. no commit has taken place yet) so the values are added together.

If I were to limit the calculation with a *WHEN TIME *IS %TIMEVAR5% the following occurs:

-	-	-	Calculated result	Expected Result
ACCOUNT	“2018.INP”	“2019.INP”	PLAN_YR5, 2015.SEP	PLAN_YR5, 2015.SEP
1101_0608	50	-50	-50	-50
1101_0610		50		25
1101_0615	50		25	25

So to prevent value duplication I have to code the calculation twice The second time with the *WHEN TIME *IS %TIMEVAR6% which would do the following:

-	-	-	Calculated result	Expected Result
ACCOUNT	“2018.INP”	“2019.INP”	PLAN_YR5, 2015.SEP	PLAN_YR5, 2015.SEP
1101_0608	50	-50	-50	-50
1101_0610		50	25	25
1101_0615	50			25

In this case, because a value has been calculated in the target already, it would contain the right value.

Is there any way of preventing value duplication or exclusion from a calc without duplicating the statement?

I am otherwise happy that finally I have overcome this issue.

Thank you again Vadim

Regards,

Marthinus Viljoen

Closing Position - test 201509141127.txt.zip

Closing Position Logic - What it should be.txt.zip

[former_member186338 · Accepted solution]

Hi Marthinus,

To my mind the code you present looks extremely complex and contains a lot of useless statements...

Can be replaced by:

*SELECT(%ACCTVAR1%,"[ID]",ACCOUNT,"[BALANCESHEET]=Y")

*SELECT(%ACCTVAR2%,"[ID]",ACCOUNT,"[ADDBALANCESHEET]=Y")

*SELECT(%TIMEVAR5%,"[INPUTVALUE5]",TIME,"[ID]='%TIME_SET%'")

*SELECT(%TIMEVAR6%,"[INPUTVALUE6]",TIME,"[ID]='%TIME_SET%'")

*SELECT(%TIMEVAR7%,"[INPUTVALUE7]",TIME,"[ID]='%TIME_SET%'")

*XDIM_MEMBERSET ACCOUNT = %ACCTVAR1%,%ACCTVAR2%

*XDIM_MEMBERSET CATEGORY = ACT_YTD_DEC

*XDIM_MEMBERSET DATASRC = COG_YTD_INP

*XDIM_MEMBERSET DETAIL = NO_DETAIL

*XDIM_MEMBERSET ENTITY = %ENTITY_SET%

*XDIM_MEMBERSET FLOW = F99

*XDIM_MEMBERSET INTCO = I_NONE

*XDIM_MEMBERSET RPTCURRENCY = LC

*XDIM_MEMBERSET TIME = %TIMEVAR5%,%TIMEVAR6%

*WHEN TIME

*IS *

*REC(EXPRESSION=%VALUE%/2,CATEGORY=PLAN_YR5,DATASRC=MAN_INPUT,TIME=%TIME_SET%)

*ENDWHEN

*XDIM_MEMBERSET TIME = %TIMEVAR6%,%TIMEVAR7%

*WHEN TIME

*IS *

*REC(EXPRESSION=%VALUE%/2,CATEGORY=PLAN_YR6,DATASRC=MAN_INPUT,TIME=%TIME_SET%)

*ENDWHEN

Vadim

P.S. Never use useless COMMIT!

[former_member186338 · Accepted solution]

P.P.S. Explanation:

You want to read: 2018.INP, 2019.INP

Calculate average: ([TIME].[2018.INP]+[TIME].[2019.INP])/2

And store the result: in 2015.SEP

*XDIM_MEMBERSET TIME=2018.INP, 2019.INP

*WHEN TIME

*IS * // both scoped - 2018.INP or 2019.INP

*REC(EXPRESSION=%VALUE%/2,TIME=2015.SEP) //Write 1/2 to 2015.SEP

*ENDWHEN

Due to WHEN/ENDWHEN accumulation:

In 2015.SEP you will have: [TIME].[2018.INP]/2+[TIME].[2019.INP]/2

Hope it's clear!

Vadim

[former_member288457 · Accepted solution]

Hi Vadim,

I did not know that about *COMMIT

Your method makes sense, I will parse your suggested code tomorrow morning. I will advise on the outcome.

I've been staring myself blind against the existing Formula.

Thanks again.

Regards,

Marthinus Viljoen

[former_member186338 · Accepted solution]

COMMIT can be used with so called MDX calculations...

With WHEN/ENDWHEN COMMIT is useless, just reset the scope!

[former_member288457 · Accepted solution]

Hi Vadim

I am happy to confirm that you were right.

I was over-complicating a simple issue.

Your solution works perfectly.

Thank you, your help comes greatly appreciated.

Until a next time.

Regards,

Marthinus Viljoen

[former_member186338]

Hi Marthinus,

Incorrect number of brackets in the first *REC, have to be:

*REC(EXPRESSION=((([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[%TIMEVAR5%])+([CATEGORY].[ACT_YTD_DEC],[DATASRC].[COG_YTD_INP],[TIME].[%TIMEVAR6%]))/2,CATEGORY="PLAN_YR5",DATASRC="MAN_INPUT",TIME=%TIME_SET%)

Please check the second REC yourself.

And in general the script is strange...

The first REC is equivalent to:

*REC(EXPRESSION=(%VALUE%+[TIME].[%TIMEVAR6%])/2,CATEGORY="PLAN_YR5",DATASRC="MAN_INPUT",TIME=%TIME_SET%)

What do you want to achieve?

Vadim

P.S. Please read:

[former_member288457]

Hi Vadim,

Thank you for your prompt reply.

I have applied your suggested equivalent, but, validation states "Field '[TIME]' is unknown...."

Thanks, I will try debugging using UJKT and collect information as suggested in supplied link if I need to pose the question again.

Regards,

Marthinus Viljoen

[former_member186338]

Validation may report some errors - %TIME_SET% is empty during validation...

Use UJKT, fill Data Region, press Execute simulate...

But the issue is with the script in general! What logic you want to achieve? And other info from my document!

Vadim

P.S. Provide some samples... What do you have in the mentioned properties for 2015.09 for example?