Focal Point
[CLOSED] Adding a column total to a specific 'by' column.

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/4017015196

August 21, 2019, 05:24 PM
Greg_Coley
[CLOSED] Adding a column total to a specific 'by' column.
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
August 22, 2019, 07:33 AM
MartinY
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
August 22, 2019, 08:55 AM
Greg_Coley
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
August 22, 2019, 08:57 AM
BabakNYC
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
August 22, 2019, 09:07 AM
MartinY
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
August 22, 2019, 09:14 AM
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 ''



Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
August 22, 2019, 09:27 AM
Greg_Coley
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
August 22, 2019, 09:28 AM
MartinY
@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
August 22, 2019, 09:35 AM
Tom Flynn
Hi Greg,
Yep, doesn't work - multi-verb then...
Good Luck!


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
August 22, 2019, 09:40 AM
Greg_Coley
 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
August 22, 2019, 09:46 AM
BabakNYC
I don't think you need FST. anymore.


WebFOCUS 8206, Unix, Windows
August 22, 2019, 09:58 AM
Greg_Coley
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
August 22, 2019, 10:04 AM
Tom Flynn
Great Job Greg!!!
Well Done!


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
August 22, 2019, 10:16 AM
MartinY
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
August 23, 2019, 05:58 AM
Tony A
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