Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [solved]Count and Total in a subhead or subtotal
Go
New
Search
Notify
Tools
Reply
  
[solved]Count and Total in a subhead or subtotal
 Login/Join
 
Platinum Member
posted
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
 
Posts: 113 | Registered: May 28, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2191 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 113 | Registered: May 28, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2191 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Gold member
posted Hide Post
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
 
Posts: 78 | Registered: November 08, 2010Reply With QuoteReport This Post
Member
posted Hide Post
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.1.05
OS:Windows 7, DB2, SQL
Output: HTML, PDF and Excel
 
Posts: 3 | Registered: March 03, 2009Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 159 | Registered: May 19, 2017Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 159 | Registered: May 19, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2191 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 159 | Registered: May 19, 2017Reply With QuoteReport This Post
Master
posted Hide Post
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,




Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2012/Tomcat Standalone Workstation: Windows 7/IE 11 Database: Oracle 12c/Netezza Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer, & ReportCaster

 
Posts: 768 | Registered: April 23, 2003Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [solved]Count and Total in a subhead or subtotal

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.