Possible with Subquery BAQ?

Epicor ERP 10
1

We are looking to have a BAQ for some basic cost analysis with the desired data: part, job, invoice, order based price, actual cost.

I connected JobProd -> InvcDtl to get part, job, and order based price. HOWEVER, I cannot figure out a good way to get the actual total cost (To get this number it seems that I need to connect the JobAsmbl table and get the sum of TLAMaterialCost, TLABurdenCost, TLALaborCost and TLASubcontractCost)

When I connect JobAsmbl to the JobProd table via JobNum, I get duplicate entries for each part/job/invoice because they each have multiple assemblies, therefore multiple actual costs.

I need the sum of each of these assembly actual costs to be placed along side the order based price for comparison. Is this something a subquery can do? I’m just beginning to work in E10 and am looking for any direction.

Thank you.

2

I think a sub-query will do what you want.

However, before you go down that route, have you looked at the cost fields in the InvcDtl table? They are given at the unit cost level, but adding up all the components and multiplying isn’t too hard in a calculated field.

3

Adam,

My suggestions is that first you use subqueries to get total job cost, once you get a subquery that has 1 lines per job containing total cost, then you can link it to the invoice table based on the job.

Hope this helps!

4

Sounds like you are going down the right path. Post your query phrase when you hit a roadblock. Makes helping you easier.

5

Sometimes you will have a Sub-Query that has a Sub-Query and if you apply GROUP properly you can get it right. Sometimes you will have a UNION and sometimes you will have a Sub-Select Query which is a Sub-Query invoked as a Calculated Field with a parameter.

Just remember to play around with GROUP BY, SUM() and Sub-Query within a Sub-Query sometimes helps with Grouping things.

If you can use SQL Studio first and do a prototype, it may help you.

Are you still on E9 looking at your Profile? If so that may be challenging.

1 Like
6

I don’t know how I missed those in the first place. Thank you! I’ll do some testing, but this may be just what we need…

I’ll still try the sub-query so that I can figure out how that works.

Thanks!

7

Actually, I don’t think this will work… just realized that I also need the “Estimated” total cost for each job, which I believe the estimates are only stored in the Asmbl table, unless I’m again missing something.

8

If you don’t want to see lower level assemblies and only care about the actual cost of the final job, then :
Filter JobAssy for ASM = 0 (show only top level assembly)
Then added up these fields (anything “LLA” is lower level Assy costs):
JobAsmbl.TLAMaterialCost + JobAsmbl.TLALaborCost + JobAsmbl.TLABurdenCost + JobAsmbl.TLASubcontractCost + JobAsmbl.LLAMaterialCost + JobAsmbl.LLALaborCost + JobAsmbl.LLABurdenCost + JobAsmbl.LLASubcontractCost