Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

New TIBCO Community Coming Soon
In early summer, TIBCO plans to launch a new community—with a new user experience, enhanced search, and expanded capabilities for member engagement with answers and discussions! In advance of that, the current myibi community will be retired on April 30. We will continue to provide updates here on both the retirement of myibi and the new community launch.

What You Need to Know about Our New Community
We value the wealth of knowledge and engagement shared by community members and hope the new community will continue cultivating networking, knowledge sharing, and discussion.

During the transition period, from April 20th until the new community is launched this summer, myibi users should access the TIBCO WebFOCUS page to engage.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[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: 117 | Registered: May 28, 2015Report 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.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report 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: 117 | Registered: May 28, 2015Report 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.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report 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, 2010Report 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.2.02
OS:Windows 10, DB2, SQL
Output: HTML, PDF and Excel
 
Posts: 3 | Registered: March 03, 2009Report 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: 190 | Registered: May 19, 2017Report 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: 190 | Registered: May 19, 2017Report 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.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report 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: 190 | Registered: May 19, 2017Report 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,




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
 
Posts: 822 | Registered: April 23, 2003Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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-2020 Information Builders