I have a report like the following code:


TABLE FILE CAR
SUM
RETAIL_COST
DEALER_COST
FOLD-LINE
BY
COUNTRY
BY
CAR
SUM
RETAIL_COST
DEALER_COST
BY
COUNTRY
BY
CAR NOPRINT
BY
BODYTYPE
ON TABLE PCHOLD FORMAT PDF
END
The requirements for the report has changed and the users now want to see multiple lines on the summary line of the report. So they want something that looks like the follow:

-* Summary Infomration
COUNTRY CAR RETAIL_COST DEALER_COST
ENGLAND JAGUAR 22,369 18,621
JENSEN 17,850 14,940
TRIUMPH 5,100 4,292
-* Detail Information
BODYTYPE RETAIL_COST DEALER_COST
CONVERTIBLE 8,878 7,427
HARDTOP 5,100 4,292
SEDAN 31,341 26,134
Does anyone have any ideas how this can be done? Also our standard output is PDF.

This message has been edited. Last edited by: <Mabel>,

I'm thinking a COMPOUND report may be the way to go.

I don't think a compound will since I needed to repeat the summary and detail like the following:

-* Summary Infomration
COUNTRY CAR RETAIL_COST DEALER_COST
ENGLAND JAGUAR 22,369 18,621
JENSEN 17,850 14,940
TRIUMPH 5,100 4,292
-* Detail Information
BODYTYPE RETAIL_COST DEALER_COST
CONVERTIBLE 8,878 7,427
HARDTOP 5,100 4,292
SEDAN 31,341 26,134
-*
-* Summary Infomration
COUNTRY CAR RETAIL_COST DEALER_COST
FRANCE PEUGEOT 5,610 4,631
-* Detail Information
BODYTYPE RETAIL_COST DEALER_COST
SEDAN 5,600 4,631
Can a compound report do this?

This message has been edited. Last edited by: <Mabel>,

Curtis,

Try this. It isn't precisely in the format you want, but that's just a matter of presentation.

-STEP1
DEFINE FILE CAR
TAG/I1=1;
END
TABLE FILE CAR
SUM
RETAIL_COST
DEALER_COST
BY COUNTRY
BY TAG
BY CAR
ON TABLE HOLD AS CARS
END
-RUN

-STEP2
DEFINE FILE CAR
TAG/I1=2;
END
TABLE FILE CAR
SUM
RETAIL_COST
DEALER_COST
BY COUNTRY
BY TAG
BY BODYTYPE
ON TABLE HOLD AS BODY
END
-RUN

-STEP3
MATCH FILE CARS
PRINT
CAR
RETAIL_COST
DEALER_COST
BY COUNTRY
BY TAG
RUN
FILE BODY
PRINT
BODYTYPE
RETAIL_COST
DEALER_COST
BY COUNTRY
BY TAG
AFTER MATCH HOLD AS CARBODY OLD-OR-NEW
END
-RUN

-STEP4
DEFINE FILE CARBODY
RETAIL/D7 = IF TAG EQ 1 THEN E04 ELSE E07;
DEALER/D7 = IF TAG EQ 1 THEN E05 ELSE E08;
END
TABLE FILE CARBODY
PRINT
RETAIL AS 'Retail Cost'
DEALER AS 'Dealer Cost'
BY COUNTRY
BY TAG NOPRINT
BY CAR
BY BODYTYPE
END

I put the -STEP statements in just to talk about this: If the DEFINE in STEP4 is something of a mystery, then put this code right after -STEP4:

TABLE FILE CARBODY
?FF
END
-EXIT

This will reveal that CARBODY contains two RETAIL_COST fields and two DEALER_COST fields. PRINT *, instead of ?FF reveals the same thing, but it doesn't show alias names. What it would show, though, is that for any given record only one of the duplicate fields has a value. I leave you to figure out why that is so.

An alternative

SET HOLDLIST =PRINTONLY

TABLE FILE CAR
SUM
COMPUTE CNO/I5=LAST CNO +1;
BY COUNTRY
ON TABLE HOLD AS DRIVER FORMAT ALPHA
END
-RUN
-SET &NOCNTRYS=&LINES;
SET COMPOUND=OPEN NOBREAK

-RUN
FILEDEF SAVE DISK SAVE.FTM
-RUN
-SET &CNTR=0;
-REPEAT ENDREPEAT &NOCNTRYS TIMES
-SET &CNTR=&CNTR+1;
TABLE FILE DRIVER
PRINT COUNTRY
WHERE CNO EQ &CNTR
ON TABLE SAVE
END
-RUN
-READ SAVE &CNTRY.A10.
-RUN
-SET &COUNTRY=TRUNCATE('&CNTRY.EVAL');
-SET &CLOSE=IF &CNTR EQ &NOCNTRYS THEN 'CLOSE' ELSE '';
TABLE FILE CAR
SUM
DEALER_COST
RETAIL_COST
BY
COUNTRY
BY CAR
WHERE COUNTRY EQ '&COUNTRY.EVAL'
ON TABLE PCHOLD FORMAT PDF NOBREAK
END
-RUN
TABLE FILE CAR
SUM
DEALER_COST
RETAIL_COST
BY
COUNTRY
BY BODYTYPE
WHERE COUNTRY EQ '&COUNTRY.EVAL'
ON TABLE PCHOLD FORMAT PDF NOBREAK &CLOSE.EVAL
END
-RUN

-ENDREPEAT

After I wrote the last post I was thinking I would have to do a combination of a compound report and looping.