[solved]subtotal CNT.DST does not work

August 19, 2019, 11:39 AM

Hello,

I have a report that will display data semi-year by month where I am counting the distinct numbers of employee. The issue I am having is that the subtotal line is not doing the CNT.DST instead it is doing SUM.
Does anyone knows how to fix this or if WF can do the CNT.DST on a summary and subtotal line?
I could not find anything on the manual.

here is the code:

TABLE FILE H_EMPSD
SUM
TOTALDAYSOFF/I9C
TOTALOFFHOURS/D8.2
COMPUTE AVE_HOURS_LOST/D8.2 = TOTALOFFHOURS/TOTALDAYSOFF;
CNT.DST.EMPLID/I9C
BY HIGHEST RANK NOPRINT
BY SEMI_YEAR AS 'Semi Year'
BY LOWEST CALENDARYEAR AS 'Year'
BY LOWEST MONTHOFYEAR NOPRINT
BY MONTHNAME AS 'Month'
ON SEMI_YEAR RECOMPUTE AS 'Semi Year Summary'
ON TABLE SUMMARIZE AS 'Summary'
ON TABLE SET AUTOFIT ON
ON TABLE SET PAGE-NUM OFF
ON TABLE SET ASNAMES ON
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET LINES 999999
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/WFC/Repository/Public/branding/cob_ar_theme_1.sty,
$
TYPE=REPORT,
ALLOW-PAGINATION=OFF,
TOPGAP=0.03,
BOTTOMGAP=0.03,
FONT='TREBUCHET MS',SIZE=9,COLOR='BLACK',GRID=ON,BORDER=LIGHT,BORDER-COLOR='SILVER',
LINES-PER-PAGE=1000,
JUSTIFY=CENTER,
$
TYPE=REPORT,CALC-LOCATION =TOP,COLUMN=N11, CALCULATION=SUM, $
TYPE=REPORT,CALC-LOCATION =TOP,COLUMN=N12, CALCULATION=SUM, $
$
TYPE=DATA,
SIZE=9,
BACKCOLOR=( 'WHITE' RGB(235 235 240) ),
$
TYPE=REPORT,
OBJECT=MENU,
HOVER-BACKCOLOR=RGB(94 106 121),
$
TYPE=REPORT,
OBJECT=CURRENT-ROW,
HOVER-BACKCOLOR=RGB(132 148 169),
$
ENDSTYLE
END

thanks,

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

August 19, 2019, 11:59 AM

MartinY

A SubTotal will always perform a SUM on numeric field except if it's coming from a COMPUTE where then it will re-COMPUTE the value

Try the below :

TABLE FILE H_EMPSD
SUM
TOTALDAYSOFF/I9C 
TOTALOFFHOURS/D8.2 
COMPUTE AVE_HOURS_LOST/D8.2 = TOTALOFFHOURS/TOTALDAYSOFF; 
COMPUTE CDEMP/I9C = CNT.DST.EMPLID; AS 'Nb Emp'
BY HIGHEST RANK NOPRINT
BY SEMI_YEAR AS 'Semi Year'
BY LOWEST CALENDARYEAR AS 'Year'
BY LOWEST MONTHOFYEAR NOPRINT
BY MONTHNAME AS 'Month'
ON SEMI_YEAR RECOMPUTE AS 'Semi Year Summary'
ON TABLE SUMMARIZE AS 'Summary'
ON TABLE SET AUTOFIT ON
ON TABLE SET PAGE-NUM OFF
ON TABLE SET ASNAMES ON
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET LINES 999999
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
 INCLUDE = IBFS:/WFC/Repository/Public/branding/cob_ar_theme_1.sty,
$
TYPE=REPORT,
 ALLOW-PAGINATION=OFF,
 TOPGAP=0.03,
 BOTTOMGAP=0.03,
 FONT='TREBUCHET MS',SIZE=9,COLOR='BLACK',GRID=ON,BORDER=LIGHT,BORDER-COLOR='SILVER',
 LINES-PER-PAGE=1000,
