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>,

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

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.

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.

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

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

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.

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.