Focal Point
[CLOSED] SUBTOTAL on multiple sort fields

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/7967063196

June 13, 2019, 10:17 AM
WebFOCUS_Dev
[CLOSED] SUBTOTAL on multiple sort fields
Hello

Below is the sample code with CAR file. This gives subtotal by Country and then grand total of the same.
Is it possible to have 5 records before grand total which provide subtotal by 5 different BODYTYPE values? I don't want subtotal by BODYTYPE for each country. I was not able to get it however I tried.

TABLE FILE CAR
SUM
	CAR.BODY.DEALER_COST
	CAR.BODY.RETAIL_COST
	CAR.BODY.SALES
BY CAR.ORIGIN.COUNTRY
BY CAR.BODY.BODYTYPE
ON CAR.ORIGIN.COUNTRY SUBTOTAL
ON TABLE SUBTOTAL
ON TABLE PCHOLD FORMAT HTML
END  


Please suggest.
Thank you.

This message has been edited. Last edited by: WebFOCUS_Dev,


WebFOCUS 8202
All formats
June 13, 2019, 01:25 PM
WebFOCUS_Dev
Only way I think of is to split into 3 reports


WebFOCUS 8202
All formats
June 13, 2019, 01:46 PM
MartinY
Something such as this ?

DEFINE FILE CAR
COUNTRY /A10 = '';
END
TABLE FILE CAR
SUM	DEALER_COST
	RETAIL_COST
	SALES
BY TOTAL COMPUTE ROWID /I2 = 1;
BY COUNTRY
BY BODYTYPE
ON TABLE HOLD AS BODYTOT
END
-RUN

DEFINE FILE CAR
ROWID /I2 = 2;
END
TABLE FILE CAR
SUM	DEALER_COST
	RETAIL_COST
	SALES
BY ROWID NOPRINT
BY COUNTRY
ON COUNTRY SUBTOTAL
WHEN ROWID EQ 2
BY BODYTYPE
ON TABLE PCHOLD FORMAT HTML
MORE
FILE BODYTOT
END
-RUN



WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
June 13, 2019, 07:35 PM
WebFOCUS_Dev
Martin, is it possible to exclude 1 country SALES from Grand Total?
TABLE FILE CAR
SUM
	CAR.BODY.SALES
BY CAR.ORIGIN.COUNTRY
ON TABLE SUBTOTAL
ON TABLE PCHOLD FORMAT HTML
END   

quote:
Originally posted by MartinY:
Something such as this ?



WebFOCUS 8202
All formats
June 14, 2019, 03:09 AM
Tony A
quote:
is it possible to exclude 1 country SALES from Grand Total?

Have you tried to achieve this yourself using Martin's example or are you just wanting someone to do it for you?

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.06 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
June 14, 2019, 09:14 AM
Danny-SRL
MacGuyver does it for you!
  
-SET &ECHO=ALL;
-* File wf_dev02.fex
-* 
-* McGuyver technique: using a flat file
-* Create the MacGuyver Master and data file on the fly
-*
SET MSG=ON
EX -LINES 7 EDAPUT MASTER,FSEQ,C,MEM
FILENAME=FSEQ,SUFFIX=FIX
SEGNAME=CHAR1,SEGTYPE=S0
FIELDNAME=BLANK,BLANK,A1,A1,$
SEGNAME=CHARS,SEGTYPE=S0,PARENT=CHAR1,OCCURS=VARIABLE
FIELDNAME=CHAR,CHARS,A1,A1,$
FIELDNAME=COUNTER,ORDER,I2,I4,$
-RUN
FILEDEF FSEQ DISK FSEQ.FTM
-RUN
-WRITE FSEQ  FILEFORMACGUYVERFILEFORMACGUYVERFILEFORMACGUYVER
-RUN
-*
-* Example
JOIN BLANK WITH BODYTYPE IN CAR TO BLANK IN FSEQ AS B_
DEFINE FILE CAR
BLANK/A1 WITH BODYTYPE=' ';
XCOUNTRY/A10=IF COUNTER EQ 2 THEN 'TOTAL' ELSE COUNTRY;
END
TABLE FILE CAR
SUM SALES RCOST DCOST
BY COUNTER NOPRINT PAGE-BREAK REPAGE
BY XCOUNTRY AS COUNTRY
BY BODYTYPE
ON XCOUNTRY SUBFOOT
" "
WHEN COUNTER EQ 1
ON XCOUNTRY SUBTOTAL MULTILINES AS '** '
WHERE COUNTER LE 2
ON TABLE NOTOTAL
END



Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF