Focal Point
[solved]Count and Total in a subhead or subtotal

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

August 25, 2017, 11:47 AM
Trudy
[solved]Count and Total in a subhead or subtotal
I want to put subtotal and count of the number of lines in either a subheading or a subtotal line. So for the car example below I would like to have in either the subhead or subtotal for England

Lines: 2 Subtotal: 45,319

 TABLE FILE CAR
SUM
     CAR.BODY.RETAIL_COST
BY  CAR.ORIGIN.COUNTRY
BY  CAR.COMP.CAR

ON CAR.ORIGIN.COUNTRY SUBHEAD
" "
ON CAR.ORIGIN.COUNTRY SUBTOTAL AS 'Subtotal '
END
 

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


WF8
Windows
August 25, 2017, 12:07 PM
MartinY
This is the simplest way

TABLE FILE CAR
SUM COMPUTE NB_ROW /I3 = 1;
    CAR.BODY.RETAIL_COST
BY  CAR.ORIGIN.COUNTRY
BY  CAR.COMP.CAR

ON CAR.ORIGIN.COUNTRY SUBHEAD
" "
ON CAR.ORIGIN.COUNTRY SUBTOTAL AS 'Subtotal '
END



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
August 25, 2017, 01:24 PM
Trudy
Yes, but then you have a row with 1's in the report that isn't really useful. I was hoping to avoid that.


WF8
Windows
August 25, 2017, 02:44 PM
MartinY
And this ?

SET ASNAMES = ON
TABLE FILE CAR
SUM CNT.CAR     AS 'Nb Row'
BY COUNTRY

SUM RETAIL_COST AS 'Retail Cost'
BY COUNTRY      AS 'Country'
BY CAR          AS 'Car'
END
-RUN


Or that:

TABLE FILE CAR
SUM RETAIL_COST
	COMPUTE NB_ROW /I3 = 1;
BY COUNTRY
BY TOTAL COMPUTE ROWID  /I1 = IF COUNTRY EQ LAST COUNTRY THEN ROWID ELSE ROWID + 1;
BY TOTAL COMPUTE TOTROW /I1 = 0;
BY CAR
ON TABLE HOLD AS EXTDATA FORMAT FOCUS
END
-RUN

DEFINE FILE EXTDATA
ROWID  /I1 = IF COUNTRY EQ LAST COUNTRY THEN LAST ROWID ELSE ROWID;
END
TABLE FILE EXTDATA
SUM RETAIL_COST
    NB_ROW
BY COUNTRY
BY ROWID
BY TOTAL COMPUTE TOTROW /I1 = 1;
BY TOTAL COMPUTE CAR    /A16 = '';
ON TABLE HOLD AS TOTDATA FORMAT FOCUS
END
-RUN

TABLE FILE EXTDATA
SUM RETAIL_COST
    NB_ROW
BY COUNTRY
BY TOTROW
BY ROWID
BY CAR
ON TABLE HOLD AS MRGDATA FORMAT FOCUS
MORE
FILE TOTDATA
END
-RUN

DEFINE FILE MRGDATA
NB_ROWX  /I3 MISSING ON = IF TOTROW EQ 0 THEN MISSING ELSE NB_ROW;
COUNTRYX /A20           = IF TOTROW EQ 0 THEN COUNTRY ELSE 'Subtotal ' | COUNTRY;
END
TABLE FILE MRGDATA
SUM RETAIL_COST AS 'Retail Cost'
    NB_ROWX     AS 'Nb Row'
BY ROWID    NOPRINT
BY TOTROW   NOPRINT
BY COUNTRYX AS 'Country'
BY CAR      AS 'Car'
ON ROWID SUBHEAD
""
ON TABLE SET PAGE-NUM NOLEAD
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
August 25, 2017, 07:24 PM
Crymsyn
Wouldn't it also be possible to do it like this?

TABLE FILE CAR
SUM 
	CNT.CAR AS 'NB_ROW'
	RETAIL_COST AS 'RT_SUBTOT'
BY COUNTRY

SUM
	RETAIL_COST
BY  COUNTRY
BY  CAR
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS HOLD_CAR
END

TABLE FILE HOLD_CAR
SUM
	RETAIL_COST
BY  COUNTRY
BY  CAR
ON COUNTRY SUBHEAD
"Lines: <NB_ROW Subtotal <COUNTRY : <RT_SUBTOT"
""
END



WF: 8201, OS: Windows, Output: HTML, PDF, Excel
August 28, 2017, 09:48 AM
RayP
Here is another way to add subtotals in a subfoot :
TABLE FILE CAR
SUM COMPUTE NB_ROW /I3 = 1; NOPRINT
    CAR.BODY.RETAIL_COST
BY  CAR.ORIGIN.COUNTRY
BY  CAR.COMP.CAR

ON CAR.ORIGIN.COUNTRY SUBHEAD
" "
ON COUNTRY SUBFOOT
"Number of Lines: <ST.NB_ROW"
"Cost : <ST.RETAIL_COST"
END  



