Skip to Content
0
Former Member
Mar 03, 2009 at 07:51 AM

A better way for cross-application calculation scripts?

25 Views

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