Report Footer Data Help

[sjd]

I am trying to create a fairly complex report that will show a full cost breakdown of making a part. I have it mostly working similar to the text below. Where I get stuck is Department and Part Cost.

The Part #, Department, and Operation are "Group By". The Operation group Footer includes: SUM(hours), SUM(pieces), calculates "OperationHoursPP" = SUM(hours) / SUM(pieces), MAX(rate), calculates "OperationCost" = [OperationHoursPP] * rate.

How do I set the Department Cost in the footer? It should be SUM([OperationCost]) -- which should point to the data in the Operation group footer... Instead, it fails to recognize the calculated data and asks for the parameter value when the report is opened. Is it possible to reference that calculated data?

Maybe there is a much simpler way that I am completely missing. I know I started with a report that only displayed the SUM for each operation (ignoring the details on a per Job # basis). Now I am trying to expand to include the details (per job #) and might be going about it in a much more complicated way than is necessary.

Note: "OPERATION Total" isn't exactly a total. Hours and Pieces are total. Hours/PC is calculated based on those totals. Cost is calculated from that Hours/PC. It is different than doing AVG([HoursPP]) or AVG([Cost]).

Part # | Department | Operation |Job # | Hours | Pieces | Hours/PC | Rate | Cost
Part_A | CNC | Programming |525435 | 1.0 | 10 | 0.1000 | $100.00 | $10.00
Part_A | CNC | Programming |365248 | 1.2 | 15 | 0.0800 | $100.00 | $8.00
Part_A | CNC | Programming |965225 | 1.1 | 12 | 0.0916 | $100.00 | $9.16
Part_A | CNC | Programming |251475 | 0.9 | 6 | 0.1500 | $100.00 | $15.00
OPERATION Total: | 4.2 | 43 | 0.0977 | $100.00 | $9.77

Part_A | CNC | Setup |525435 | 0.3 | 10 | 0.0300 | $100.00 | $3.00
Part_A | CNC | Setup |365248 | 0.4 | 15 | 0.0267 | $100.00 | $2.67
Part_A | CNC | Setup |965225 | 0.3 | 12 | 0.0250 | $100.00 | $2.50
Part_A | CNC | Setup |251475 | 0.4 | 6 | 0.0667 | $100.00 | $6.67
OPERATION Total: | 1.4 | 43 | 0.0093 | $100.00 | $3.25

Part_A | CNC | Machining |525435 | 3.0 | 10 | 0.3000 | $100.00 | $30.00
Part_A | CNC | Machining |365248 | 3.2 | 15 | 0.2133 | $100.00 | $21.33
Part_A | CNC | Machining |965225 | 3.1 | 12 | 0.2583 | $100.00 | $25.83
Part_A | CNC | Machining |251475 | 2.9 | 6 | 0.4833 | $100.00 | $48.33
OPERATION Total: | 12.2 | 43 | 0.2837 | $100.00 | $28.37

Department Total: | $41.39

Part_A | Grind | Machining |525435 | 2.0 | 10 | 0.2000 | $100.00 | $20.00
Part_A | Grind | Machining |365248 | 2.2 | 15 | 0.1467 | $100.00 | $14.67
Part_A | Grind | Machining |965225 | 2.1 | 12 | 0.1750 | $100.00 | $17.50
Part_A | Grind | Machining |251475 | 1.4 | 6 | 0.2333 | $100.00 | $23.33
OPERATION Total: | 7.7 | 43 | 0.1791 | $100.00 | $17.91

Department Total: | $17.91

Part Total: | $59.30

 

[billmeye]

Instead, try using:

Sum([OperationHoursPP] * rate) in your footer.

 

[sjd]

OperationHoursPP does the same thing - asks me to enter the value of the variable; instead of seeing the data in the footer of the group 'below' it. Meaning Group On order is 1)Part # -> 2) Deparment -> 3) Operation.

 

[rzw0wr]

[OperationHoursPP] is not a filed. It is a text box
First remove the [] and second it is a calculated text box so you can not use sum().

If I understand what I just read on another forum.

Dale

 

[sjd]

That makes sense, given the E in the Design, Grouping and Totals, ribbon bar does not show SUM as options for that field, but does for the non-calculated fields.

I suppose my question now is, can this sort of report be done?

Perhaps with sub-reports or something. (I am not really familiar with them -- yet.)

 

[pmproszynal]

Did you ever resolve this reporting SUMs issue?