I just got this strange request to put a list of distinct values for a particular field on the header line. I'm not quite sure how to code this. This is what I hope to accomplish using the car file:

     SALES REPORT FOR ENGLAND
BODYTYPES: CONVERTIBLE, HARDTOP, SEDAN

SEATS DEALER_COST RETAIL_COST SALES 
    2      11,719      13,978     0 
    4      14,940      17,850     0 
    5      11,194      13,491 12000 

Does that make sense?

Dev Studio 7.1.3, WinXP, SQL Server 2005

Try this snippet of code:

SET HOLDLIST=PRINTONLY
TABLE FILE CAR
BY BODYTYPE
WHERE COUNTRY EQ 'ENGLAND'
ON TABLE HOLD
END
-RUN
-SET &DSTHEAD = '';
-SET &SEP = '';
-LOOP
-READ HOLD &BDTP.12
-IF &IORETURN NE 0 THEN GOTO ENDLOOP;
-SET &DSTHEAD = &DSTHEAD || &SEP | &BDTP;
-SET &SEP = ', ';
-GOTO LOOP
-ENDLOOP
-SET &DSTHEAD = &DSTHEAD || '.';

TABLE FILE CAR
HEADING
"SALES REPORT FOR ENGLAND"
"BODYTYPES: &DSTHEAD"
SUM DCOST RCOST SALES
BY SEATS
WHERE COUNTRY EQ 'ENGLAND'
END

GamP

GamP

this is a smart solution.
thanks

BTW
would you be so kind to update your signature...

(het is zo makkelijk als je kunt zien welk platform iedereen gebruikt)

Thank you GamP!
Although, I forgot to mention that England is a Subhead. So, I actually won't have a WHERE COUNTRY EQ 'ENGLAND' clause, it would print each country's report one after the other. So, I guess I'll have to do the &DSTHEAD computation for every country, which means a TABLE FILE CAR...HOLD... for each country? Let me think about this some more.

dump all cars into save file. loop around reading them one at a time for the number of countries you have. place the country in a subhead with a where clause that is derived from the save file as it rewads thru it one at a time.
You can always (filedef) append each output together or create a compound report.
see below

TABLE FILE CAR PRINT COUNTRY
ON TABLE SAVE AS THESAVE
END
-SET &REX = &RECORDS;
-TYPE # OF COUNTRIES CAPTURED: &REX
-THELOOP
-REPEAT ENDREPEAT &REX TIMES
-READ THESAVE &COUNTRY.A10.
TABLE FILE CAR
"BODYTYPES: ON TABLE SUBHEAD
"FOR COUNTRY: &COUNTRY"
SUM DCOST RCOST SALES BY SEATS
WHERE COUNTRY EQ '&COUNTRY'
END
-ENDREPEAT

ira unix 5.3.6

Here's an example using hold files:

-*
-** HOLD DATA FROM CAR FILE
-*
TABLE FILE CAR
SUM
DEALER_COST
RETAIL_COST
SALES
BY COUNTRY
BY BODYTYPE
BY SEATS
ON TABLE HOLD AS HLD_CAR
END
-*
-** CREATE HOLD FILE(HLD_BTS) - BODYTYPES BY COUNTRY
-*
TABLE FILE HLD_CAR
BY COUNTRY
BY BODYTYPE
ON TABLE HOLD AS HLD_TMP1
END
-*
DEFINE FILE HLD_TMP1
BT_LIT/A138 = IF COUNTRY EQ LAST COUNTRY
THEN SUBSTR(138, BT_LIT, 1, 124, 124, 'A124') || ', ' || BODYTYPE
ELSE BODYTYPE;
END
-*
TABLE FILE HLD_TMP1
SUM
BT_LIT
BY COUNTRY
ON TABLE HOLD AS HLD_BTS FORMAT FOCUS INDEX COUNTRY
END
-*
-** JOIN HLD_CAR TO HLD_BTS AND CREATE REPORT
-*
JOIN COUNTRY IN HLD_CAR TO COUNTRY IN HLD_BTS AS J1
-*
TABLE FILE HLD_CAR
HEADING
"SALES REPORT FOR "BODYTYPES: " "
SUM
DEALER_COST
RETAIL_COST
SALES
BY COUNTRY NOPRINT
BY BT_LIT NOPRINT
BY SEATS
ON COUNTRY PAGE-BREAK REPAGE
END

Jim

WF DevStu 5.2.6/WF Srv 5.2.4/Win NT 5.2

I'll try again with HTML disabled.

