[SOLVED] Want to show total BAD items in column in GOOD report
I am trying to create a report to show number of units which meet certain criteria (where SFLAG EQ 'GOOD'), with a column in the report showing the total number of units which do not meet these criteria (where SFLAG EQ 'BAD').
When I run the report, I am only interested in showing the GOOD data but I want a column in the report which shows the total # of BAD items by unit (COUNTRY).
I have been working with the car file to try to get the answer, but I am missing something...
Code:
quote:
-SET &ECHO=ALL; -* SET NODATA = ' ',ASNAMES = ON -* TABLE FILE CAR PRINT CAR MODEL SEATS COMPUTE SFLAG/A4 MISSING ON = IF SEATS EQ '4' OR '5' THEN 'GOOD' ELSE IF SEATS EQ '2' THEN 'BAD' ELSE MISSING; -* BY COUNTRY ON TABLE HOLD AS TIGER END -RUN -* DEFINE FILE TIGER BDCNT/I9 MISSING ON = IF SFLAG EQ 'BAD' THEN 1 ELSE MISSING; GDCNT/I9 MISSING ON = IF SFLAG EQ 'GOOD' THEN 1 ELSE MISSING; END -* TABLE FILE TIGER PRINT CAR MODEL SEATS BDCNT GDCNT SFLAG BY COUNTRY ON TABLE HOLD AS PUMA END -RUN -* ?FF PUMA -RUN -* TABLE FILE PUMA HEADING CENTER "RPT ONE" SUM CNT.BDCNT AS 'BADONES' BY COUNTRY -* PRINT CAR MODEL SEATS BY COUNTRY WHERE SFLAG EQ 'GOOD'; ON TABLE HOLD AS LEOPARD END -RUN -* TABLE FILE LEOPARD " SFLAG EQ GOOD " SUM CAR MODEL SEATS LST.BADONES -* BY COUNTRY END -RUN -*
Any ideas?
Thanks!This message has been edited. Last edited by: Kerry,
you've eliminated all of your bad records so there's no way to count them. Try computing a value on every good record that is the tot.bdcnt WITHOUT using a WHERE, hold it then use max.bdcnt to display the bad count, but use a WHERE to only display to good values.
Regards,
Darin
In FOCUS since 1991 WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex WF Client: 77 on Linux w/Tomcat
June 26, 2009, 01:50 PM
mgrackin
Tom, Try using MATCH FILE ...
MATCH FILE CAR SUM CNT.SALES AS 'BADSALES' WHERE SALES LT 10000 BY COUNTRY BY CAR RUN FILE CAR PRINT RCOST DCOST SALES BY COUNTRY BY CAR BY MODEL BY BODYTYPE WHERE SALES GE 10000 AFTER MATCH HOLD NEW END -RUN TABLE FILE HOLD PRINT * END
Thanks!
Mickey
FOCUS/WebFOCUS 1990 - 2011
June 28, 2009, 12:22 AM
Tomsweb
Thanks Mickey, Here is what I ended up with...
quote:
-* File bad_cars.fex -SET &ECHO=ALL; -* SET ASNAMES=ON -* MATCH FILE CAR SUM CNT.SALES AS 'LT_10K' BY COUNTRY
WHERE SALES LT 10000; RUN -* FILE CAR PRINT CAR MODEL SALES BY COUNTRY WHERE SALES GE 10000; AFTER MATCH HOLD AS X12 OLD-OR-NEW END -RUN -* TABLE FILE X12 HEADING CENTER "AFTER THE MATCH" SUM -*LST.LT_10K CAR MODEL SALES LST.LT_10K BY COUNTRY END -RUN -* -* LET'S GET THE REAL DATA AND COUNTS DEFINE FILE CAR XFLAG/I9 MISSING ON = IF SALES LT 10000 THEN 1 ELSE MISSING; ZFLAG/I9 MISSING ON = IF SALES GE 10000 THEN 1 ELSE MISSING; END -* TABLE FILE CAR HEADING CENTER " THIS IS NORMAL DATA" SUM CNT.XFLAG AS 'LT_10K' CNT.ZFLAG AS 'GE_10K' BY COUNTRY -* PRINT CAR MODEL SALES BY COUNTRY END -RUN