Focal Point
Distinct Field Values on Header line
October 01, 2007, 02:40 AM
AnatessDistinct Field Values on Header line
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
WF 8.1.05 Windows
October 01, 2007, 03:51 AM
GamPTry 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
- Using AS 8.2.01 on Windows 10 - IE11. |
| in Focus since 1988 |
October 01, 2007, 06:35 AM
FrankDutchGamP
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)
|
Frank |
| prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7 |
October 01, 2007, 10:12 AM
AnatessThank 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.
WF 8.1.05 Windows
October 01, 2007, 11:21 AM
ihfdump 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
October 01, 2007, 03:22 PM
JimRiceHere'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
October 01, 2007, 03:24 PM
JimRiceI'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
WF DevStu 5.2.6/WF Srv 5.2.4/Win NT 5.2
October 02, 2007, 02:04 AM
Piipster...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
ttfn, kp
Access to most releases from R52x, on multiple platforms.
October 02, 2007, 06:04 AM
GamPAnd, 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
GamP
- Using AS 8.2.01 on Windows 10 - IE11. |
| in Focus since 1988 |
October 02, 2007, 10:24 PM
AnatessHi 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.
WF 8.1.05 Windows
October 03, 2007, 08:40 PM
Piipsterquote:
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.
ttfn, kp
Access to most releases from R52x, on multiple platforms.