BMW Audi Jaguar BMW Audi Jaguar Model DCOST DCOST DCOST RCOST RCOST RCOST ----- ------ ---- ----- ----- ----- ------- ----- ------ ---- ----- ----- ----- -------
original code:
TABLE FILE CAR SUM DCOST RCOST ACROSS CAR BY MODEL ENDThis message has been edited. Last edited by: Kerry,
WebFOCUS 7.6 Windows, All Outputs
June 11, 2012, 09:13 AM
GamP
It's a bit of awkward code, but it has the desired ersult for the given example. It may of may not work for you in real life.... but here it is:
TABLE FILE CAR
SUM DCOST
COMPUTE SORT/I2 = 1;
COMPUTE SORTNAME/A5='DCOST';
BY CAR
BY MODEL
WHERE CAR EQ 'AUDI' OR 'BMW' OR 'JAGUAR ';
ON TABLE HOLD AS KEEP
END
FILEDEF KEEP DISK KEEP.FTM (APPEND
TABLE FILE CAR
SUM RCOST
COMPUTE SORT/I2 = 2;
COMPUTE SORTNAME/A5='RCOST';
BY CAR
BY MODEL
WHERE CAR EQ 'AUDI' OR 'BMW' OR 'JAGUAR ';
ON TABLE HOLD AS KEEP
END
TABLE FILE KEEP
SUM RE
BY MODEL
ACROSS SORT NOPRINT
ACROSS CAR
ACROSS SORTNAME AS ''
END
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
June 11, 2012, 05:01 PM
Crymsyn
This is similar to GamP's way but uses the MORE statment instead of APPEND and the DCOST and RCOST are above the CARs.
DEFINE FILE CAR
CUR_COST/D20=DCOST;
CUR_SORT/A63='DCOST';
END
TABLE FILE CAR
SUM
CUR_COST
CUR_SORT
BY MODEL
BY CAR
WHERE CAR EQ 'AUDI' OR 'BMW' OR 'JAGUAR ';
ON TABLE HOLD AS KEEP
END
DEFINE FILE CAR
CUR_COST/D20=RCOST;
CUR_SORT/A63='RCOST';
END
TABLE FILE CAR
SUM
CUR_COST AS ''
ACROSS CUR_SORT AS ''
ACROSS CAR
BY MODEL
WHERE CAR EQ 'AUDI' OR 'BMW' OR 'JAGUAR ';
MORE
FILE KEEP
END
WF: 8201, OS: Windows, Output: HTML, PDF, Excel
June 12, 2012, 07:27 AM
Danny-SRL
Or with an alternate master:
-SET &ECHO=ALL;
-* File keerthi01.fex
TABLE FILE CAR
SUM
COMPUTE S1/A6='DEALER';
DCOST
COMPUTE S2/A6='RETAIL';
RCOST
BY CAR
BY COUNTRY
ON TABLE HOLD AS KEERTHI FORMAT ALPHA
END
-RUN
EX -LINES 8 EDAPUT MASTER,KEERTHI,C,MEM
FILENAME=KEERTHI , SUFFIX=FIX
SEGMENT=KEERTHI, SEGTYPE=S0
FIELDNAME=CAR, ALIAS=E01, USAGE=A16, ACTUAL=A16, $
FIELDNAME=COUNTRY, ALIAS=E02, USAGE=A10, ACTUAL=A10, $
SEGMENT=COST, PARENT=KEERTHI, OCCURS=VARIABLE
FIELDNAME=S, ALIAS=E03, USAGE=A6, ACTUAL=A06, $
FIELDNAME=COST, ALIAS=E04, USAGE=D7, ACTUAL=A07, $
-RUN
TABLE FILE KEERTHI
SUM COST
ACROSS S AS ''
ACROSS CAR
BY COUNTRY
END
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
June 15, 2012, 08:12 AM
Keerthi
Thanks you. That was exactly what I was looking for. but I have a small issue now. My report is actually an AHTML report and I was wondering on how we can supress the unwanted column title.
TABLE FILE KEERTHI SUM COST AS '' ACROSS S AS '' ACROSS CAR BY COUNTRY ON TABLE PCHOLD FORMAT AHTML END
I tried giving a blank alias name for COST column but however it shows up as column1 , column 2..etc in the report.
Appreciate your help.
Thanks, Keerthi
WebFOCUS 7.6 Windows, All Outputs
June 20, 2012, 08:23 AM
Keerthi
Thanks Crymsyn. One question though, is there a way to remove the extra line in the title?
DEFINE FILE CAR CUR_COST/D20=DCOST; CUR_SORT/A63='DCOST'; END TABLE FILE CAR SUM CUR_COST CUR_SORT BY MODEL BY CAR WHERE CAR EQ 'AUDI' OR 'BMW' OR 'JAGUAR '; ON TABLE HOLD AS KEEP END
DEFINE FILE CAR CUR_COST/D20=RCOST; CUR_SORT/A63='RCOST'; END TABLE FILE CAR SUM CUR_COST AS '&|nbsp' ACROSS CUR_SORT AS '' ACROSS CAR AS '' BY MODEL WHERE CAR EQ 'AUDI' OR 'BMW' OR 'JAGUAR '; ON TABLE PCHOLD FORMAT AHTML MORE FILE KEEP END
when I use the above code, I get an extra line in column title... any help is greatly appreciated.
Thanks, Keerthi
WebFOCUS 7.6 Windows, All Outputs
June 20, 2012, 02:29 PM
Dan Satchell
The MacGyver technique may offer another solution:
JOIN CLEAR *
JOIN BLANK WITH BODYTYPE IN CAR TO BLANK IN FSEQ AS J1
-*
DEFINE FILE CAR
BLANK/A1 WITH BODYTYPE = ' ';
COSTX/D8S = IF (FSEQ.COUNTER EQ 1) THEN DEALER_COST ELSE RETAIL_COST ;
TYPEX/A5 = IF (FSEQ.COUNTER EQ 1) THEN 'DCost' ELSE
IF (FSEQ.COUNTER EQ 2) THEN 'RCost' ELSE '';
END
-*
TABLE FILE CAR
SUM COSTX AS '&|nbsp;'
BY MODEL
ACROSS TYPEX AS ''
ACROSS CAR AS ''
WHERE CAR EQ 'AUDI' OR 'BMW' OR 'JAGUAR';
WHERE (TYPEX NE '');
ON TABLE COLUMN-TOTAL
ON TABLE SET NODATA ''
ON TABLE PCHOLD FORMAT AHTML
END
This message has been edited. Last edited by: Dan Satchell,
WebFOCUS 7.7.05
July 03, 2012, 12:12 PM
Doug
quote:
I get an extra line in column title...
Are you talking about the grid lines? If so, consider adding this:
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
GRID=OFF, BORDER=LIGHT, FONT=ARIAL, SIZE=12,$
ENDSTYLE