Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [solved]subtotal CNT.DST does not work
Go
New
Search
Notify
Tools
Reply
  
[solved]subtotal CNT.DST does not work
 Login/Join
 
Silver Member
posted
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,


WebFOCUS 8.2.0.3,
Application Studio,
Webfocus Info-Assist
iWay Service Manager,
iWay Data Migrator
Windows, All Outputs
IBM DB2/400, MS SQL-Server 2014
 
Posts: 40 | Registered: September 30, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
</>


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2159 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
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.


   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Most recent: 8204 Gen 48 in Test and Production.
   Currently Available. Please contact me.
 
Posts: 2976 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
Silver Member
posted Hide Post
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


WebFOCUS 8.2.0.3,
Application Studio,
Webfocus Info-Assist
iWay Service Manager,
iWay Data Migrator
Windows, All Outputs
IBM DB2/400, MS SQL-Server 2014
 
Posts: 40 | Registered: September 30, 2015Reply With QuoteReport This Post
Expert
posted Hide Post
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.
 
Posts: 2976 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
@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 ?


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2159 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Silver Member
posted Hide Post
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


WebFOCUS 8.2.0.3,
Application Studio,
Webfocus Info-Assist
iWay Service Manager,
iWay Data Migrator
Windows, All Outputs
IBM DB2/400, MS SQL-Server 2014
 
Posts: 40 | Registered: September 30, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2159 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Silver Member
posted Hide Post
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?


WebFOCUS 8.2.0.3,
Application Studio,
Webfocus Info-Assist
iWay Service Manager,
iWay Data Migrator
Windows, All Outputs
IBM DB2/400, MS SQL-Server 2014
 
Posts: 40 | Registered: September 30, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2159 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Silver Member
posted Hide Post
thanks for all your help Martin.
it works.


WebFOCUS 8.2.0.3,
Application Studio,
Webfocus Info-Assist
iWay Service Manager,
iWay Data Migrator
Windows, All Outputs
IBM DB2/400, MS SQL-Server 2014
 
Posts: 40 | Registered: September 30, 2015Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [solved]subtotal CNT.DST does not work

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.