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,

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
 

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

Thank you Frans and MartinY for the suggestions.

Please consider this closed

I used Martiny's version...


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