Allocation combined with *REC generation

[Former Member]

Hi,

I am on version 7.5 MS and currently struggeling with the following:

Step 1. I am writing to a yearly input container via EPM client and the affected underlying months should get exactly the same value. It's more or less a one-to-many copy.

Step 2. These newly generated monthly values should form a calculation A * B = C. This is done for two Accounts.

I decided to give Step 1 a try by using Script Logic allocation. Works without problems when run alone.

Also Step 2 is more or less straight forward. Works without problems when run alone.

Then I combined the two parts and separated it by a *commit.

Findings:

When I run the combined package as DEFAULT.LGF (this is what I need) it would not perform Step 2. At least it looks like it as all resulting values in C are put back to zero.

When I run the DM package DEFAULT FORMULAS afterwards, the combination works without problems.

As I said, I need to have that as default lgf. The intended routine is supposed to make the input process of users easier.

Can somebody point me into the right direction ? Is it the missing scoping that causes the problem ? It seems to be a triggering problem, ie. what triggers what.

BR

Wolfgang

Here comes the code in the default.lgf

// Section 1

*RUNALLOCATION

*FACTOR=1

//*FACTOR=1/COUNT

*DIM TIME WHAT=%YEAR%.INP; WHERE=BAS(%YEAR%.TOTAL)

*ENDALLOCATION

*COMMIT

// Section 2

*WHEN PARM   

*IS "VOL"

*WHEN ACCOUNT

*IS "CASH"

*REC(EXPRESSION = (%VALUE% * GET(ACCOUNT="TAXRATE",PARM="NO_PARM") * FLD(TIME.DAYSINPERIOD) / FLD(TIME.DAYSINYEAR)) , ACCOUNT="REBATES",PARM="NO_PARM")

*IS "ACCREC"

*REC(EXPRESSION = (%VALUE% * GET(ACCOUNT="TAXINCENTRATE",PARM="NO_PARM") * FLD(TIME.DAYSINPERIOD) / FLD(TIME.DAYSINYEAR) ) , ACCOUNT="INTERESTEXP",PARM="NO_PARM")

*ENDWHEN

*ENDWHEN

*COMMIT

[Former Member · Accepted solution]

Wolfgang Moeller wrote:

Hi,

I am on version 7.5 MS and currently struggeling with the following:

Step 1. I am writing to a yearly input container via EPM client and the affected underlying months should get exactly the same value. It's more or less a one-to-many copy.

Step 2. These newly generated monthly values should form a calculation A * B = C. This is done for two Accounts.

I decided to give Step 1 a try by using Script Logic allocation. Works without problems when run alone.

Also Step 2 is more or less straight forward. Works without problems when run alone.

Then I combined the two parts and separated it by a *commit.

Findings:

When I run the combined package as DEFAULT.LGF (this is what I need) it would not perform Step 2. At least it looks like it as all resulting values in C are put back to zero.

When I run the DM package DEFAULT FORMULAS afterwards, the combination works without problems.

As I said, I need to have that as default lgf. The intended routine is supposed to make the input process of users easier.

Can somebody point me into the right direction ? Is it the missing scoping that causes the problem ? It seems to be a triggering problem, ie. what triggers what.

BR

Wolfgang



Here comes the code in the default.lgf

// Section 1

*RUNALLOCATION
*FACTOR=1
//*FACTOR=1/COUNT
*DIM TIME WHAT=%YEAR%.INP; WHERE=BAS(%YEAR%.TOTAL)
*ENDALLOCATION

*COMMIT

// Section 2

*WHEN PARM   
*IS "VOL"

*WHEN ACCOUNT
*IS "CASH"
*REC(EXPRESSION = (%VALUE% * GET(ACCOUNT="TAXRATE",PARM="NO_PARM") * FLD(TIME.DAYSINPERIOD) / FLD(TIME.DAYSINYEAR)) , ACCOUNT="REBATES",PARM="NO_PARM")

*IS "ACCREC"
*REC(EXPRESSION = (%VALUE% * GET(ACCOUNT="TAXINCENTRATE",PARM="NO_PARM") * FLD(TIME.DAYSINPERIOD) / FLD(TIME.DAYSINYEAR) ) , ACCOUNT="INTERESTEXP",PARM="NO_PARM")

*ENDWHEN
*ENDWHEN

*COMMIT