JUSTIFY=CENTER,
$
TYPE=REPORT,CALC-LOCATION =TOP,COLUMN=N11, CALCULATION=SUM, $
TYPE=REPORT,CALC-LOCATION =TOP,COLUMN=N12, CALCULATION=SUM, $
$
TYPE=DATA,
 SIZE=9,
 BACKCOLOR=( 'WHITE' RGB(235 235 240) ),
$
TYPE=REPORT,
 OBJECT=MENU,
 HOVER-BACKCOLOR=RGB(94 106 121),
$
TYPE=REPORT,
 OBJECT=CURRENT-ROW,
 HOVER-BACKCOLOR=RGB(132 148 169),
$
ENDSTYLE
END

Also, when posting sample code and/or data, please use the code tag which is the last icon on the ribbon that looks like the below

</>

August 19, 2019, 12:19 PM

Doug

Consider setting a DEFINE'd FIELD to the results of what you would get from the CNT.DST, NOPRINT it, and use that FIELD in a SUBFOOT.

August 19, 2019, 12:29 PM

vnf

quote:
RECOMPUTE

hi Martin,

Thanks for the reply but it is still does not display the DST.CNT. by semi-year instead it is still doing the DST.CNT by semi-year/month.

The detail lines are using BY MONTHNAME and the Subtotal is by SEMI-year that is the reason the DST.CNT is not working. In the subtotal line, I want a DST.CNT without the month breakdown. Can this still be done?

thanks,
Vera

August 19, 2019, 01:28 PM

Doug

Try this

BY SEMI_YEAR AS 'Semi Year' RECOMPUTE AS 'Semi Year Summary'
BY LOWEST CALENDARYEAR AS 'Year'
BY LOWEST MONTHOFYEAR NOPRINT
BY MONTHNAME AS 'Month'
-*ON SEMI_YEAR RECOMPUTE AS 'Semi Year Summary'
ON TABLE SUMMARIZE AS 'Summary'

Move the RECOMPUTE to the end of the "BY SEMI_YEAR" line...
I don't have access to App Studio at the moment, so this was not verified.

August 19, 2019, 02:07 PM

MartinY

@Doug, unfortunately it won't change anything

@vnf
How does SEMI_YEAR field is defined ? What is its content ?

Can you post actual & expected result sample ?

August 19, 2019, 02:27 PM

vnf

hi Martin,

SEMI_YEAR is defined as A50V

output

  
Semi Year	Year	Month	Total Days Off	Total Off Hours	Avg. Off Hours	# of Employee Off
Sep-18 to Feb-19	2019	January	1,751	12,202.70	6.97	409
Sep-18 to Feb-19	2019	February	862	5,836.65	6.77	265
Semi Year Summary Sep-18 to Feb-19			2,613	18,039.35	6.9	674

desired output

 
Semi Year	Year	Month	Total Days Off	Total Off Hours	Avg. Off Hours	# of Employee Off
Sep-18 to Feb-19	2019	January	1,751	12,202.70	6.97	409
Sep-18 to Feb-19	2019	February	862	5,836.65	6.77	265
Semi Year Summary Sep-18 to Feb-19			2,613	18,039.35	6.9	502

I would like to get on the subtotal # of Employee Off 502 (distinct count) instead of 674 (409+265)

thanks

August 19, 2019, 02:57 PM

MartinY

Not the easiest way but it works. Not able to figure out why the RECOMPUTE on a SUBTOTAL doesn't work with CNT.DST.

-* CREATE DETAIL DATA PER MODEL
TABLE FILE CAR
SUM CNT.DST.BODYTYPE/I2 AS 'NBBODY'
    SALES
BY TOTAL COMPUTE GTID    /I2   = 1;
BY CAR
BY TOTAL COMPUTE NCAR    /A20V = CAR;
BY COUNTRY
BY TOTAL COMPUTE NCNTRY  /A20V = COUNTRY;
BY TOTAL COMPUTE STID    /I2   = 1;
BY MODEL
ON TABLE HOLD AS DETDATA
END
-RUN

