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    WebFOCUS/FOCUS Forum on Focal Point     [solved]Count and Total in a subhead or subtotal

 Go Search Notify Tools
 [solved]Count and Total in a subhead or subtotal
Platinum Member
 posted August 25, 2017 11:47 AM
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 SUBTOTAL AS 'Subtotal '
END
```

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

WF8
Windows

 Posts: 117 | Registered: May 28, 2015 IP
Virtuoso
 posted August 25, 2017 12:07 PM 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 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, 2013 IP
Platinum Member
 posted August 25, 2017 01:24 PM 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, 2015 IP
Virtuoso
 posted August 25, 2017 02:44 PM 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 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, 2013 IP
Gold member
 posted August 25, 2017 07:24 PM 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
"Lines: <NB_ROW Subtotal <COUNTRY : <RT_SUBTOT"
""
END```

WF: 8201, OS: Windows, Output: HTML, PDF, Excel

 Posts: 78 | Registered: November 08, 2010 IP
Member
 posted August 28, 2017 09:48 AM 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 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, 2009 IP
Platinum Member
 posted September 27, 2019 10:50 AM 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 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, 2017 IP
Platinum Member
 posted September 27, 2019 11:00 AM 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 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, 2017 IP
Virtuoso
 posted September 27, 2019 11:49 AM Hide Post
```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, 2013 IP
Platinum Member
 posted September 27, 2019 12:04 PM 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, 2017 IP
Master
 posted September 27, 2019 12:52 PM 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
"Country: <COUNTRY"
"Number of Cars: <CARCNT"
"Total Retail Cost: <CARCOST"
BY  CAR       AS 'Car'
ON TABLE SET STYLE *
INCLUDE=ENSilver_DarkComp.sty, \$
TYPE=TITLE,   JUSTIFY=CENTER,\$
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, 2003 IP