Need to calculate value within a report

[dab1477]

I consider myself a newbie, thanks for bearing with me.

I have a GROUPED report as follows:

Date by Day Dept or Loc Date Shift Total Good Orders Total Rejects Total Manhours

After a date range input, the report groups Dept or Loc, then for each shift, the report displays Total Good Orders, Total Rejects and Total Manhours. It does this for each of 4 groupings (Final Finish, Rough Insp, Setup, Shipping).

I want a text box - preferably in a header or footer, but I will put it anywhere, that will calculate Hours per Order (HPO) as
HPO = Grand Total of cum manhours from ALL 4 Dept or Loc / Total Good Orders from Shipping (1 of 4 from the group).

I have tried expressions and code unsuccessfully. Some of my other research noted that I can't do this within a header or footer (I am open to other areas on the report), but I am unable to create an accurate calculation within the detail section of the report. I believe my issue is that I want to use Grand Total or the Cum Sum of all Manhours.

I've also attached a .zip file of report output.

What expression or code can I use:

I've tried =sum(abs([Group of Dept or location]="shipping")*([Total Good Orders)]/sum([Total Manhours]) in the control source as an expression. No luck.

I've tried =sum(iff([Group of Dept or location]="shipping",([Total Good Orders)]/sum([Total Manhours],0) ...no luck

Thanks in advance

 

[vbaInet]

I don't know what your calculation is but basically SUM will only work on fields that exist in the Record Source of your report. So if you use SUM on a calculated field it will not work.

Repeat the same calculation within SUM(). That is substitute the name of the textbox where the calculation was performed with the actual calculation.

Or move the calculation to the Record Source, give it a name and sum using that new field name.