-* CREATE SUBTOTAL DATA PER COUNTRY
TABLE FILE CAR
SUM CNT.DST.BODYTYPE/I2 AS 'NBBODY'
    SALES
BY TOTAL COMPUTE GTID    /I2   = 1;
BY CAR
BY TOTAL COMPUTE NCAR    /A20V = CAR;
BY COUNTRY
BY TOTAL COMPUTE NCNTRY  /A20V = 'SubTotal ' | COUNTRY;
BY TOTAL COMPUTE STID    /I2   = 33;
BY TOTAL COMPUTE MODEL   /A24  = '';
ON TABLE HOLD AS STDATA
END
-RUN

-* CREATE GRAND TOTAL
TABLE FILE CAR
SUM CNT.DST.BODYTYPE/I2 AS 'NBBODY'
    SALES
BY TOTAL COMPUTE GTID    /I2   = 99;
BY TOTAL COMPUTE CAR     /A16  = '';
BY TOTAL COMPUTE NCAR    /A20V = 'GrandTotal';
BY TOTAL COMPUTE COUNTRY /A10  = '';
BY TOTAL COMPUTE NCNTRY  /A20V = '';
BY TOTAL COMPUTE STID    /I2   = 99;
BY TOTAL COMPUTE MODEL   /A24  = '';
ON TABLE HOLD AS GTDATA
END
-RUN

-* MERGE DETAIL WITH S-TOT
TABLE FILE DETDATA
SUM NBBODY/I2C AS 'Nb Body'
    SALES/P8C  AS 'Sales'
BY GTID        NOPRINT
BY CAR         NOPRINT
BY NCAR        AS 'Car'
BY COUNTRY     NOPRINT
BY STID        NOPRINT
BY NCNTRY      AS 'Country'
BY MODEL       AS 'Model'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET PAGE-NUM NOLEAD
MORE
FILE STDATA
MORE
FILE GTDATA
END
-RUN

August 20, 2019, 10:48 AM

vnf

Thanks Martin for all your help.
I think that WF should have a easy way of doing this.

Would you use a macro to change the background color for subtotal and grand total?

August 20, 2019, 01:35 PM

MartinY

Either way will work

Without MACRO

-* MERGE DETAIL WITH S-TOT / G-TOT
SUM NBBODY/I2C AS 'Nb Body'
    SALES/P8C  AS 'Sales'
BY GTID        NOPRINT
BY CAR         NOPRINT
BY NCAR        AS 'Car'
BY COUNTRY     NOPRINT
BY STID        NOPRINT
BY NCNTRY      AS 'Country'
BY MODEL       AS 'Model'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET STYLE *
    INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
TYPE=DATA,
    BACKCOLOR=GREY,
    WHEN=STID EQ 33,
$
TYPE=DATA,
    COLOR=WHITE,
    BACKCOLOR=BLUE,
    WHEN=STID EQ 99,
$
ENDSTYLE
MORE
FILE STDATA
MORE
FILE GTDATA
END
-RUN

With MACRO

-* MERGE DETAIL WITH S-TOT / G-TOT
TABLE FILE DETDATA
SUM NBBODY/I2C AS 'Nb Body'
    SALES/P8C  AS 'Sales'
BY GTID        NOPRINT
BY CAR         NOPRINT
BY NCAR        AS 'Car'
BY COUNTRY     NOPRINT
BY STID        NOPRINT
BY NCNTRY      AS 'Country'
BY MODEL       AS 'Model'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET STYLE *
    INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
    DEFMACRO=STOT,
    MACTYPE=RULE,
    WHEN=STID EQ 33,
$
    DEFMACRO=GTOT,
    MACTYPE=RULE,
    WHEN=STID EQ 99,
$
TYPE=DATA,
    BACKCOLOR=GREY,
    MACRO=STOT,
$
TYPE=DATA,
    COLOR=WHITE,
    BACKCOLOR=BLUE,
    MACRO=GTOT,
$
ENDSTYLE
MORE
FILE STDATA
MORE
FILE GTDATA
END
-RUN