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     [CLOSED] Adding a column total to a specific 'by' column.
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Adding a column total to a specific 'by' column.
 Login/Join
 
Member
posted
Hello,

I have created a report using the following code:

TABLE FILE STAFFING_REPORT
SUM 
     CNT.STAFFING_REPORT.STAFFING_REPORT.PAYROLL_NAME AS '#'
     STAFFING_REPORT.STAFFING_REPORT.OVERTIME_HOURS_TOTAL AS 'OT'
BY  LOWEST J0.HOME_DEPARTMENT_PARENT.BRAND AS ''
BY  J0.HOME_DEPARTMENT_PARENT.HOME_DEPARTMENT_PARENT NOPRINT  AS ''
BY  J0.HOME_DEPARTMENT_PARENT.HOME_DEPARTMENT_DESCRIPTION AS ''
BY  LOWEST STAFFING_REPORT.STAFFING_REPORT.UNION_CODE_DESCRIPTION AS ''
BY  J0.HOME_DEPARTMENT_PARENT.BUDGET AS 'Budget'
ACROSS LOWEST STAFFING_REPORT.STAFFING_REPORT.Month_Int NOPRINT  AS ''
ACROSS LOWEST STAFFING_REPORT.STAFFING_REPORT.MONTH_STR AS ''
ON J0.HOME_DEPARTMENT_PARENT.BRAND SUBTOTAL AS 'Total'
ON J0.HOME_DEPARTMENT_PARENT.BRAND UNDER-LINE
ON J0.HOME_DEPARTMENT_PARENT.BRAND PAGE-BREAK
ON J0.HOME_DEPARTMENT_PARENT.HOME_DEPARTMENT_PARENT SUBTOTAL AS ''
ON TABLE SUBHEAD
"Staffing Summary for <+0>&LASTREPORTYEAR"
WHERE ( STAFFING_REPORT.STAFFING_REPORT.YEAR EQ '&LASTREPORTYEAR' ) AND ( STAFFING_REPORT.STAFFING_REPORT.POSITION_STATUS EQ 'Active' OR 'Leave' );


I have a subtotal on the HOME_DEPARTMENT_PARENT that successfully shows the total of the CNT.PAYROLL_NAME and OVERTIME_HOURS_TOTAL across MONTH_STR (so there is a overtime hours and head count for each month). What i'm not having much success doing is totalling the J0.HOME_DEPARTMENT_PARENT.BUDGET in the same way that the other two fields show up. I tried a few instances of "ON TABLE COLUMN-TOTAL J0.HOME_DEPARTMENT_PARENT.BUDGET" Which results in the error: FOC002) A WORD IS NOT RECOGNIZED: J0.HOME_DEPARTMENT_PARENT.BUDGET
. Am i even on the right track here ?

This message has been edited. Last edited by: FP Mod Chuck,


Greg Coley
 
Posts: 11 | Location: Pittsburgh, PA | Registered: February 13, 2019Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Not 100% clear for me

You want to perform the BUDGET total or a total PAYROLL & OVERTIME_HOURS_TOTAL by BUDGET ?

You cannot perform the total of a BY field (in your case J0.HOME_DEPARTMENT_PARENT.BUDGET) but only a total of the SUM fields according to the BY fields
The error you have is exactly that : telling you that you are trying to perform a total on a field that is not part of the SUM fields

If can be easier to explain, post a sample of actual and desired result


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: 2224 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Member
posted Hide Post
I would like to total the budget column. I have it as a 'BY' Field because It only needs to show up once, not across every month like would happen if it was a sum field. Take a look at this Screenshot, Yellow highlights are the budget numbers, they're ints so there should not be an issue adding them together. The red circles are where i expect to add totals. at the moment thee place where i want to put he totals is occupied with total tags. \

Heres the link to the screenshot incase the embedded img doesn't work: Screenshot


Greg Coley
 
Posts: 11 | Location: Pittsburgh, PA | Registered: February 13, 2019Reply With QuoteReport This Post
Virtuoso
posted Hide Post
You probably need a multi-verb request.
  
TABLE FILE ibisamp/car
SUM SALES
BY COUNTRY RECOMPUTE  AS 'Subtotal:'
BY CAR
ACROSS BODYTYPE
SUM RCOST
BY COUNTRY
BY CAR
ON TABLE PCHOLD FORMAT HTML
ON TABLE NOTOTAL
ON TABLE SET CACHELINES 100
ON TABLE SET GRWIDTH 1
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$

ENDSTYLE
END

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


WebFOCUS 8206, Unix, Windows
 
