Subreport sums inside a main report.

[swpsoak]

I have a main report that contains three (3) subreports…labor, material, PO.
My main report has three (3) Headers & Footers that allow grouping and sorting…supervisor, asset. wo.
Each subreport is successfully returning a calculated sum value of labor, material and PO value respectfully for each work order for each supervisor.
<b>BUT</b>, I have been trying, with no success to get a total cost for each wo that is the sum of each subreport <i>Sum([labor]+[material]+[PO])</i>. The values in each subreport under each wo are correct. I just need a grand total for each work order.
The subreports are getting their calculated values from a their own query and have a single calculated field…labor, material, po.
Any help will be greatly appreciated.

 

[swpsoak]

I got each row to finally sum by using the below calculation in the main report, placed in the wo footer, in a text box, in the control source…

=([labor].[Report]![labor_costs])+([material].[Report]![material_costs])+([po].[Report]![po_costs])

Works…but the next error is: If the query returns a work order (wo) with labor, material, but no po, the sum calculation will return #Error. This is not due to a po=0 or a null value, the wo just doesn't have a po record for it. Same is true if there is labor, po and no material…#Error. The calculation can not add a non-existent record. If all three values, labor, material & po have a value I can get a total.

If I could get the value to be zero ( 0 ) when no record exist, I could add it to the other two values.
I can add 0 but not &quot;no record&quot;.
Any way to get a sum when a record doesn't exist?

 

[swpsoak]

I got my sub reports to total. If anyone is interested you can download my example database at

http://members.socket.net/~oakoak/report/

 

[marcondes]

Swpsoak,

Your sample database helped me quite a bit. Thanks for posting it.

 

[Thom]

You can add the NZ function to all the individual calculated elements of the main summing equation. The NZ function - Null to Zero - takes two arguments, the actual value you are trying to calculate, and the second (I assume you want to use a zero) as the second argument if the NZ value sees a Null value.
Good luck...