How to display subtotal in subhead

Due to FOCUS way of process (SUBHEAD is processed before), in order to show subtotals in a subhead, you must calculate the subtotal before, using for example a multi verb request:

TABLE FILE CAR
SUM
COMPUTE STSALES/I9 = SALES ; NOPRINT
BY COUNTRY

PRINT
SALES
BY COUNTRY
ON COUNTRY NOPRINT SUBHEAD
" "Subtotal: END

Regards,
Mikel

This message has been edited. Last edited by: <Mabel>,

Posts: 173 | Location: Madrid, Spain | Registered: May 09, 2003

Ignored post by Mikel posted

<Vipul>

posted

Thanks Mikel, it worked...

Member

posted

Sorry, guys but I don't understand the solution to getting subtotals to print out as subheads. I'm using WebFocus version 7.1.4 and the code I'm using to get results is:
TABLE FILE SALES
SUM
HARDWARE
MENS
APPLIANCES
KITCHEN
BY STORE
BY SALESMAN
ON STORE SUBTOTAL AS '*TOTAL'

The resutls of the above code are :
Store Salesman Hardware Mens Appliances Kitchen
ABC Store Donovan 405 85 725 800
Johnson 204 43 694 390
Smith 400 84 450 1254
Jones 207 44 225 450
*TOTAL ABC Store 1,216 256 2,094 2,894

What I need as a result is:

Store Salesman Hardware Mens Appliances Kitchen
ABC Store 1,216 256 2,094 2,894
Donovan 405 85 725 800
Johnson 204 43 694 390
Smith 400 84 450 1254
Jones 207 44 225 450

When I try to modify my code to mimic the example I either get no subhead or I get errors

WebFocus Version = 7.1.4
OS.Platform = Windows XP 5.1
Expected OUtput = HTML, Excel and PDF

Posts: 13 | Registered: July 23, 2007

Ignored post by patjohnson posted

Francis Mariani

Expert

posted

This should get you on your way:

TABLE FILE CAR
SUM 
COMPUTE CTSALES/D15 = SALES; NOPRINT
COMPUTE COUNTRYHEAD/A25 = 'COUNTRY: ' | COUNTRY; NOPRINT
BY COUNTRY NOPRINT

SUM
SALES/D15
BY COUNTRY NOPRINT
BY MODEL
ON COUNTRY SUBHEAD
"<COUNTRYHEAD<CTSALES"
ON TABLE SET STYLE *
TYPE=SUBHEAD, HEADALIGN=BODY, COLOR=BLUE, $
TYPE=SUBHEAD, ITEM=2, JUSTIFY=RIGHT, $
END

This message has been edited. Last edited by: Francis Mariani,

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Member

posted

Thank you so much! It works and looks great.

WebFocus Version = 7.1.4
OS.Platform = Windows XP 5.1
Expected OUtput = HTML, Excel and PDF

Posts: 13 | Registered: July 23, 2007

Ignored post by patjohnson posted

Member

posted

Okay here is the final twist in the "Subtotal as Subhead" saga. I need to show the ranking of the stores in the subhead along with the rest of the data. Got any ideas on how I could do that?

WebFocus Version = 7.1.4
OS.Platform = Windows XP 5.1
Expected OUtput = HTML, Excel and PDF

Posts: 13 | Registered: July 23, 2007

Ignored post by patjohnson posted

Francis Mariani

Expert

posted

I could only get this to work with three passes through the data!

-SET &ECHO=ALL;

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA
-RUN

TABLE FILE CAR
SUM 
SALES/D15 AS 'CTSALES'
BY COUNTRY

SUM
SALES/D15
BY COUNTRY
BY MODEL
ON TABLE HOLD AS H001
END
-RUN

TABLE FILE H001
PRINT 
CTSALES
SALES
RANKED BY HIGHEST CTSALES NOPRINT
BY COUNTRY
BY MODEL
ON TABLE HOLD AS H002
END
-RUN

?FF H002
-RUN

DEFINE FILE H002
COUNTRYHEAD/A25 = 'COUNTRY: ' | COUNTRY;
RANKD/F7 = RANK;
RANKHEAD/A25 = 'RANK: ' | FTOA(RANKD, '(F7)', 'A7');
END
-RUN

TABLE FILE H002
PRINT
SALES
BY COUNTRY NOPRINT
BY MODEL
ON COUNTRY SUBHEAD
"<COUNTRYHEAD<CTSALES<RANKHEAD"
ON TABLE SET STYLE *
TYPE=SUBHEAD, HEADALIGN=BODY, COLOR=BLUE, $
TYPE=SUBHEAD, ITEM=2, JUSTIFY=RIGHT, $
END

You may be able to do it in two passes through the data if the sort order of the stores is the ranking order, not alphanumeric order.

I tweaked the previous code a little as well - see edited posting above.

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Member

posted

A belated thanks for all your help yesterday. Your solution worked and my end-user is very happy with the results!

WebFocus Version = 7.1.4
OS.Platform = Windows XP 5.1
Expected OUtput = HTML, Excel and PDF

Posts: 13 | Registered: July 23, 2007

Ignored post by patjohnson posted

Francis Mariani

Expert

posted

Pat, I'm happy it worked out. Cheers.

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted