I am summing the extprice and extcost for each salesrep (481,678,...) The Goals column is their monthly goal that is to be printed only one(1) time in the subfooting.
I am looking for an answer of how I can grab the monthly goal (goals), to print that one(1) without summing that goals for each record/salesrep;
I tried this logic with no success:
DEFINE FILE HOLD1 PRCUSTNUM/I6 = CUSTNUM; PREXTPRICE/D12.2SMB = EXTPRICE/100; PREXTCOST/D12.2SMB = IF GLOFFSET EQ 'PF' THEN 0.00 ELSE EXTCOST/100; PRGOALS/D12.2SMB = IF GOAL EQ LAST GOAL AND SALESREP NE LAST SALESREP THEN GOAL ELSE 0; <---- END
Any suggestions?
FYI, I'm using SQLPassthru to retrieve all my records and holding in HOLD1.
ThanksThis message has been edited. Last edited by: FP Mod Chuck,
July 09, 2020, 06:27 AM
Frans
You could try putting it in a parameter, something like this
TABLE FILE CAR
SUM
COMPUTE VALUE/A500 = IF LAST SALES LE 0 THEN EDIT(MAX.SALES) ELSE LAST VALUE ||','||EDIT(MAX.SALES);
BY SALES
WHERE SALES GT 0
ON TABLE HOLD
END
TABLE FILE HOLD
SUM VALUE
ON TABLE HOLD AS SUBVALUE
END
-RUN
-READFILE SUBVALUE
TABLE FILE CAR
SUM SALES
BY COUNTRY
FOOTING
"&VALUE"
END
PRGOALS/D12.2SMB = IF GOAL EQ LAST GOAL AND SALESREP NE LAST SALESREP THEN GOAL ELSE 0; <----
Try instead something similar to this Assuming that for a same SALESREP and CUSTNUM the GOAL is the same otherwise it doesn't really make sense to have different GOAL for the same SALESREP/CUTNUM; if it's the cas then we're missing a Key to make it unique
SET NODATA = ''
DEFINE FILE HOLD1
...
PRGOALS/D12.2SMB MISSING ON = IF SALESREP EQ LAST SALESREP THEN (IF CUSTNUM EQ LAST CUSTNUM THEN MISSING ELSE GOAL) ELSE GOAL;
END
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007