[CLOSED] How can I create a Conditional or Filtered Column Total?

I have an error report that has a quantity field and an error message field. There can be warning or "hard" errors, and I want a total quantity only for "hard error" rows.

Currently, I have a DEFINE field that is set to the quantity field if the record indicates a hard error (does not start with "WRNG") and is set to zero otherwise, like so:

 REJECTED_QUANTITY/P16.5 = 
IF SUBSTR(50, ERROR_MESSAGE, 1,4,4,'A4') NE 'WRNG' THEN QUANTITY ELSE 0;

So this works fine, I can see it's generating the right number and I can do a COLUMN-TOTAL, but what I really want is to not show the column at all, and just have the total show up under the QUANTITY column with the title "Total Rejected Quantity" but if I NOPRINT the calculated column, the total doesn't show up either. Is there any way I can do this?

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

Prod: 8.2.0.4 OS:Windows 10 Output:AHTML, Excel 2007+

The life of a designer is a life of fight against the ugliness.

Posts: 141 | Location: North Carolina | Registered: August 10, 2012

Francis Mariani

Expert

posted

Hide Post

You need to use ON TABLE SUBFOOT, which will require some styling to make it look like a subtotal line:

SET BYDISPLAY=ON
TABLE FILE CAR
SUM
SALES/D10
COMPUTE SALES_E/D10 = IF COUNTRY CONTAINS 'E' THEN SALES_E + SALES ELSE SALES_E; NOPRINT

BY COUNTRY
BY CAR
BY MODEL

ON TABLE SUBTOTAL AS 'TOTAL SALES FOR ALL COUNTRIES'

ON TABLE SUBFOOT
"TOTAL SALES FOR E-TYPE COUNTRIES <TOT.SALES_E"

ON TABLE SET STYLE *
TYPE=REPORT, SQUEEZE=ON,
FONT='ARIAL', SIZE=9, $

TYPE=GRANDTOTAL, COLOR=GREEN, $

TYPE=TABFOOTING, HEADALIGN=BODY, JUSTIFY=RIGHT, COLOR=BLUE, $
TYPE=TABFOOTING, ITEM=1, COLSPAN=3, JUSTIFY=LEFT, $
ENDSTYLE
END

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

Show Post

Rao D

Platinum Member

posted

Hide Post

I think we need more detail to answer your question. In the below scenario let us say you want to see Total under Sales column but don't want to display Sales details. what about country and car fields? Do you want those 2 to display or not?

TABLE FILE CAR
SUM
SALES
BY COUNTRY
BY CAR
ON TABLE COLUMN-TOTAL AS 'Total'
END

WebFOCUS - ver8201
[ReportingServers: Windows 64bit;
Client: tomcat and IIS on windows 2012
AppStudio

Posts: 104 | Location: Indianapolis | Registered: November 08, 2007

Ignored post by Rao D posted

Show Post

J.Hines

Platinum Member

posted

Hide Post

Thanks Francis and Rao.

The column I want to show is the QUANTITY column, but I would like to have the REJECTED_QUANTITY total show. I realize that doesn't conceptually make sense as normally you'd want column totals to show up under their own columns only.

What I want is essentially Francis' SUBFOOT solution, but the problem is we already have a message declared for FOOTING BOTTOM that says "Sample data - limited to 5000 records" (as some of our reports are too big for AHTML output, so we also have Excel output options). And so if I put in a subfoot, it would show under this line, which is out of place.

Prod: 8.2.0.4 OS:Windows 10 Output:AHTML, Excel 2007+

The life of a designer is a life of fight against the ugliness.

Posts: 141 | Location: North Carolina | Registered: August 10, 2012

Ignored post by J.Hines posted

Show Post

Francis Mariani

Expert

posted

Hide Post

You may be able to get the SUBFOOT above the FOOTING by turning PRINTPLUS on, because:

quote:
PRINTPLUS
The PRINTPLUS parameter introduces printing enhancements. PRINTPLUS is not supported with StyleSheets.

The syntax is:

SET {PRINTPLUS|PRTPLUS} = {ON|OFF}
where:

ON - Handles page breaks internally to provide the correct spacing of pages. You can perform RECAPs in cases where pre-specified conditions are met. Additionally, a SUBFOOT prints above the footing instead of below it.

(Thou I don't know what "PRINTPLUS is not supported with StyleSheets" means!)

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

Show Post

Please Wait. Your request is being processed...