Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[solved]subtotal CNT.DST does not work
 Login/Join
 
vnf
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.7,
Application Studio,
Webfocus Info-Assist
iWay Service Manager,
iWay Data Migrator
Windows, All Outputs
IBM DB2/400, MS SQL-Server 2014
 
Posts: 43 | Registered: September 30, 2015Report This Post
MartinY
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.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Doug
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.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
vnf
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.7,
Application Studio,
Webfocus Info-Assist
iWay Service Manager,
iWay Data Migrator
Windows, All Outputs
IBM DB2/400, MS SQL-Server 2014
 
Posts: 43 | Registered: September 30, 2015Report This Post
Doug
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: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
MartinY
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.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
vnf
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.7,
Application Studio,
Webfocus Info-Assist
iWay Service Manager,
iWay Data Migrator
Windows, All Outputs
IBM DB2/400, MS SQL-Server 2014
 
Posts: 43 | Registered: September 30, 2015Report This Post
MartinY
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.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
vnf
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.7,
Application Studio,
Webfocus Info-Assist
iWay Service Manager,
iWay Data Migrator
Windows, All Outputs
IBM DB2/400, MS SQL-Server 2014
 
Posts: 43 | Registered: September 30, 2015Report This Post
MartinY
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.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
vnf
Silver Member
posted Hide Post
thanks for all your help Martin.
it works.


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

Read-Only Read-Only Topic

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

Copyright © 1996-2020 Information Builders