Dear Experts,
I know cross-application calculations are fairly easy with *lookup and *Destination_app .
But looks like it still has limitations with calculations a bit more complicated.
The script below is already done to do the following in sequences.
1. 09_fin.lgf add up totals in Finance application and store into InvCost application
in DataSrc member "Temp". This script is ran in Finance application
2. 09_Inv.lgf adds up another sets of totals and stored into DataSrc "Fac". Then I do
my next calculation ->-- %VALUE% * Temp / Fac and stored into my result members.
The above scripts works fine but creates a lot of use-only-once values in the database
and cube. A better way should be using the temperary variables with # sign. I did try
to use #Temp, #Fac to store temperary data to do the calculation but they didn't work.
I'm posting the scripts below for everyone to take a look. Any idea how to make the code more reliable?
09_fin.lgf
*SELECT(%FACTO%,"ID","FACTORY","[FUNCTION1]<>'COGS' AND [FUNCTION1]<>'ASU' AND [FUNCTION1]<>''") *XDIM_MEMBERSET TIME=%TIME_SET% *XDIM_MEMBERSET CATEGORY=ACTUAL *XDIM_MEMBERSET RPTCURRENCY=LC *XDIM_MEMBERSET LOB=00000 *XDIM_MEMBERSET FACTORY=%FACTO% *XDIM_MEMBERSET DATASRC=Input,ErpCor,ErpAdj,Madj,CExpAdj *XDIM_MEMBERSET COSTCENTER=111,112,115 *DESTINATION_APP=INVCOST *SKIP_DIM=COSTCENTER,LOB,SACCT *RENAME_DIM MACCT=INVACCT *ADD_DIM INTFAC=NONINTFAC *ADD_DIM PARTNO=NONPARTNO *WHEN MACCT *IS "5401" *REC(DATASRC="TEMP") *IS "5501" *REC(MACCT="5501_R",DATASRC="TEMP") *ENDWHEN *COMMIT
09_Inv.lgf
//CALCULATE TOTAL SAVE INTO FAC *SELECT(%FACTO%,"ID","FACTORY","[FUNCTION1]<>'COGS' AND [FUNCTION1]<>'ASU' AND [FUNCTION1]<>''") *XDIM_MEMBERSET TIME=%TIME_SET% *XDIM_MEMBERSET CATEGORY=ACTUAL *XDIM_MEMBERSET DATASRC=C_M3_W *XDIM_MEMBERSET RPTCURRENCY=LC *XDIM_MEMBERSET FACTORY=%FACTO% *XDIM_MEMBERSET INVACCT=INV_220,INV_240 *WHEN * *IS * *REC(DATASRC="FAC",INTFAC="NONINTFAC",PARTNO="NONPARTNO",INVACCT="INV0000") *ENDWHEN *COMMIT //FINAL CALCULATION - TEMP(SRC) * FACTOR(%VALUE%) / TOTAL(FAC) *SELECT(%FACTO%,"ID","FACTORY","[FUNCTION1]<>'COGS' AND [FUNCTION1]<>'ASU' AND [FUNCTION1]<>''") *XDIM_MEMBERSET TIME=%TIME_SET% *XDIM_MEMBERSET CATEGORY=ACTUAL *XDIM_MEMBERSET RPTCURRENCY=LC *XDIM_MEMBERSET FACTORY=%FACTO% *WHEN INVACCT *IS "INV_220","INV_240" *WHEN DATASRC *IS "C_M3_W" *REC(EXPRESSION=%VALUE% * GET(INVACCT="5401",INTFAC="NONINTFAC",PARTNO="NONPARTNO",DATASRC="TEMP") / GET(INTFAC="NONINTFAC",PARTNO="NONPARTNO",DATASRC="FAC",INVACCT="INV0000"),INVACCT="5401",INTFAC="NONINTFAC",DATASRC="INPUT") *REC(EXPRESSION=%VALUE% * GET(INVACCT="5501_R",INTFAC="NONINTFAC",PARTNO="NONPARTNO",DATASRC="TEMP") / GET(INTFAC="NONINTFAC",PARTNO="NONPARTNO",DATASRC="FAC",INVACCT="INV0000"),INVACCT="5501_R",INTFAC="NONINTFAC",DATASRC="INPUT") *ENDWHEN *ENDWHEN *COMMIT