Sums and grouping E10 Microsoft Report Builder

Epicor ERP 10
1

I’m a relative newbie for building reports with Report Builder and I have been having difficulty with calculating sums properly. The problem arises with the data I need to display on the report as a summary. The data consists of all costs for each assembly, and the hour breakdown of all operations for a given job. The data is broken down like this:

JOB
Assy1 Costs
Op1 Hours
Op2 Hours
Assy2 Costs
Op1 Hours
Op2 Hours
Op3 Hours

The problem comes in when I want a sum of assembly costs. Since the query iterates through each op cost, the assembly cost comes through each time making, in the example above, the Assy1 cost double, and the Assy2 cost triple.
This was a simple thing in Crystal I could say to sum by group change and I’d get an accurate sum. I can’t seem to find an option to do that in Report Builder.

I’ve been searching the internet for a day trying to find a solution, but haven’t had any luck. I can get an accurate per assy cost by dividing the sum by countrows() for each assembly. But this only works while under the assembly group. once I step out to the job group, count rows returns, as it should, the total of all rows and is useless.

Any ideas or help would be appreciated.

Rick

2

Can you just use First() instead of Sum() ?

3

That doesn’t work, because I need the SUM of all the assembly costs as well as the sum of all hours.

I did just get a work around solution from Epicor. What it boiled down to was that they knew of no way to get the results I wanted from within report builder. So the solution was to create subqueries in my BAQ to count the number of rows for operations, and the number of assemblies. Then divide each “Hour” field by the # of hour rows and each Assy cost field by the number of Assemblies. then the sums for the calculate hour field and the sums for the calculated assembly fields totalled correctly.

Thank you for responding though, I really do appreciate the input.