Focal Point
[CLOSED] Conditional Subtotals on a single column

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/5627082376

October 27, 2014, 02:24 PM
Michael Watts
[CLOSED] Conditional Subtotals on a single column
I have a report that needs to count the number of occurrences of a particular text value in a single column, and display that count in a subtotal section of the report for a corresponding BY column.

Example:
State City # of Widgets Available
-----------------------------------------
NY Yonkers 5 Y
Tarrytown 7 N
NYC 30 Y
---------------
Total NY 42 2

NJ Trenton 18 Y
Jersey City 29 Y
Newark 13 Y
---------------
Total NJ 60 3

In this example I need to count the number of 'Y' values in the available column and display that value in the "State" sub total section.

How can I do this in WebFOCUS?

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


8.0.03/Windows7
October 27, 2014, 02:35 PM
MartinY
Must be something like this:
DEFINE FILE ABC
NB_AVAIL /P4 = IF AVAILABLE EQ 'Y' THEN 1 ELSE 0;
END
TABLE FILE ABC
SUM NB_WIDGET
    NB_AVAIL
BY STATE
BY CITY
ON STATE 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
October 27, 2014, 04:33 PM
Michael Watts
I want the subtotal for NB_AVAIL to appear under the AVAILABLE column. Your example does not display that field. I do not want to display the DEFINE field.

It has to look exactly as the example I supplied.


8.0.03/Windows7
October 28, 2014, 05:34 AM
Alan B
SUBTOTAL will only work on numeric columns. As Available is non-numeric, there is no subtotal option available.

The best option is to use SUBFOOT with the required fields and the count as Martin showed you.


Alan.
WF 7.705/8.007
October 28, 2014, 08:37 AM
MartinY
SUBFOOT may be used as following:
DEFINE FILE CAR
SEDAN /P4 = IF BODYTYPE EQ 'SEDAN' THEN 1 ELSE 0;
END
TABLE FILE CAR
PRINT CAR.BODY.DEALER_COST
     CAR.BODY.SEDAN
BY  CAR.ORIGIN.COUNTRY
BY  LOWEST CAR.BODY.BODYTYPE

ON CAR.BODY.BODYTYPE SUBFOOT WITHIN
"TOTAL <CAR.BODY.BODYTYPE<CAR.BODY.DEALER_COST<CAR.BODY.SEDAN"
""

WHERE COUNTRY EQ 'ENGLAND';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE COLUMN-TOTAL AS 'GAND-TOTAL' 'CAR.BODY.DEALER_COST' 'CAR.BODY.SEDAN'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
TYPE = REPORT,
     COLUMN = BODYTYPE,
     JUSTIFY = RIGHT,
$
TYPE = SUBFOOT,
     JUSTIFY = RIGHT,
     HEADALIGN = BODY,
$
TYPE = SUBFOOT,
     JUSTIFY = RIGHT,
     HEADALIGN = BODY,
$
ENDSTYLE
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
October 28, 2014, 11:51 AM
susannah
Michael, you may want to use RECAP
eg:
DEFINE FILE IBISAMP/CAR
SPORTSCARS/I5=IF SEATS EQ 2 THEN 1 ELSE 0;
END
TABLE FILE IBISAMP/CAR
SUM SEATS RETAIL_COST SPORTSCARS NOPRINT BY COUNTRY BY CAR
ON COUNTRY RECAP thing/I5=SUM.SPORTSCARS;
ON COUNTRY SUBFOOT
 "<+0> <+0>sports: <thing<TOT.RETAIL_COST"
ON TABLE SET STYLE *
TYPE=REPORT,HEADALIGN=BODY,$
TYPE=SUBFOOT,ITEM=2,JUSTIFY=RIGHT,STYLE=ITALIC,$
TYPE=SUBFOOT,ITEM=3, JUSTIFY=RIGHT,STYLE=ITALIC,$
TYPE=SUBFOOT,ITEM=4, JUSTIFY=RIGHT,$
END





In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
November 03, 2014, 01:37 PM
Michael Watts
Thanks Susannah, I tried RECAP. It gives me the value I want but does not place it where I need it.
Placing the value in a subfooter puts it a line below the subtotals. Client does not like that, they want it aligned with the rest of the subtotals.


8.0.03/Windows7
November 03, 2014, 01:56 PM
Alan B
Michael

Susannah's solution does not use subtotal, just subfoot.

Using that approach, with values in the correct order, and using HEADALIGN will give a line equivalent to subtotal.


Alan.
WF 7.705/8.007