Webfocus v8.2.02
OS:Windows 10, DB2, SQL
Output: HTML, PDF and Excel
September 27, 2019, 10:50 AM
Shingles
quote:
Originally posted by RayP:
Here is another way to add subtotals in a subfoot :
TABLE FILE CAR
SUM COMPUTE NB_ROW /I3 = 1; NOPRINT
    CAR.BODY.RETAIL_COST
BY  CAR.ORIGIN.COUNTRY
BY  CAR.COMP.CAR

ON CAR.ORIGIN.COUNTRY SUBHEAD
" "
ON COUNTRY SUBFOOT
"Number of Lines: <ST.NB_ROW"
"Cost : <ST.RETAIL_COST"
END  


Nice clean approach.

I've tried to use it with one of my reports, but I'm not getting the total rows. The number for the total rows on the SUBHEAD (I am using a SUBHEAD instead of a SUBFOOT) is showing 1 all the time.


WebFOCUS 8201, SP 0.1, Windows 7, HTML
September 27, 2019, 11:00 AM
Shingles
quote:
Originally posted by Shingles:
quote:
Originally posted by RayP:
Here is another way to add subtotals in a subfoot :
TABLE FILE CAR
SUM COMPUTE NB_ROW /I3 = 1; NOPRINT
    CAR.BODY.RETAIL_COST
BY  CAR.ORIGIN.COUNTRY
BY  CAR.COMP.CAR

ON CAR.ORIGIN.COUNTRY SUBHEAD
" "
ON COUNTRY SUBFOOT
"Number of Lines: <ST.NB_ROW"
"Cost : <ST.RETAIL_COST"
END  


Nice clean approach.

I've tried to use it with one of my reports, but I'm not getting the total rows. The number for the total rows on the SUBHEAD (I am using a SUBHEAD instead of a SUBFOOT) is showing 1 all the time.


Um... ok... I think I figured it out... sorta... ST stands for SubTotal and seems like it can only be used in the SUBFOOT. Is there something equivalent to ST for the SUBHEAD?


WebFOCUS 8201, SP 0.1, Windows 7, HTML
September 27, 2019, 11:49 AM
MartinY
Build your own
TABLE FILE CAR
SUM COMPUTE NB_ROW /I3 = 1;
    RETAIL_COST
BY COUNTRY
BY TOTAL COMPUTE ROWID /I2 = 2;
BY CAR
ON TABLE HOLD AS EXTDATA
END
-RUN

TABLE FILE EXTDATA
SUM NB_ROW
    RETAIL_COST
BY COUNTRY
BY TOTAL COMPUTE ROWID /I2  = 1;
BY TOTAL COMPUTE CAR   /A16 = 'Total';
ON TABLE HOLD AS TOTDATA
END
-RUN

TABLE FILE EXTDATA
SUM NB_ROW
    RETAIL_COST
BY COUNTRY
BY ROWID
BY CAR
ON TABLE HOLD AS RPTDATA
MORE
FILE TOTDATA
END
-RUN

TABLE FILE RPTDATA
SUM NB_ROW      AS 'Nb Rows'
    RETAIL_COST AS 'Retail Cost'
BY COUNTRY AS 'Country'
BY ROWID   NOPRINT
BY CAR     AS 'Car'
ON ROWID   SUBFOOT
""
ON TABLE NOTOTAL
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
September 27, 2019, 12:04 PM
Shingles
Right... of course... clever... thank you... I would rather not create a whole bunch of other queries though.

Thank you!


WebFOCUS 8201, SP 0.1, Windows 7, HTML
September 27, 2019, 12:52 PM
David Briars
quote:
...I am using a SUBHEAD instead of a SUBFOOT...

SUBHEAD picks up the first value of a set.

So for SUBHEAD to pick up total values, use a multiset request.
TABLE FILE CAR
"Retail Cost Report"
-* Totals within Country.
SUM
 COMPUTE CARCOST/D12C = SUM.RCOST; NOPRINT
 COMPUTE CARCNT/I9    = CNT.CAR;   NOPRINT
 BY  COUNTRY   NOPRINT
-* Total within Country/Car.
SUM RCOST     AS 'Retail Cost'
 BY  COUNTRY   NOPRINT
 ON  COUNTRY   SUBHEAD
 "Country: <COUNTRY"
 "Number of Cars: <CARCNT"
 "Total Retail Cost: <CARCOST"
 BY  CAR       AS 'Car'
ON TABLE SET STYLE *
 INCLUDE=ENSilver_DarkComp.sty, $
 TYPE=HEADING, JUSTIFY=CENTER,$
 TYPE=TITLE,   JUSTIFY=CENTER,$
 TYPE=SUBHEAD, SIZE=9,$
ENDSTYLE
END  

This message has been edited. Last edited by: David Briars,




Pilot: WebFOCUS 8.2.06 Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2016/Tomcat Standalone Workstation: Windows 10/IE11+Edge Database: Oracle 12c, Netezza, & MS SQL Server 2019 Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer & ReportCaster