DEFINE FILE CAR NEWMODEL/A24=PARAG(24, MODEL, ';', 8, NEWMODEL); MODELP1/A8=GETTOK(NEWMODEL, 24, 1, ';', 8, MODELP1); MODELP2/A8=GETTOK(NEWMODEL, 24, 2, ';', 8, MODELP2); MODELP3/A8=GETTOK(NEWMODEL, 24, 3, ';', 8, MODELP3); END TABLE FILE CAR SUM RCOST ACROSS COUNTRY AS '' ACROSS MODEL NOPRINT ACROSS MODELP1 AS '' ACROSS MODELP2 AS '' ACROSS MODELP3 AS '' BY CAR -*WHERE COUNTRY EQ 'W GERMANY' ON TABLE PCHOLD FORMAT EXL2K ON TABLE SET STYLE * TYPE=REPORT, ORIENTATION=LANDSCAPE, FONT='ARIAL',$ -*SQUEEZE=ON, WRAP=0.75,$ -*TYPE=REPORT, COLUMN=COUNTRY, WRAP=1.00,$ TYPE=ACROSSVALUE, ACROSS=MODELP1, COLOR=RED, BORDER-LEFT=MEDIUM,BORDER-RIGHT=MEDIUM,BORDER-TOP=MEDIUM,$ TYPE=ACROSSVALUE, ACROSS=MODELP2, COLOR=RED, BORDER-LEFT=MEDIUM,BORDER-RIGHT=MEDIUM,$ TYPE=ACROSSVALUE, ACROSS=MODELP3, COLOR=RED, BORDER-LEFT=MEDIUM,BORDER-RIGHT=MEDIUM,$ TYPE=ACROSSTITLE, ACROSS=MODELP1, BACKCOLOR=NAVY, COLOR=WHITE,$ TYPE=TITLE, BORDER=MEDIUM, BACKCOLOR=NAVY, COLOR=WHITE,$ TYPE=DATA , BORDER=MEDIUM,$ ENDSTYLE ENDThis message has been edited. Last edited by: FP Mod Chuck,
INTERCEP TR7 V12XKE XJ12L 504 4 OR III AUTO AUTO DOOR CAR ALFA ROMEO E F AUDI N R BMW G A DATSUN L N JAGUAR A 8,878 13,491 C JENSEN N 17,850 E MASERATI D PEUGEOT 5,610 TOYOTA TRIUMPH 5,100
quote:Originally posted by Clinton Side-Kick:
Do you want to display the Country value vertically next to the Model value?
In which case you may get this done with a define? Perhaps using the age old McGuiver technique might be needed too.
-* First, create the CARTST master from the lines below: -* FILENAME=CARTST,SUFFIX=FOC,$ -* SEGNAME=ONLY,SEGTYPE=S3,$ -* FIELDNAME=COUNTRY,,A10,$ -* FIELDNAME=CARLST,,A16,$ -* FIELDNAME=MYMOD,,A6,$ -* FIELDNAME=B,,A1,$ -* FIELDNAME=AVEDC,,D6c,MISSING=ON,$ -* END -* Now, get some shortened fields from the car file, -* as well as aliases for CAR (CARLST) to avoid confusion JOIN CLEAR * SET NODATA = ' ' SET PANEL = 130 -RUN DEFINE FILE CAR MYMOD/A6 = EDIT(MODEL,'999999') ; MYDC/D5c = DCOST ; AVEDC/D5c = DCOST ; BLNK/A1 WITH COUNTRY = ' '; CARLST/A16 = CAR ; END -RUN -* Now, create a Focus file of a simple list of all -* distict cars, along with a blank field to be -* used as an index for MCGUYVER (cartesion product) technique TABLE FILE CAR BY CARLST ON TABLE HOLD END -RUN DEFINE FILE HOLD MYL/I2 = MYL + 1 ; BLNK/A1 WITH E01 = ' '; END -RUN TABLE FILE HOLD PRINT BLNK MYL CARLST ON TABLE HOLD AS CARLIST FORMAT FOCUS INDEX BLNK END -RUN -* Here we find the alphebetically lowest model for each -* country. For this lowest model for each country, -* we will create fake new records for each car in carlist, -* that contains our magic character B which is the character -* of the COUNTRY that will be printed for our vertical country title. TABLE FILE CAR BY BLNK BY COUNTRY BY LOWEST 1 MYMOD ON TABLE HOLD END -RUN JOIN BLNK IN HOLD TO ALL BLNK IN CARLIST DEFINE FILE HOLD B/A1 = SUBSTRING(COUNTRY,MYL,1) ; END -RUN TABLE FILE HOLD PRINT COUNTRY CARLST B MYL MYMOD ON TABLE HOLD END -RUN -* We now have a hold file containing fake records for each country/fst model -* combination obtained using the MCGUYVER (cartesion product) technique. -* We will load the CARTST file with this data first, and then load the -* rest of the CAR file data (ave dcost, country, car, and model). CREATE FILE CARTST NOMSG -RUN JOIN CLEAR * -RUN MODIFY FILE CARTST COMPUTE MYL/I2 = ; FIXFORM FROM HOLD COMPUTE AVEDC/D6 = MISSING ; MATCH COUNTRY CARLST MYMOD ON NOMATCH INCLUDE ON MATCH REJECT DATA ON HOLD END -RUN TABLE FILE CAR SUM AVE.AVEDC BY COUNTRY BY CAR BY MYMOD ON TABLE HOLD END -RUN MODIFY FILE CARTST FIXFORM FROM HOLD MATCH COUNTRY CAR MYMOD ON MATCH UPDATE AVEDC ON NOMATCH COMPUTE B = ' '; ON NOMATCH INCLUDE DATA ON HOLD END -RUN -* finally, the final table TABLE FILE CARTST HEADING CENTER "AVERAGE DEALER COST FOR SELECTED MODELS" " " SUM B AS ' ' AVEDC AS ' ' ACROSS COUNTRY ACROSS MYMOD IF COUNTRY NE ITALY IF MYMOD OMITS '530I' BY CAR END -RUN -EXIT Here is the output: AVERAGE DEALER COST FOR SELECTED MODELS COUNTRY ENGLAND FRANCE JAPAN W GERMANY MYMOD INTERC TR7 V12XKE XJ12L 504 4 B210 2 COROLL 100 LS 2002 2 3.0 SI CARLST ------------------------------------------------------------------------------------------------------------------------------- ALFA ROMEO E F J W AUDI N R A 5063 BMW G A P G 5900 10500 DATSUN L N A 2626 E JAGUAR A 7427 11194 C N R JENSEN N 14940 E M MASERATI D A PEUGEOT 4631 N TOYOTA 2886 Y TRIUMPH 4292
CREATE FILE CARTST NOMSG
-RUN
0 ERROR AT OR NEAR LINE 73 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: CARTST
(FOC009) Request failed validation, not executed.
quote:-* First, create the CARTST master from the lines below:
-* FILENAME=CARTST,SUFFIX=FOC,$
-* SEGNAME=ONLY,SEGTYPE=S3,$
-* FIELDNAME=COUNTRY,,A10,$
-* FIELDNAME=CARLST,,A16,$
-* FIELDNAME=MYMOD,,A6,$
-* FIELDNAME=B,,A1,$
-* FIELDNAME=AVEDC,,D6c,MISSING=ON,$
-* END
EX -LINES * EDAPUT MASTER,CARTST,CV,FILE FILENAME=CARTST,SUFFIX=FOC,$ SEGNAME=ONLY,SEGTYPE=S3,$ FIELDNAME=COUNTRY,,A10,$ FIELDNAME=CARLST,,A16,$ FIELDNAME=MYMOD,,A6,$ FIELDNAME=B,,A1,$ FIELDNAME=AVEDC,,D6c,MISSING=ON,$ EDAPUT*
In FOCUS since 1986 | WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 | ||
WebFOCUS App Studio 8.2.06 standalone on Windows 10 |