Hi,
I’m trying to get all the costs for a job and it’s materials in one query. We have some jobs that make use of SubAssemblies but most parts we make the lower levels to stock and then issue them to the final part. Some end parts have 25+ in house manufactured part numbers on them and can go 5 levels deep. I don’t even know what to search for here in the help forum 
We do use lot tracking so all the issued materials have the job they were manufactured on as the lot number. I can create a dashboard using publish and subscribe (and have) but some of the parts are so complicated that it can get confusing really quickly. Any ideas where to start?
JobAsmbl holds all of that data.
TLA fields are This Level Actual.
LLA fields are Lower Level Actual. At Asm 0 that is all sub levels.
TLAMfgComp are parts you built and then issued to this job.
If you look at Job Tracker > Assemblies > Costs you should be able to recreate that data.
1 Like
The issue I’m having is I think I want a CTE and Union to get all the cost data from the jobs the parts issued to the top level job were made on. They are linked via parttran lot number = job the part was made on. I’ve found some posts on recursive CTEs and Unions but I don’t understand how to recreate it with the data I need 
I think I sort of understand what I need to do. Each of my queries gives me the correct data. I am having trouble with the union and getting them to show together.
I get this error: Column ‘Erp.JobHead.JobNum’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Invalid column name ‘JobHead1_JobNum’.
/*
* Disclaimer!!!
* This is not a real query being executed, but a simplified version for general vision.
* Executing it with any other tool may produce a different result.
*/select
[JobHead].[JobNum] as [JobHead_JobNum],
[JobMtl].[AssemblySeq] as [JobMtl_AssemblySeq],
[JobHead].[PartNum] as [JobHead_PartNum],
[JobAsmbl].[TLALaborCost] as [JobAsmbl_TLALaborCost],
[JobAsmbl].[TLABurdenCost] as [JobAsmbl_TLABurdenCost],
[JobAsmbl].[TLAMaterialCost] as [JobAsmbl_TLAMaterialCost],
[JobAsmbl].[TLASubcontractCost] as [JobAsmbl_TLASubcontractCost],
('Top Level') as [Calculated_JobType],
('Top Level') as [Calculated_IssuedTo]
from Erp.JobHead as JobHead
inner join Erp.JobAsmbl as JobAsmbl on
JobHead.Company = JobAsmbl.Company
and JobHead.JobNum = JobAsmbl.JobNum
inner join Erp.JobMtl as JobMtl on
JobAsmbl.Company = JobMtl.Company
and JobAsmbl.JobNum = JobMtl.JobNum
and JobAsmbl.AssemblySeq = JobMtl.AssemblySeq
where (JobHead.Company = @CompanyID and JobHead.JobNum = @JobNumber)
group by [JobHead].[JobNum],
[JobMtl].[AssemblySeq],
[JobHead].[PartNum],
[JobAsmbl].[TLALaborCost],
[JobAsmbl].[TLABurdenCost],
[JobAsmbl].[TLAMaterialCost],
[JobAsmbl].[TLASubcontractCost]
union
select
[JobHead1].[JobNum] as [JobHead1_JobNum],
[JobOper].[AssemblySeq] as [JobOper_AssemblySeq],
[JobHead1].[PartNum] as [JobHead1_PartNum],
(sum(JobOper.ActLabCost)) as [Calculated_LaborCost],
(sum(JobOper.ActBurCost)) as [Calculated_BurdenCost],
(sum(JobMtl1.TotalCost)) as [Calculated_MaterialCost],
(sum(JobAsmbl1.TLASubcontractCost)) as [Calculated_SubcontractCost],
('Level 1 Issued Job') as [Calculated_JobType],
[JobMtl1].[JobNum] as [JobMtl1_JobNum]
from Erp.JobMtl as JobMtl1
inner join Erp.PartTran as PartTran on
JobMtl1.Company = PartTran.Company
and JobMtl1.JobNum = PartTran.JobNum
and JobMtl1.PartNum = PartTran.PartNum
and ( PartTran.TranType = 'stk-mtl' )
inner join Erp.JobHead as JobHead1 on
PartTran.Company = JobHead1.Company
and PartTran.LotNum = JobHead1.JobNum
inner join Erp.JobAsmbl as JobAsmbl1 on
JobHead1.Company = JobAsmbl1.Company
and JobHead1.JobNum = JobAsmbl1.JobNum
inner join Erp.JobOper as JobOper on
JobHead1.Company = JobOper.Company
and JobHead1.JobNum = JobOper.JobNum
where (JobMtl1.Company = @CompanyID and JobMtl1.JobNum = @JobNumber)
group by [JobHead1].[JobNum],
[JobOper].[AssemblySeq],
[JobHead1].[PartNum],
[JobMtl1].[JobNum]
union
select
[JobHead2].[JobNum] as [JobHead2_JobNum],
[JobOper1].[AssemblySeq] as [JobOper1_AssemblySeq],
[JobHead2].[PartNum] as [JobHead2_PartNum],
(sum(JobOper1.ActLabCost)) as [Calculated_LaborCost],
(sum(JobOper1.ActBurCost)) as [Calculated_BurdenCost],
(sum(JobMtl2.TotalCost)) as [Calculated_MaterialCost],
(sum(JobAsmbl2.TLASubcontractCost)) as [Calculated_SubcontractCost],
('Level 2 Issued Job') as [Calculated_JobType],
[JobMtl2].[JobNum] as [JobMtl2_JobNum]
from Erp.JobMtl as JobMtl2
inner join Erp.PartTran as PartTran1 on
JobMtl2.Company = PartTran1.Company
and JobMtl2.JobNum = PartTran1.JobNum
and JobMtl2.PartNum = PartTran1.PartNum
and ( PartTran1.TranType = 'stk-mtl' )
inner join Erp.JobHead as JobHead2 on
PartTran1.Company = JobHead2.Company
and PartTran1.LotNum = JobHead2.JobNum
inner join Erp.JobAsmbl as JobAsmbl2 on
JobHead2.Company = JobAsmbl2.Company
and JobHead2.JobNum = JobAsmbl2.JobNum
inner join Erp.JobOper as JobOper1 on
JobAsmbl2.Company = JobOper1.Company
and JobAsmbl2.JobNum = JobOper1.JobNum
and JobAsmbl2.AssemblySeq = JobOper1.AssemblySeq
where (JobMtl2.Company = @CompanyID and JobMtl2.JobNum in (select JobHead1_JobNum from (select
[JobHead1].[JobNum] as [JobHead1_JobNum],
[JobOper].[AssemblySeq] as [JobOper_AssemblySeq],
[JobHead1].[PartNum] as [JobHead1_PartNum],
(sum(JobOper.ActLabCost)) as [Calculated_LaborCost],
(sum(JobOper.ActBurCost)) as [Calculated_BurdenCost],
(sum(JobMtl1.TotalCost)) as [Calculated_MaterialCost],
(sum(JobAsmbl1.TLASubcontractCost)) as [Calculated_SubcontractCost],
('Level 1 Issued Job') as [Calculated_JobType],
[JobMtl1].[JobNum] as [JobMtl1_JobNum]
from Erp.JobMtl as JobMtl1
inner join Erp.PartTran as PartTran on
JobMtl1.Company = PartTran.Company
and JobMtl1.JobNum = PartTran.JobNum
and JobMtl1.PartNum = PartTran.PartNum
and ( PartTran.TranType = 'stk-mtl' )
inner join Erp.JobHead as JobHead1 on
PartTran.Company = JobHead1.Company
and PartTran.LotNum = JobHead1.JobNum
inner join Erp.JobAsmbl as JobAsmbl1 on
JobHead1.Company = JobAsmbl1.Company
and JobHead1.JobNum = JobAsmbl1.JobNum
inner join Erp.JobOper as JobOper on
JobHead1.Company = JobOper.Company
and JobHead1.JobNum = JobOper.JobNum
where (JobMtl1.Company = @CompanyID and JobMtl1.JobNum = @JobNumber)
group by [JobHead1].[JobNum],
[JobOper].[AssemblySeq],
[JobHead1].[PartNum],
[JobMtl1].[JobNum]) as Level1))
group by [JobHead2].[JobNum],
[JobOper1].[AssemblySeq],
[JobHead2].[PartNum],
[JobMtl2].[JobNum]
I’ve done this twice and I still don’t understand it… but I’ll try to lay down a bumpy roadmap and hopefully not completely screw it up.
DISCAIMER: I am by no means an expert in this. I struggle every time I touch a CTE query. Every. Time. You should scrutinize everything you are about to read!
But, since I’m actively fumbling through this again for a project, I’ll share some of what I’m currently working on. You’ll see in the screen shots some extraneous fields, etc. as my current project is adding a secondary costing calculation… long story, not important… but you can just ignore those.
Anyway…
I start with a simply CTE query where I just add the JobAsmbl table.
And I just pull in the display fields so I can have access to all the TLA costs (This level assembly) for each assembly seq.
~~ SECOND QUERY ~~
I then create a second CTE Query. In this one, I again pull in JobAsmbl, but then I link it to the first CTE query.
Here, on the JobAsmbl1 table, I need to set AssemblySeq = 0
You’ll link these two tables where Job = Job.
And where the AssemblySeq from the JobAsmbl1 table = the Parent of the added subquery.
Here are my display fields:
The crux of this step is to lay down you baseline “AssyLevel”… i just use (0):
This one, you don’t have to do… but I build an assembly sequence “path” (string), which gets added to as the recursion goes. I can then drop that into reports if I want to.
For example, in my Production Detail report, I’ll end up with something like this, showing an assemblies parentage:
I can easily know that this assembly 18 is part of Asmbl 14, which is in Asmbl 3, which is in Asmbl 1, which is in Asmbl 0.
Again, not required, but its nice to have in your back pocket if you want it later.
The other calculated fields are SUMs of the values from the first query (AssyLvlCosts, is what I named that one). Because of the table relationships, these will sum, for example, the labor costs of all assemblies where Assembly 0 is the parent.
I will generally add the ISNULL statement in each one so I end up with zeros going forward and not null values.
Once, you start adding these SUM aggregate calculated columns, you’ll have to add the other columns to the group-by clause (see snip of display columns above).
~~ THIRD QUERY ~~
This is your Union All… and it looks just like the last query.
Pull in JobAsmbl (again)… and then pull in the second query you just created.
In this case, The AssemblySeq from the JobAsmbl table <> 0.
And, I flip the relationship so the Parent of the JobAsmbl table is the AssemblySeq of the Assy0CTE query (the second query).
Here are my display fields. A Union query MUST have the same number of fields as the previous query, AND each column must be the same data type as the previous query.
Here are the calculated fields:
NewParent: As the query recursively runs, you want to update the parent of the assembly.
AssyLevel1: You are going to take the AssyLevel from the previous query and add 1.
AssySeqPath2 (if you’re choosing to do this): You’re just appending the previous path with the current assembly number as it recursively moves forward.
All the other calculated fields, I’m just pulling in the fields from the original CTE query.
This may not have to be calculated fields, but I created them that way because I wasn’t sure if it was going to have to add the ISNULL statement again.
~~ NEXT QUERY ~~
This is where my current project gets messy so my screen shots are far less valuable… probably ridden with bad choices and may lead you astray. But this is what I did…
To start, make this a Top Level query so you can start testing and seeing results.
I brought in my Assy0CTE, and then I brought in the AssyLvlCosts (the very first query) twice.
For Table (3) in the above snip, I set that join where AssemblySeq = AssemblySeq (so, this will pull in all my Assembly 0 top level values).
For Table (2) (the one on the bottom), I set that join where AssemblySeq = Parent
Also have a table criteria on that one where AssemblySeq <> 0
Here are my display columns:
The first few are pulled in from the Assy0CTE query, and we’ll group on those. That gives you each asssemblyseq on the job, its parent, and the assembly level.
Just run a quick test on that. See if you get all the assemblies on the job to appear, AND that the correct parents are assigned, and you should see the assembly levels incrementing up.
If so, then recursion is working… and you can start creating more calculated fields to sum all the other stuff you need. I did a horrible job naming mine. But my “Labor” should be equivalent to “TLA Labor” and my “LaborNot0” should be equivalent to “LLA Labor”.
Anyway… this may help get you started, or it may only add to your frustration. In which case, I apologize in advance!
In my case, I have two more subqueries going on doing other things, so, again, this may not be the best example, but hopefully its a starting point.
2 Likes
That got me closer 
using a bunch of unions I can get the information for each level. I have to add a query for each drill down though since I still don’t understand how to make it recursive… I have a handmade spreadsheet that I am trying to reproduce in the BAQ.
What I can get
What I’m trying to get
part, Mtl Seq, Part Type, Costing, Lot
I finally figured it out!!! If it helps someone else in the future, here is my BAQ that will take a job number and drill down to show the material cost of every material issued, and go down all levels on the BOM even if the parts were made to stock, linking the lot issued back to the job it was created on.
* Disclaimer!!!
* This is not a real query being executed, but a simplified version for general vision.
* Executing it with any other tool may produce a different result.
*/with [MtlCTE] as
(select
[JobMtl].[Company] as [JobMtl_Company],
[JobMtl].[JobNum] as [JobMtl_JobNum],
[JobAsmbl].[AssemblySeq] as [JobAsmbl_AssemblySeq],
[JobHead].[PartNum] as [JobHead_PartNum],
[JobMtl].[MtlSeq] as [JobMtl_MtlSeq],
[JobMtl].[PartNum] as [JobMtl_PartNum],
[PartTran1].[LotNum] as [PartTran1_LotNum],
[PartTran1].[MtlUnitCost] as [PartTran1_MtlUnitCost],
[PartTran1].[LbrUnitCost] as [PartTran1_LbrUnitCost],
[PartTran1].[BurUnitCost] as [PartTran1_BurUnitCost],
[PartTran1].[SubUnitCost] as [PartTran1_SubUnitCost],
(CAST(JobMtl.PartNum AS NVARCHAR(MAX))) as [Calculated_Ind1],
(0) as [Calculated_Hierarchy]
from Erp.JobMtl as JobMtl
inner join Erp.JobHead as JobHead on
JobMtl.Company = JobHead.Company
and JobMtl.JobNum = JobHead.JobNum
and ( JobHead.Company = @CompanyID and JobHead.JobNum = @Jobnumber )
inner join Erp.JobAsmbl as JobAsmbl on
JobHead.Company = JobAsmbl.Company
and JobHead.JobNum = JobAsmbl.JobNum
inner join Erp.PartTran as PartTran1 on
JobMtl.Company = PartTran1.Company
and JobMtl.JobNum = PartTran1.JobNum
and JobMtl.MtlSeq = PartTran1.JobSeq
and ( PartTran1.Company = @CompanyID and PartTran1.TranType = 'stk-mtl' )
where (JobMtl.Company = @CompanyID)
union all
select
[JobMtl1].[Company] as [JobMtl1_Company],
[JobMtl1].[JobNum] as [JobMtl1_JobNum],
[JobAsmbl1].[AssemblySeq] as [JobAsmbl1_AssemblySeq],
[JobHead1].[PartNum] as [JobHead1_PartNum],
[JobMtl1].[MtlSeq] as [JobMtl1_MtlSeq],
[JobMtl1].[PartNum] as [JobMtl1_PartNum],
[PartTran2].[LotNum] as [PartTran2_LotNum],
[PartTran2].[MtlUnitCost] as [PartTran2_MtlUnitCost],
[PartTran2].[LbrUnitCost] as [PartTran2_LbrUnitCost],
[PartTran2].[BurUnitCost] as [PartTran2_BurUnitCost],
[PartTran2].[SubUnitCost] as [PartTran2_SubUnitCost],
(MtlCTE.Calculated_Ind1 + ' -> ' + JobMtl1.PartNum) as [Calculated_Ind2],
(MtlCTE.Calculated_Hierarchy+1) as [Calculated_Hierarchy2]
from Erp.JobHead as JobHead1
inner join Erp.JobMtl as JobMtl1 on
JobMtl1.Company = JobHead1.Company
and JobMtl1.JobNum = JobHead1.JobNum
and ( JobMtl1.Company = @CompanyID )
inner join Erp.PartTran as PartTran on
JobMtl1.Company = PartTran.Company
and JobMtl1.JobNum = PartTran.LotNum
and ( PartTran.Company = @CompanyID and PartTran.TranType = 'stk-mtl' )
inner join MtlCTE as MtlCTE on
MtlCTE.JobMtl_JobNum = PartTran.JobNum
and MtlCTE.JobMtl_PartNum = PartTran.PartNum
inner join Erp.PartTran as PartTran2 on
JobMtl1.Company = PartTran2.Company
and JobMtl1.JobNum = PartTran2.JobNum
and JobMtl1.AssemblySeq = PartTran2.AssemblySeq
and JobMtl1.MtlSeq = PartTran2.JobSeq
inner join Erp.JobAsmbl as JobAsmbl1 on
PartTran2.Company = JobAsmbl1.Company
and PartTran2.JobNum = JobAsmbl1.JobNum
and PartTran2.AssemblySeq = JobAsmbl1.AssemblySeq)
select
[MtlCTE1].[JobMtl_Company] as [JobMtl_Company],
[MtlCTE1].[JobMtl_JobNum] as [JobMtl_JobNum],
[MtlCTE1].[JobAsmbl_AssemblySeq] as [JobAsmbl_AssemblySeq],
[MtlCTE1].[JobHead_PartNum] as [JobHead_PartNum],
[MtlCTE1].[JobMtl_MtlSeq] as [JobMtl_MtlSeq],
[MtlCTE1].[JobMtl_PartNum] as [JobMtl_PartNum],
[MtlCTE1].[PartTran1_LotNum] as [PartTran1_LotNum],
[MtlCTE1].[PartTran1_MtlUnitCost] as [PartTran1_MtlUnitCost],
[MtlCTE1].[PartTran1_LbrUnitCost] as [PartTran1_LbrUnitCost],
[MtlCTE1].[PartTran1_BurUnitCost] as [PartTran1_BurUnitCost],
[MtlCTE1].[PartTran1_SubUnitCost] as [PartTran1_SubUnitCost],
[MtlCTE1].[Calculated_Ind1] as [Calculated_Ind1],
[MtlCTE1].[Calculated_Hierarchy] as [Calculated_Hierarchy]
from MtlCTE as MtlCTE1
3 Likes