Posts: 1707 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Can't see your image. imgur not showing any images for me

As Babak suggested, something such as this

TABLE FILE GGSALES
SUM BUDDOLLARS
BY REGION

SUM DOLLARS
BY REGION
BY CITY
ACROSS CATEGORY
ON TABLE COLUMN-TOTAL
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: 2224 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
Before you try a multi-verb, try FST. and remove the BY:

  
TABLE FILE STAFFING_REPORT
SUM 
     FST.J0.HOME_DEPARTMENT_PARENT.BUDGET AS 'Budget'
     CNT.STAFFING_REPORT.STAFFING_REPORT.PAYROLL_NAME AS '#'
     STAFFING_REPORT.STAFFING_REPORT.OVERTIME_HOURS_TOTAL AS 'OT'
BY  LOWEST J0.HOME_DEPARTMENT_PARENT.BRAND AS ''
BY  J0.HOME_DEPARTMENT_PARENT.HOME_DEPARTMENT_PARENT NOPRINT  AS ''
BY  J0.HOME_DEPARTMENT_PARENT.HOME_DEPARTMENT_DESCRIPTION AS ''
BY  LOWEST STAFFING_REPORT.STAFFING_REPORT.UNION_CODE_DESCRIPTION AS ''

ACROSS LOWEST STAFFING_REPORT.STAFFING_REPORT.Month_Int NOPRINT  AS ''
ACROSS LOWEST STAFFING_REPORT.STAFFING_REPORT.MONTH_STR AS ''
ON J0.HOME_DEPARTMENT_PARENT.BRAND SUBTOTAL AS 'Total'
ON J0.HOME_DEPARTMENT_PARENT.BRAND UNDER-LINE
ON J0.HOME_DEPARTMENT_PARENT.BRAND PAGE-BREAK
ON J0.HOME_DEPARTMENT_PARENT.HOME_DEPARTMENT_PARENT SUBTOTAL AS ''


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1958 | Location: Centennial, CO | Registered: January 31, 2006Reply With QuoteReport This Post
Member
posted Hide Post
quote:
Originally posted by Tom Flynn:
Before you try a multi-verb, try FST. and remove the BY:

  
TABLE FILE STAFFING_REPORT
SUM 
     FST.J0.HOME_DEPARTMENT_PARENT.BUDGET AS 'Budget'
     CNT.STAFFING_REPORT.STAFFING_REPORT.PAYROLL_NAME AS '#'
     STAFFING_REPORT.STAFFING_REPORT.OVERTIME_HOURS_TOTAL AS 'OT'
BY  LOWEST J0.HOME_DEPARTMENT_PARENT.BRAND AS ''
BY  J0.HOME_DEPARTMENT_PARENT.HOME_DEPARTMENT_PARENT NOPRINT  AS ''
BY  J0.HOME_DEPARTMENT_PARENT.HOME_DEPARTMENT_DESCRIPTION AS ''
BY  LOWEST STAFFING_REPORT.STAFFING_REPORT.UNION_CODE_DESCRIPTION AS ''

ACROSS LOWEST STAFFING_REPORT.STAFFING_REPORT.Month_Int NOPRINT  AS ''
ACROSS LOWEST STAFFING_REPORT.STAFFING_REPORT.MONTH_STR AS ''
ON J0.HOME_DEPARTMENT_PARENT.BRAND SUBTOTAL AS 'Total'
ON J0.HOME_DEPARTMENT_PARENT.BRAND UNDER-LINE
ON J0.HOME_DEPARTMENT_PARENT.BRAND PAGE-BREAK
ON J0.HOME_DEPARTMENT_PARENT.HOME_DEPARTMENT_PARENT SUBTOTAL AS ''


So the problem with doing it like this (and it may not be as big a problem as i think it is, i'm checking with the powers to be to see if they're okay with it) is that the budget goes from displaying once before the repetition of months, to showing up in each month, which will eventually crowd the screen when i have twelve months in the report. See screenshot here:


Greg Coley
 
Posts: 11 | Location: Pittsburgh, PA | Registered: February 13, 2019Reply With QuoteReport This Post
Virtuoso
posted Hide Post
@Tom
But BUDGET will be displayed at every month and not only once

quote:
Originally posted by Greg_Coley:
I have it as a 'BY' Field because It only needs to show up once, not across every month like would happen if it was a sum field.


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: 2224 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
Hi Greg,
Yep, doesn't work - multi-verb then...
Good Luck!


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1958 | Location: Centennial, CO | Registered: January 31, 2006Reply With QuoteReport This Post
Member
posted Hide Post
 TABLE FILE STAFFING_REPORT
