Focal Point
[SOLVED] Printing a field once that appears on multiple lines in the subfooting

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/5137058396

July 08, 2020, 12:10 PM
tlbrydie
[SOLVED] Printing a field once that appears on multiple lines in the subfooting
Hello everyone, I hope everyone is will. I have a issue. I am trying to print a field that occurs on multiple lines in a subfooting


I have records like this:

custnum,salesrep,extprice,extcost,goals
1234,481,2400.00,400.00,10000.00
1234,481,2888.00,700.00,10000.00
3998,481,757.00,45.00,10000.00
8993,678,7890.00,4500.00,50000.00
4366,678,7843.00,4232.00,50000.00
7889,678,7113.00,45645.00,50000.00

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.

Thanks

This 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
 



Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
July 09, 2020, 07:59 AM
MartinY
quote:
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
July 09, 2020, 11:04 AM
tlbrydie
Thank you Frans and MartinY for the suggestions.

Please consider this closed
July 09, 2020, 11:18 AM
tlbrydie
I used Martiny's version...


It worked perfectly. thank you both very much !!!