I have a request to update a dashboard to include a total of WIP by Part Number BUT, they only want to include jobs that have had a transaction against them (weather be set-up, production or material). They do not want to include WIP jobs that have not started yet.
I cannot figure out how to get this on a dashboard and hoping some of the experts here can lend a hand.
The current BAQ is below and looks fantastic, but this WIP thing.,…grrrr
Everything I have tried does not work
What have you tried? In my mind, the starting point is to identify open Jobs by part number and then filter those Jobs to ones with material or labor transactions.
It sounds like you’re looking to quantify your started, production backlog and not the financial value of that WIP, yes? The former shouldn’t be too bad, but the latter would be quite the exercise.
If you’re looking for transactional level criteria, then I would suggest a summarized subquery for count of LaborDtl records for the given job. Making that an equal join should remove the jobs with no transactions.
My thoughts were along the lines of linking to JobHead then Job Assembly Records. Using the Costs summary fields here for labor, burden, material, subcontract, skip jobs with zero. Also link to the JobProd table to see if any parts were transferred to finished goods. If so use this as a percentage of the costs remaining.
you are correct. They want quantity.
I am not good at Sub queries though and it is for a total of 193 parts, not just 1.
This is the data that I need to add a column to. (Note, highlighted are already calculated fields.
The dashboard itself highlights if we are below safety and reorder to highlight the production that needs to be worked on first.
I ended up creating a separate BAQ and tying the Part=Part through the dashboard and I could not get the sum to calculate correctly in a calculated field.
This sounds simpler and no subqueries required if we can make a few assumptions.
You could just link JobAsmbl table to JobHead as Patrick mentions. Set a table criteria for Asm=0. Then look at the TLA (this level actual) and LLA (lower level actual) fields. If added together they have a non-zero value, then something has been recorded against the job ie: Labor, Burden, Material or Subcontract.
Edit: Forgot the assumptions.
This should work assuming you have no materials at $0, no labor or burden rates at $0, and no subcontract at $0.
That would work if I was looking for costs, not WIP Qty
In this scenario, you are only using the costs to determine if activity has been recorded so that you can only show jobs in process. Or at least that was the thought process above.
How are you defining WIP Qty? If a job is for a quantity of 100 finished parts, and the job started (0.1 hour of production activity, for example)… are you then saying that (1) part is in WIP, or all (100) from that job are in WIP?
For example… I have a dashboard tracking Job progress which includes results like the below:
These are mainly just calculated fields derived from the JobOper table and LaborDtl tables.
JobAsmbl table gives me required qty of a given part number (whether it is assembly 0 or a subassembly).
Estimated hrs are a sum of setup and production hours from JobOper table.
Actual hrs are a sum of LaborDtl hours.
Estimated % (complete) is just ActHrs/EstHrs.
Last Labor is max(LaborDtl.CreateDate)… so the last time that job was worked on.
In my world, I would say all these parts (quantities) are in WIP… the jobs have started.
Yes, I tried that, but the above is summed by job. I am pulling the info starting at the part level because they want the totals by Part. And they want to Total WIP in production IF the job has 1 transaction
I tried to create a subquery as you outlined above, but the fields do not tie together, only giving 1 total WIP for each part.
Even taking JobProd.Qty, returns the rows of qty, listing the jobs, but then doing a sum(JobProd.Qty), turns out bad data.
I think PartWip has the data you are looking for.
