[CLOSED] Print data in holdfiles in specific order/in multiple lines in excel format
Hi All,
Is it possible to print two hold files one after the other for summary and detail format in Excel format. able to get like Output holdfiles one hold file followed by next hold file.
Below are the hold files TABLE FILE CAR SUM RCOST DCOST BY COUNTRY HEADING "Summary at Country Level" FOOTING "" ON TABLE PCHOLD FORMAT EXL2K OPEN NOBREAK END TABLE FILE CAR SUM RCOST DCOST BY COUNTRY BY CAR HEADING "Summary at Country and Car Level" FOOTING "" ON TABLE PCHOLD FORMAT EXL2K NOBREAK END TABLE FILE CAR SUM RCOST DCOST BY COUNTRY BY CAR BY MODEL HEADING "Detail at Model Level" FOOTING "" ON TABLE PCHOLD FORMAT EXL2K CLOSE END
The output needs to be like
COUNTRY RETAIL_COST DEALER_COST ENGLAND 45,319 37,853
COUNTRY CAR RETAIL_COST DEALER_COST ENGLAND JAGUAR 22,369 18,621 JENSEN 17,850 14,940 TRIUMPH 5,100 4,292
COUNTRY CAR MODEL RETAIL_COST DEALER_COST ENGLAND JAGUAR V12XKE AUTO 8,878 7,427 XJ12L AUTO 13,491 11,194 JENSEN INTERCEPTOR III 17,850 14,940 TRIUMPH TR7 5,100 4,292
COUNTRY RETAIL_COST DEALER_COST FRANCE 5,610 4,631
COUNTRY CAR RETAIL_COST DEALER_COST FRANCE PEUGEOT 5,610 4,631
COUNTRY CAR MODEL RETAIL_COST DEALER_COST FRANCE PEUGEOT 504 4 DOOR 5,610 4,631
.... In the excel the output displays like summary row for one record followed by details and second summary row followed by details having page break by country.
Any suggestions.. Please update
Thank You!This message has been edited. Last edited by: FP Mod Chuck,
WebFOCUS 8
Windows, All Outputs
February 11, 2019, 07:26 AM
Darryl_uk
There may be a better answer but when I've needed to do this kind of thing I've wrapped it up in a loop or two allowing me to repeat the extract or output code a number of times while feeding in a parameter from a generated hold file. So along the lines of: TABLE FILE CAR BY COUNTRY ON TABLE NOTOTAL ON TABLE HOLD AS CLIST END -RUN -SET &CCNT = &LINES ;
-REPEAT :ENDREPEAT2 FOR &I FROM 1 TO &CCNT -READFILE CLIST -RUN
. . . your extract code + Where COUNTRY EQ ‘&COUNTRY’ ; . . .
-:ENDREPEAT2 -RUN
7.7.05 Windows.
February 11, 2019, 07:58 AM
MartinY
Basically, what you are requesting is subtotals on each BY field where the subtotal is above the detailed line Does something such as this may suits your need ?
TABLE FILE CAR
SUM RETAIL_COST
DEALER_COST
BY COUNTRY
ON COUNTRY RECOMPUTE AS 'Country Total'
BY CAR
ON CAR RECOMPUTE AS 'Car Total'
BY MODEL
ON MODEL RECOMPUTE AS 'Model Total'
ON TABLE PCHOLD FORMAT XLSX
-* To create a "page-break" (new tab) in Excel
ON TABLE SET COMPOUND BYTOC
-* To have the subtotal placed above the detailed data
ON TABLE SET SUBTOTALS ABOVE
ON TABLE SET PAGE-NUM NOLEAD
-* To make all BY fields displayed on each line
ON TABLE SET BYDISPLAY ON
-* To avoid the display of the Grand-Total
ON TABLE NOTOTAL
ON TABLE SET LINES 999999
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
-RUN
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
February 14, 2019, 09:01 AM
pvijaysai
Thank You Martin. Can we display the column names separately for each subtotal fields.
WebFOCUS 8
Windows, All Outputs
February 14, 2019, 09:36 AM
MartinY
quote:
Can we display the column names separately for each subtotal fields.
What do you mean by the above ? Can you illustrate with a sample output ?
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007