SUM
    FST.J0.HOME_DEPARTMENT_PARENT.BUDGET AS 'Budget'
BY  LOWEST J0.HOME_DEPARTMENT_PARENT.BRAND AS ''
BY  J0.HOME_DEPARTMENT_PARENT.HOME_DEPARTMENT_PARENT NOPRINT  AS ''
BY  J0.HOME_DEPARTMENT_PARENT.HOME_DEPARTMENT_DESCRIPTION AS ''
BY  LOWEST STAFFING_REPORT.STAFFING_REPORT.UNION_CODE_DESCRIPTION AS ''
SUM
     CNT.STAFFING_REPORT.STAFFING_REPORT.PAYROLL_NAME AS '#'
     STAFFING_REPORT.STAFFING_REPORT.OVERTIME_HOURS_TOTAL AS 'OT'
BY  LOWEST J0.HOME_DEPARTMENT_PARENT.BRAND AS ''
BY  J0.HOME_DEPARTMENT_PARENT.HOME_DEPARTMENT_PARENT NOPRINT  AS ''
BY  J0.HOME_DEPARTMENT_PARENT.HOME_DEPARTMENT_DESCRIPTION AS ''
BY  LOWEST STAFFING_REPORT.STAFFING_REPORT.UNION_CODE_DESCRIPTION AS ''
ACROSS LOWEST STAFFING_REPORT.STAFFING_REPORT.Month_Int NOPRINT  AS ''
ACROSS LOWEST STAFFING_REPORT.STAFFING_REPORT.MONTH_STR AS ''


The Multi-Verb has delivered the desired Results. Thanks !


Greg Coley
 
Posts: 11 | Location: Pittsburgh, PA | Registered: February 13, 2019Reply With QuoteReport This Post
Virtuoso
posted Hide Post
I don't think you need FST. anymore.


WebFOCUS 8206, Unix, Windows
 
Posts: 1707 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Member
posted Hide Post
If i don't the numbers are WAY too high, and they are correct with FST included. Now, that's not to say I haven't screwed something up in the multi-verb request to cause the need for FST. However the code i have in the post above puts out the report exactly as desired With exception to the need of some minor formatting modifications : Screenshot


Greg Coley
 
Posts: 11 | Location: Pittsburgh, PA | Registered: February 13, 2019Reply With QuoteReport This Post
Expert
posted Hide Post
Great Job Greg!!!
Well Done!


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1958 | Location: Centennial, CO | Registered: January 31, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
quote:
Originally posted by Greg_Coley:
If i don't the numbers are WAY too high,


The problem is related to the used BY fields that are the same in both section of the multi-verb.
You need to have the BY fields for top section up to where the budget is defined and not at a subsequent level.

Let say that you budget are defined per year, then you should not have a BY month. Otherwise the budget will be repeated at each month (which explain the need of FST or LST to only have one value for all months and not one value per month which result in 12 times the budget).

So, probably that one of below BY fields is too "deep" as for budget speaking
BY  LOWEST J0.HOME_DEPARTMENT_PARENT.BRAND AS ''
BY  J0.HOME_DEPARTMENT_PARENT.HOME_DEPARTMENT_PARENT NOPRINT  AS ''
BY  J0.HOME_DEPARTMENT_PARENT.HOME_DEPARTMENT_DESCRIPTION AS ''
BY  LOWEST STAFFING_REPORT.STAFFING_REPORT.UNION_CODE_DESCRIPTION AS ''


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: 2224 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
For me, Tom was on the correct track by including the FST.BUDGET ... but LEAVE the BY BUDGET but NOPRINT it. Also keep the multiverb request.

Example using the CAR file. Potentially not as complex as your report but structurally similar?
TABLE FILE CAR
SUM DCOST
 BY COUNTRY
 BY CAR
 BY MODEL
 BY DCOST NOPRINT
SUM CNT.CAR AS 'Count'
	RCOST
 BY COUNTRY
 BY CAR
 BY MODEL
 BY DCOST NOPRINT
ACROSS BODYTYPE AS ''
ON COUNTRY SUBTOTAL AS 'Total' UNDER-LINE PAGE-BREAK
ON TABLE SET PAGE NOLEAD
ON TABLE NOTOTAL
ON TABLE SET STYLE *
grid=off, $
type=acrossvalue, justify=center, $
type=subtotal, justify=right, $
ENDSTYLE
END


T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.06 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5616 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Adding a column total to a specific 'by' column.

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