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,

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

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

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,

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

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 ''

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:

@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.

Hi Greg,
Yep, doesn't work - multi-verb then...
Good Luck!

 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 !

I don't think you need FST. anymore.

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

Great Job Greg!!!
Well Done!

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 ''

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