OK, I would try to replace *REC(EXPRESSION=(%VALUE% *...) by :

*REC(FACTOR=1 * GET(ACCOUNT...) * FLD(...), ACCOUNT="INTERESTEXP", PARM="NO_PARM").

This is because I cannot see how %VALUE% would be initialized in the default script logic and I can only imagine that BPC is calculating something like this :

VOL / INTERESTEXP : value is 100 for all periods (say 4 periods for instance)

NO_PARM / TAXRATE : value is 0.05 for all periods

%VALUE% : nothing

For each record in VOL / INTERESTEXP (so 4 records) :

NO_PARM / REBATES = Nothing * 0.05 = 0

So each value written will be 0

It should work as long as there are some data in the model for VOL / CASH and VOL / ACCREC

Additionnally, I would try first without the *GET(ACCOUNT...) and * FDL(...) and check that it does copy the VOL / CASH amount into the INTERESTEXP / NO_PARM

If this works, then you can add the * GET(ACCOUNT...) into the formula and check if it works as intended and last if everything else works, add the * FLFD(...) part.

[Former Member · Accepted solution]

Hi David, Hi Jörg (you old rabbit ) ,

@David: I replaced the *REC(EXPRESSION = (%VALUE% *GET.. with *REC(FACTOR=GET... -> no joy.

Again: When I take out the allocation section (the one-to-many copy) and I ensure that the scope is taken care of in section 2, I get predictable and correct results when using an input form to enter single data. The combination CASH/VOL or the combination TAXRATE/NO_PARM delivers correct REBATES, for instance, even when I use the EXPRESSION version with the %VALUE%. Stand alone section works just fine.

________________________________________________________________

// Section 2

//* The following scope is new

*XDIM_MEMBERSET PARM = VOL,NO_PARM

*XDIM_MEMBERSET ACCOUNT = %ACCOUNT_SET%

*WHEN PARM   

*IS "VOL"

*WHEN ACCOUNT

*IS "CASH"

*REC(EXPRESSION = (%VALUE% * GET(ACCOUNT="TAXRATE",PARM="NO_PARM") * FLD(TIME.DAYSINPERIOD) / FLD(TIME.DAYSINYEAR)) , ACCOUNT="REBATES",PARM="NO_PARM")

*IS "ACCREC"

*REC(EXPRESSION = (%VALUE% * GET(ACCOUNT="TAXINCENTRATE",PARM="NO_PARM") * FLD(TIME.DAYSINPERIOD) / FLD(TIME.DAYSINYEAR) ) , ACCOUNT="INTERESTEXP",PARM="NO_PARM")

*ENDWHEN

*ENDWHEN

*COMMIT

____________________________________________________________________

Only when I include the allocation part (see above) in the default.lgf then I don't get correct results any more. The DM package Default Formulas solves all the problems again, ie correct results for the allocation in section 1 and the calculation in section 2. There seems to be some "magic" in default formulas that default.lgf does not have. I have a log of "default formulas" that I could provide.

If nothing else works for this combination, then maybe the strange FOR-NEXT construction of BPC for the copy part in section 1 will work. Or good ol' Excel formulas in the sheet (which I would like to avoid).

Regards.

Wolfgang

[JohnL · Accepted solution]

Hi Wolfgang,

Can you attach the Default formula log file?

Thanks,

[Former Member · Accepted solution]

Hi John,

no problem, here it comes.

Regards

Wolfgang

+++ I took out the copy-pasted log as the thread gets unreadable otherwise. Atached in a file now. +++ Wolfgang

[Former Member · Accepted solution]

the log as an attached file

log2.txt.zip

[Former Member · Accepted solution]

Hi everybody,

I solved the problem. The trouble maker was the missing scope for at least TIME. To be sure I added the scope for ENTITY and CATEGORY as well and now it works like a charm.

Scoping is was the key.

Thanks for your support !

Regards.

Wolfgang

[JohnL · Accepted solution]

Hi Wolfgang

I know you solved but if you look at the scope statements you'd see the differences as well:

From running in input template - DebugLogic.log

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

Building sub-query 2

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

Query Type:0

Max members:

Region:

DIMENSION:CATEGORY

ACTUAL

DIMENSION:DATASOURCE

INPUT

DIMENSION:DEPARTMENT

NODEPT

DIMENSION:DETAIL

LINE001

DIMENSION:ENTITY

Germany

DIMENSION:PARM

VOL

DIMENSION:PRODLINE

NOPRODLINE

DIMENSION:RPTCURRENCY

LC

DIMENSION:TIME

2004.INP

From running in DM - DebugLogic_42_506502507.Log

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

Building sub-query 2

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

Query Type:0

Max members:

Region:

DIMENSION:CATEGORY

ACTUAL

DIMENSION:ENTITY

Germany

DIMENSION:TIME

2004.JAN,2004.FEB,2004.MAR,2004.APR,2004.MAY,2004.JUN,2004.JUL,2004.AUG,2004.SEP,2004.OCT,2004.NOV,2004.DEC

Notice the Time dims ...

[Former Member]

Hi, there seem to be a missing part in your script :

you use a %VALUE% variable which does not seem to be initialized anywhere.

So either :

- you need to copy / paste here the whole content of your script along with a sample of the data you have

- you have a missing part in your script and that is why the second part overwrites your data with empty data.

[Former Member]

Hi Arnold, hi David,

thanks for coming back on that.

I am entering data for the yeary input container %YEAR%.INP used in the allocation part. Let's say 2016.INP. The Account would be "Cash", the Parm is "Vol". The sytsem than generates 12 monthly value for 2016 for the account "Cash" and the Parm "Vol" No problem here, works.

So my hope was that the second part would get the freshly ceated records and would trigger the calculation after the two *WHENs.

I copied the whole logic that I used. It does not work in default.lgf, but it does work using the DM package DEFAULT FORMULAS.

@David: you could be right when you say that something is missing when being used in default.lgf. Do you have any idea how I can carry the generated values from part 1 to part 2 and trigger the calcuation ?

Thanks in advance.

Wolfgang

[Former Member]

What dimension members are supposed to be %VALUE% ?

From what I see in this script the exact content is undefined so BPC won't be able to process anything there.

Do you have you run script log to share ? It would be useful to check if anything at all is processed in the second part of the script.

[Former Member]

The dimension member of %VALUE% should be "CASH" in the first case and "ACCREC" in the second. Both are ACCOUNTs. I know that the name of the Accounts do not make a lot of busisness sense. Its simply taking one account from Balance Sheet,multiply by a factor and get a P/L account.

Lets say I enter 10,000 as a yearly volume in .INP and a rate of 5%. The allocation generates both numbers in each moth of the year. This works.

The *REC part should do the calculation for each month, 10,000 * 5% = 500 for each month. The rest of the calculation is just weighing it depending on the number of days in the period and does not play a role here.

Using default.lgf I get the following log:

****************************************************************************************************

Start time --->6:52:25 PM  -  Date:4/11/2016  (build code:7.5.102)

****************************************************************************************************

User:demoadm

Appset:Demo5b

App:FINANCEDETAIL

Logic mode:0

Logic by:

Scope by:CATEGORY,DATASOURCE,DEPARTMENT,DETAIL,ENTITY,PARM,PRODLINE,RPTCURRENCY,TIME

Data File:

Debug File:D:\PC_MS\DATA\WebFolders\Demo5b\FINANCEDETAIL\PrivatePublications\demoadm\TempFiles\DebugLogic.log

Logic File:

Selection:DIMENSION:CATEGORY|ACTUAL|DIMENSION:DATASOURCE|INPUT|DIMENSION:DEPARTMENT|NODEPT|DIMENSION:DETAIL|LINE001|DIMENSION:ENTITY|GERMANY|DIMENSION:PARM|VOL|DIMENSION:PRODLINE|NOPRODLINE|DIMENSION:RPTCURRENCY|LC|DIMENSION:TIME|2004.INP|

Run mode:1

Query size:0

Delim:,

Query type:0

Simulation:0

Calc diff.:1

Formula script:

Max Members:

Test mode:0

Is Modelling:1

Number of logic calls:1

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

Call no. 1, logic:D:\PC_MS\DATA\WebFolders\Demo5b\AdminApp\FINANCEDETAIL\DEFAULT.LGX

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

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

Building sub-query 1

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

Query Type:0

Max members:

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

Executing allocation @A_1 -

FACTOR = 1

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

APP/DIM      WHAT         WHERE               USING     TOTAL

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

TIME    -->  %YEAR%.INP   BAS(%YEAR%.TOTAL)

-->

select distinct [ID],[ISBASEMEM] from mbrTIME where [PARENTH1] = '2004.TOTAL'

select distinct [ID],[ISBASEMEM] from mbrTIME where [PARENTH1] = '2004.Q1'

select distinct [ID],[ISBASEMEM] from mbrTIME where [PARENTH1] = '2004.Q2'

select distinct [ID],[ISBASEMEM] from mbrTIME where [PARENTH1] = '2004.Q3'

select distinct [ID],[ISBASEMEM] from mbrTIME where [PARENTH1] = '2004.Q4'

--Time to get base members:0.1 sec.

-- read WHAT

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

select [ACCOUNT],[CATEGORY],[DATASOURCE],[DEPARTMENT],[DETAIL],[ENTITY],[PARM],[PRODLINE],[RPTCURRENCY],[TIMEID],SIGNEDDATA

into #109296

from tblFactFINANCEDETAIL

where [CATEGORY] = 'ACTUAL' and [DATASOURCE] = 'INPUT' and [DEPARTMENT] = 'NODEPT' and [DETAIL] = 'LINE001' and [ENTITY] = 'GERMANY' and [PARM] = 'VOL' and [PRODLINE] = 'NOPRODLINE' and [RPTCURRENCY] = 'LC' and [TIMEID] = '20040000'

insert into #109296 ([ACCOUNT],[CATEGORY],[DATASOURCE],[DEPARTMENT],[DETAIL],[ENTITY],[PARM],[PRODLINE],[RPTCURRENCY],[TIMEID],SIGNEDDATA)

select [ACCOUNT],[CATEGORY],[DATASOURCE],[DEPARTMENT],[DETAIL],[ENTITY],[PARM],[PRODLINE],[RPTCURRENCY],[TIMEID],SIGNEDDATA

from tblFactWBFINANCEDETAIL

where [CATEGORY] = 'ACTUAL' and [DATASOURCE] = 'INPUT' and [DEPARTMENT] = 'NODEPT' and [DETAIL] = 'LINE001' and [ENTITY] = 'GERMANY' and [PARM] = 'VOL' and [PRODLINE] = 'NOPRODLINE' and [RPTCURRENCY] = 'LC' and [TIMEID] = '20040000' and SOURCE = 0

insert into #109296 ([ACCOUNT],[CATEGORY],[DATASOURCE],[DEPARTMENT],[DETAIL],[ENTITY],[PARM],[PRODLINE],[RPTCURRENCY],[TIMEID],SIGNEDDATA)

select [ACCOUNT],[CATEGORY],[DATASOURCE],[DEPARTMENT],[DETAIL],[ENTITY],[PARM],[PRODLINE],[RPTCURRENCY],[TIMEID],SIGNEDDATA

from tblFac2FINANCEDETAIL

where [CATEGORY] = 'ACTUAL' and [DATASOURCE] = 'INPUT' and [DEPARTMENT] = 'NODEPT' and [DETAIL] = 'LINE001' and [ENTITY] = 'GERMANY' and [PARM] = 'VOL' and [PRODLINE] = 'NOPRODLINE' and [RPTCURRENCY] = 'LC' and [TIMEID] = '20040000'

select a.[ACCOUNT],a.[CATEGORY],a.[DATASOURCE],a.[DEPARTMENT],a.[DETAIL],a.[ENTITY],a.[PARM],a.[PRODLINE],a.[RPTCURRENCY],a.[TIMEID],sum(SIGNEDDATA) as amtWHAT

into #WHAT_109296 from #109296 a

group by a.[ACCOUNT],a.[CATEGORY],a.[DATASOURCE],a.[DEPARTMENT],a.[DETAIL],a.[ENTITY],a.[PARM],a.[PRODLINE],a.[RPTCURRENCY],a.[TIMEID]

drop table #109296

--Time to load WHAT:0.0 sec.

go

select * from #WHAT_109296

-- read destination

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

select [ACCOUNT],[CATEGORY],[DATASOURCE],[DEPARTMENT],[DETAIL],[ENTITY],[PARM],[PRODLINE],[RPTCURRENCY],[TIMEID],SIGNEDDATA

into #109296

from tblFactFINANCEDETAIL

where [CATEGORY] = 'ACTUAL' and [DATASOURCE] = 'INPUT' and [DEPARTMENT] = 'NODEPT' and [DETAIL] = 'LINE001' and [ENTITY] = 'GERMANY' and [PARM] = 'VOL' and [PRODLINE] = 'NOPRODLINE' and [RPTCURRENCY] = 'LC' and [TIMEID] in ('20040100','20040200','20040300','20040400','20040500','20040600','20040700','20040800','20040900','20041000','20041100','20041200')

insert into #109296 ([ACCOUNT],[CATEGORY],[DATASOURCE],[DEPARTMENT],[DETAIL],[ENTITY],[PARM],[PRODLINE],[RPTCURRENCY],[TIMEID],SIGNEDDATA)

select [ACCOUNT],[CATEGORY],[DATASOURCE],[DEPARTMENT],[DETAIL],[ENTITY],[PARM],[PRODLINE],[RPTCURRENCY],[TIMEID],SIGNEDDATA

from tblFactWBFINANCEDETAIL

where [CATEGORY] = 'ACTUAL' and [DATASOURCE] = 'INPUT' and [DEPARTMENT] = 'NODEPT' and [DETAIL] = 'LINE001' and [ENTITY] = 'GERMANY' and [PARM] = 'VOL' and [PRODLINE] = 'NOPRODLINE' and [RPTCURRENCY] = 'LC' and [TIMEID] in ('20040100','20040200','20040300','20040400','20040500','20040600','20040700','20040800','20040900','20041000','20041100','20041200') and SOURCE = 0

insert into #109296 ([ACCOUNT],[CATEGORY],[DATASOURCE],[DEPARTMENT],[DETAIL],[ENTITY],[PARM],[PRODLINE],[RPTCURRENCY],[TIMEID],SIGNEDDATA)

select [ACCOUNT],[CATEGORY],[DATASOURCE],[DEPARTMENT],[DETAIL],[ENTITY],[PARM],[PRODLINE],[RPTCURRENCY],[TIMEID],SIGNEDDATA

from tblFac2FINANCEDETAIL

where [CATEGORY] = 'ACTUAL' and [DATASOURCE] = 'INPUT' and [DEPARTMENT] = 'NODEPT' and [DETAIL] = 'LINE001' and [ENTITY] = 'GERMANY' and [PARM] = 'VOL' and [PRODLINE] = 'NOPRODLINE' and [RPTCURRENCY] = 'LC' and [TIMEID] in ('20040100','20040200','20040300','20040400','20040500','20040600','20040700','20040800','20040900','20041000','20041100','20041200')

select a.[ACCOUNT],a.[CATEGORY],a.[DATASOURCE],a.[DEPARTMENT],a.[DETAIL],a.[ENTITY],a.[PARM],a.[PRODLINE],a.[RPTCURRENCY],a.[TIMEID],-sum(SIGNEDDATA) as signeddata

into #DIFF_109296 from #109296 a

group by a.[ACCOUNT],a.[CATEGORY],a.[DATASOURCE],a.[DEPARTMENT],a.[DETAIL],a.[ENTITY],a.[PARM],a.[PRODLINE],a.[RPTCURRENCY],a.[TIMEID]

drop table #109296

--Time to load DIFF:0.0 sec.

go

select * from #DIFF_109296

-- Merge WHAT with destination MEMBERS into WHERE2

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

select w.ACCOUNT,w.CATEGORY,w.DATASOURCE,w.DEPARTMENT,w.DETAIL,w.ENTITY,w.PARM,w.PRODLINE,w.RPTCURRENCY,r.TIMEID,amtWHAT,amtWHAT*0 as amtWHERE

into #WHERE2_109296 from #WHAT_109296 w,mbrTIME r

where

r.ID in(N'2004.FEB',N'2004.JAN',N'2004.MAR',N'2004.APR',N'2004.JUN',N'2004.MAY',N'2004.AUG',N'2004.JUL',N'2004.SEP',N'2004.DEC',N'2004.NOV',N'2004.OCT')

--Time to merge:0.0 sec.

go

select * from #WHERE2_109296

-- Apply factor

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

update #WHERE2_109296 set amtWHERE = cast(amtWHAT as float) * 1

--Time to apply factor:0.0 sec.

go

select * from #WHERE2_109296

-- Calculate difference

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

insert into #DIFF_109296 ([ACCOUNT],[CATEGORY],[DATASOURCE],[DEPARTMENT],[DETAIL],[ENTITY],[PARM],[PRODLINE],[RPTCURRENCY],[TIMEID],signeddata)

select [ACCOUNT],[CATEGORY],[DATASOURCE],[DEPARTMENT],[DETAIL],[ENTITY],[PARM],[PRODLINE],[RPTCURRENCY],[TIMEID],amtWHERE as signeddata

from #WHERE2_109296

select [ACCOUNT],[CATEGORY],[DATASOURCE],[DEPARTMENT],[DETAIL],[ENTITY],[PARM],[PRODLINE],[RPTCURRENCY],[TIMEID],sum(signeddata) as signeddata , 0 AS [WorkStatus]

into #RESULT_109296 from #DIFF_109296

group by [ACCOUNT],[CATEGORY],[DATASOURCE],[DEPARTMENT],[DETAIL],[ENTITY],[PARM],[PRODLINE],[RPTCURRENCY],[TIMEID]

--Time to calculate and count differences (12 found):0.0 sec.

-- Post to FactWB

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

insert into tblFactWBFINANCEDETAIL ([ACCOUNT],[CATEGORY],[DATASOURCE],[DEPARTMENT],[DETAIL],[ENTITY],[PARM],[PRODLINE],[RPTCURRENCY],[TIMEID],signeddata,source)

select [ACCOUNT],[CATEGORY],[DATASOURCE],[DEPARTMENT],[DETAIL],[ENTITY],[PARM],[PRODLINE],[RPTCURRENCY],[TIMEID],signeddata,source=0

from #RESULT_109296 where signeddata<>0

--Time to post:0.1 sec.

go

select * from #RESULT_109296 where signeddata<>0

-- drop temp tables

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

drop table #WHAT_109296,#WHERE_109296,#DIFF_109296,#RESULT_109296,#WHERE2_109296,#USING_109296,#TOTAL_109296

Time to run Allocation:.3 sec.

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

Building sub-query 2

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

Query Type:0

Max members:

Region:

DIMENSION:CATEGORY

ACTUAL

DIMENSION:DATASOURCE

INPUT

DIMENSION:DEPARTMENT

NODEPT

DIMENSION:DETAIL

LINE001

DIMENSION:ENTITY

Germany

DIMENSION:PARM

VOL

DIMENSION:PRODLINE

NOPRODLINE

DIMENSION:RPTCURRENCY

LC

DIMENSION:TIME

2004.INP

Loading TIME.DAYSINPERIOD,DAYSINYEAR,TIMEID

Time to load properties:0.0 sec.

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

select ACCOUNT,PARM,TIMEID,SIGNEDDATA

into #tblTempLogic_993813

from tblFactFINANCEDETAIL

WHERE  CATEGORY=N'ACTUAL' AND  DATASOURCE=N'INPUT' AND  DEPARTMENT=N'NODEPT' AND  DETAIL=N'LINE001' AND  ENTITY=N'GERMANY' AND  PARM=N'VOL' AND  PRODLINE=N'NOPRODLINE' AND  RPTCURRENCY=N'LC' AND  TIMEID=N'20040000'

insert into #tblTempLogic_993813 (ACCOUNT,PARM,TIMEID,SIGNEDDATA)

select ACCOUNT,PARM,TIMEID,SIGNEDDATA

from tblFactWBFINANCEDETAIL

WHERE  CATEGORY=N'ACTUAL' AND  DATASOURCE=N'INPUT' AND  DEPARTMENT=N'NODEPT' AND  DETAIL=N'LINE001' AND  ENTITY=N'GERMANY' AND  PARM=N'VOL' AND  PRODLINE=N'NOPRODLINE' AND  RPTCURRENCY=N'LC' AND  TIMEID=N'20040000'

and SOURCE = 0

insert into #tblTempLogic_993813 (ACCOUNT,PARM,TIMEID,SIGNEDDATA)

select ACCOUNT,PARM,TIMEID,SIGNEDDATA

from tblFAC2FINANCEDETAIL

WHERE  CATEGORY=N'ACTUAL' AND  DATASOURCE=N'INPUT' AND  DEPARTMENT=N'NODEPT' AND  DETAIL=N'LINE001' AND  ENTITY=N'GERMANY' AND  PARM=N'VOL' AND  PRODLINE=N'NOPRODLINE' AND  RPTCURRENCY=N'LC' AND  TIMEID=N'20040000'

select tmpTable.ACCOUNT,tmpTable.PARM,tmpTable.TIMEID,sum(SIGNEDDATA) as SIGNEDDATA

from #tblTempLogic_993813 as tmpTable

group by tmpTable.ACCOUNT,tmpTable.PARM,tmpTable.TIMEID

drop table #tblTempLogic_993813

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

Time to load Source data:0.2 sec.

2 records to process

Time to Prepare data process:0.0 sec.

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

select ACCOUNT,PARM,TIMEID,SIGNEDDATA

into #tblTempLogic_740927

from tblFactFINANCEDETAIL

WHERE  ACCOUNT in (N'REBATES',N'INTERESTEXP') AND  CATEGORY=N'ACTUAL' AND  DATASOURCE=N'INPUT' AND  DEPARTMENT=N'NODEPT' AND  DETAIL=N'LINE001' AND  ENTITY=N'GERMANY' AND  PARM in (N'NO_PARM',N'VOL') AND  PRODLINE=N'NOPRODLINE' AND  RPTCURRENCY=N'LC' AND  TIMEID=N'20040000'

insert into #tblTempLogic_740927 (ACCOUNT,PARM,TIMEID,SIGNEDDATA)

select ACCOUNT,PARM,TIMEID,SIGNEDDATA

from tblFactWBFINANCEDETAIL

WHERE  ACCOUNT in (N'REBATES',N'INTERESTEXP') AND  CATEGORY=N'ACTUAL' AND  DATASOURCE=N'INPUT' AND  DEPARTMENT=N'NODEPT' AND  DETAIL=N'LINE001' AND  ENTITY=N'GERMANY' AND  PARM in (N'NO_PARM',N'VOL') AND  PRODLINE=N'NOPRODLINE' AND  RPTCURRENCY=N'LC' AND  TIMEID=N'20040000'

and SOURCE = 0

insert into #tblTempLogic_740927 (ACCOUNT,PARM,TIMEID,SIGNEDDATA)

select ACCOUNT,PARM,TIMEID,SIGNEDDATA

from tblFAC2FINANCEDETAIL

WHERE  ACCOUNT in (N'REBATES',N'INTERESTEXP') AND  CATEGORY=N'ACTUAL' AND  DATASOURCE=N'INPUT' AND  DEPARTMENT=N'NODEPT' AND  DETAIL=N'LINE001' AND  ENTITY=N'GERMANY' AND  PARM in (N'NO_PARM',N'VOL') AND  PRODLINE=N'NOPRODLINE' AND  RPTCURRENCY=N'LC' AND  TIMEID=N'20040000'

select tmpTable.ACCOUNT,tmpTable.PARM,tmpTable.TIMEID,sum(SIGNEDDATA) as SIGNEDDATA

from #tblTempLogic_740927 as tmpTable

group by tmpTable.ACCOUNT,tmpTable.PARM,tmpTable.TIMEID

drop table #tblTempLogic_740927

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

Time to load Destination data:0.0 sec.

0 records in destination region

Time to initialize destination:0.0 sec.

Time to get source data:0.0 sec.

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

2 scanned records

0 skipped records

2 processed records

2 records generated

Time to Scan data:0.0 sec.

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

Time to calculate difference:0.0

Time to prepare final strings:0.0

Total processing time:0.0

App: FINANCEDETAIL - Records to be posted are 0  (calc diff = 1)

CATEGORY,DATASOURCE,DEPARTMENT,DETAIL,ENTITY,PRODLINE,RPTCURRENCY,ACCOUNT,PARM,TIME,SIGNEDDATA

Time to post records:0.0 sec.

call 1 completed and data posted in 0.7 sec.

Run completed in 0.8 sec.

****************************************************************************************************

End time --->6:52:26 PM  -  Date:4/11/2016

****************************************************************************************************

[jrg_finster3]

Hi Wolfgang

(you old house )

in 7.5 i think for a "GET" Statement it was necessary to have records retrieved via GET in the scope.

In your case this means you need to have PARM=NO_PARM in scope.

Are you writing on NO_PARM in your schedule?

Try to remove every "GET" or replace it by "100" just to test if you are generating 0-records because of GET and Scope.

Regards

Jörg

*edit* just saw that david also mentioned to remove the GET's in his large answer

So pls follow Davids idea. I think ist related to missing scope for GET

[Former Member]

Hi Wolfgang,

for which period are you entering data and for which period(s) would you expect the script to write data?

BR,
Arnold