-*
-** HOLD DATA FROM CAR FILE
-*
TABLE FILE CAR
SUM
DEALER_COST
RETAIL_COST
SALES
BY COUNTRY
BY BODYTYPE
BY SEATS
ON TABLE HOLD AS HLD_CAR
END
-*
-** CREATE HOLD FILE(HLD_BTS) - BODYTYPES BY COUNTRY
-*
TABLE FILE HLD_CAR
BY COUNTRY
BY BODYTYPE
ON TABLE HOLD AS HLD_TMP1
END
-*
DEFINE FILE HLD_TMP1
BT_LIT/A138 = IF COUNTRY EQ LAST COUNTRY
THEN SUBSTR(138, BT_LIT, 1, 124, 124, 'A124') || ', ' || BODYTYPE
ELSE BODYTYPE;
END
-*
TABLE FILE HLD_TMP1
SUM
BT_LIT
BY COUNTRY
ON TABLE HOLD AS HLD_BTS FORMAT FOCUS INDEX COUNTRY
END
-*
-** JOIN HLD_CAR TO HLD_BTS AND CREATE REPORT
-*
JOIN COUNTRY IN HLD_CAR TO COUNTRY IN HLD_BTS AS J1
-*
TABLE FILE HLD_CAR
HEADING
"SALES REPORT FOR <COUNTRY "
"BODYTYPES: <BT_LIT "
" "
SUM
DEALER_COST
RETAIL_COST
SALES
BY COUNTRY NOPRINT
BY BT_LIT NOPRINT
BY SEATS
ON COUNTRY PAGE-BREAK REPAGE
END

...and how about only one HOLD file and no JOIN:

-DEFAULTS &ECHO=ALL
TABLE FILE CAR
SUM
     DEALER_COST
     RETAIL_COST
     SALES
     COMPUTE BT_LIT/A138 = IF COUNTRY NE LAST COUNTRY 
	                       THEN BODYTYPE 
						   ELSE 
						      IF BODYTYPE EQ LAST BODYTYPE 
						      THEN LAST BT_LIT 
							  ELSE SUBSTR(138, LAST BT_LIT, 1, 124, 124, 'A124') 
							              || (', ' || BODYTYPE); 
BY COUNTRY 
BY HIGHEST TOTAL BT_LIT
BY BODYTYPE
BY SEATS
ON TABLE HOLD AS HOLDDATA
END
-RUN
DEFINE FILE HOLDDATA
BT_DISP/A138 = IF COUNTRY EQ LAST COUNTRY 
               THEN LAST BT_LIT
			   ELSE BT_LIT;
END
TABLE FILE HOLDDATA
HEADING CENTER
"SALES REPORT FOR <COUNTRY "
"BODYTYPE: <MAX.BT_DISP </1"
SUM
	 DEALER_COST
     RETAIL_COST
	 SALES
	 MAX.BT_DISP NOPRINT
BY COUNTRY NOPRINT
BY SEATS
ON COUNTRY PAGE-BREAK REPAGE
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
GRID=OFF, ORIENTATION=LANDSCAPE, SQUEEZE=ON,$
ENDSTYLE
END
  

And, to sum it all up into one table, one define, no hold file or join:

DEFINE FILE CAR
SEP/A2 = ', ';
BDTPL/I4   = ARGLEN(12, BODYTYPE, BDTPL);
CNTR/I4 WITH BODYTYPE = IF COUNTRY NE LAST COUNTRY THEN 1 ELSE CNTR+1;
CHECK/A200 = IF COUNTRY NE LAST COUNTRY THEN ' ' ELSE CHECK;
BDTP/A200  = IF COUNTRY NE LAST COUNTRY THEN ' ' ELSE BDTP;
YES/I4     = POSIT(CHECK, 200, BODYTYPE, 12, YES);
CHECK      = IF CNTR EQ 1 THEN BODYTYPE ELSE 
             OVRLAY(CHECK, 200, BODYTYPE, 12, CNTR*12, 'A200');
LL/I4      = IF CNTR EQ 1 THEN ARGLEN(200,BODYTYPE,LL) ELSE ARGLEN(200,BDTP,LL);
BDTP       = IF YES NE 0 THEN BDTP ELSE
             IF CNTR EQ 1 THEN BODYTYPE ELSE
             OVRLAY(BDTP, 200, BODYTYPE, BDTPL, ARGLEN(200,BDTP,'I4')+3, 'A200');
BDTP       = IF YES NE 0 THEN BDTP ELSE
             IF CNTR EQ 1 THEN BDTP ELSE
             OVRLAY(BDTP, 200, SEP, 2, LL+1, 'A200');
END

TABLE FILE CAR
SUM DCOST RCOST SALES
 BDTP NOPRINT CHECK NOPRINT
BY COUNTRY NOPRINT PAGE-BREAK
BY SEATS
HEADING CENTER
"SALES REPORT FOR COUNTRY <COUNTRY"
"BODYTYPES: <BDTP"
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
END

Thr trick is of course in the defines and the order they are being executed.
Pro of this solution is that it means one pass through the data and reusability.

GamP

Hi guys -
Thank you so very much for all the sample code! They all worked well!
GamP, I'm taking your solution. One pass is definitely a big Pro and it is simple enough for me to understand months from now when I have to support it in production.

quote:

CNTR/I4 WITH BODYTYPE = IF COUNTRY NE LAST COUNTRY THEN 1 ELSE CNTR+1

The only way this DEFINE will work is if the data is presorted in COUNTRY order as DEFINEs are evaluated as each row of data is read from the database.