I’m currently trying (and failing) at creating a summary field. I would like summary of actual job hours in each OpCode for each assembly/sub assembly. I would like to display (1) Opcode summary of hours per assembly/subassembly (example summarize actual hours of Fab into one line per assembly/subassembly). I attached fields I have.
I created a basic calculated field sum (JobOper_ActProdHours) but am not getting what I need.
Thoughts on best way to resolve? Thanks!!
Sum fields.docx (30.6 KB)
Remove the Actual Prod column.
Or do you want that displayed along with a running sum of the hours for each OpCode?
Are you saying that for your first example, the highlighted field on fab, 28.55 should be all of the fab ops? Looking at what you have in the list, all of the fab ops should be 119.27 (there are 6 ops called fab). Can you explain why the total should be 28.55?
Looking at the first line, we see the sum_actual is exactly 3 x the Act Prod Hrs. This leads me to believe that his dataset has 3 BURN lines with 1.19 hrs each.
Because he has GROUP BY on all displayed fields but sum_actual, separate lines are generated when the ActProdHrs differ.
If the ActProdHrs field is removed, and a count_actual added (formula being count(JobOper_ActProdHours) ), he’d see:
There are zero hours logged to burn in assembly 0, but 4.79 shows up (I actually don’t have a burn operation in assembly 0). The same sum calculation is repeated over and over in my results. I need to see total hours clocked in each Operation Code per assembly.
For example - my report is showing for fab 119.27 hours for assembly 0, but actual hours logged against fab assembly 0 are 28.55 (when I review job in job tracker). Each fab operation in all assemblies is showing 119.27 hours, but each of the fab operation assemblies have very different hours logged.
I’m just not sure how to get the results I’m wanting. I’ve attached more of my results so you can see how the same sum is repeated over and over throughout all assemblies. Also including what hours results I should be seeing by assembly/oper code. We currently manually pull this information. Actual hours.xlsx (9.2 KB)
sum errors.docx (66.7 KB)
Copy the Query Phrase from the BAQ and paste it here.
Put 3 gave characters (the backwards single quote next to the 1 key) on a line above the code you paste, and 3 more on a line after the code). Like (ignore the actual code between the opening 3 graves and closing 3 graves, it’s just an example) :
That will make it look like the following when you post it.
SELECT OrderNum
FROM OrderHed
WHERE OrderHed.OrderNum > 12345
select
[JobHead].[JobClosed] as [JobHead_JobClosed],
[JobOper].[JobNum] as [JobOper_JobNum],
[JobAsmbl].[Parent] as [JobAsmbl_Parent],
[JobAsmbl].[AssemblySeq] as [JobAsmbl_AssemblySeq],
[JobAsmbl].[PartNum] as [JobAsmbl_PartNum],
[JobAsmbl].[Description] as [JobAsmbl_Description],
[LaborDtl].[ResourceGrpID] as [LaborDtl_ResourceGrpID],
(sum( LaborDtl.BurdenHrs )) as [Calculated_SUM_ACTUAL]
from Erp.JobOper as JobOper
inner join Erp.JobHead as JobHead on
JobOper.Company = JobHead.Company
and JobOper.JobNum = JobHead.JobNum
and ( JobHead.JobNum = '015455-1-1' )
inner join Erp.JobAsmbl as JobAsmbl on
JobHead.Company = JobAsmbl.Company
and JobHead.JobNum = JobAsmbl.JobNum
inner join Erp.LaborDtl as LaborDtl on
JobAsmbl.Company = LaborDtl.Company
and JobAsmbl.JobNum = LaborDtl.JobNum
and JobAsmbl.AssemblySeq = LaborDtl.AssemblySeq
where (JobOper.SubContract = FALSE)
group by [JobHead].[JobClosed],
[JobOper].[JobNum],
[JobAsmbl].[Parent],
[JobAsmbl].[AssemblySeq],
[JobAsmbl].[PartNum],
[JobAsmbl].[Description],
[LaborDtl].[ResourceGrpID]
order by JobOper.JobNum, JobAsmbl.AssemblySeq
I did change some of the fields - started using Labor DTL Resource group - which at least only displays the operations in the assemblies. But the totals are still not correct. Updated Sum error.docx (52.4 KB)
Do you have the Job Header joined directly to the Job Operation table? (I suck at reading SQL)
Job Head, doesn’t have enough info to join directly to JobOper, (Usually). You’re going to get some weird stuff if you don’t do it this way.
You should have Job head at table 1 joined to JobAsmbl joined to JobOper. Like shown below.
Here are your tables, with one criteria. The joins should come in automatically if you do it this way.
Here are your display fields with group by
and here is your calculated field.
I did this really quick so I might have missed something, but it should work.
I didn’t have Job Head in there! Thank you so much!!! It worked!!!
It’s working quite well now - I was able to add in more details as well. See attached for my results (and details). Wondering how I get all my data on one “line”. For example ASM 0 Oper Fab I would like the line to list est. prod hours, actual hours and then rework hours. Right now rework hours split on a new line. I’m assuming it’s because I added the rework column in - but I had to (or thought I did based on the error I was receiving without it) Rework error.docx (95.7 KB)
Rework is going to be another thing that gets grouped by since it’s just another labor detail, so it will add a row for rework, because now they don’t match.
If you want it all on one line you will need to do a subquery and add up the rework then bring that in to join to your table. Search the forum and you should find some examples. The best is to go into the tools guide in help and work through some of the BAQ examples, that will show you how to do sub queries. They aren’t that hard, just think of them like another table.
Here’s a good step by step on how to do a sub query
