I am taking PartMtl, Part, PartBin tables so far to create a listing of MOM & Materials with Qty Req from PartMtl - then group and calc field to get total QOH with a parameter of Finished part.
Next:
Am I able to create an input field (Qty needed) it would be blank for folks to input desired qty then have another field that would display qty needed based on the input field x partmtl_QtyPer = Qty Needed
If that works - create calc field from QOH- Qty Needed = Variance
@Mark_Wonsil I had never seen that report before. It is very nice. Oddly enough I happened onto very close to what @RPLEGRAND described in this post. It is @timshuwy’s bom_explode baq in the thread below. It is a little slow depending on your setup, but some people have added company to filters to make it run better.
Another standard dashboard is the production Planner Workbench.
It show all parts where the supply is less than the current demand and jobs that have all parts or are short of parts.
It will also show in the detail panel at the bottom of the dashboard if anything is buy direct.
Attached - Key in part and what qty you want to run:
Returns The parts with parent qty from BOM, Qty on hand, Requested qty = calc field, Variance, and what is on open purchase order for the part.
So in the BAQ part parameter - how would I go about adding multiple parts with multiple / different qty per part?
That’s going to be pretty hard to do, since there isn’t a way to relate parameters to other parameters. You can make a list of part numbers, using a list parameter. And you could use a list of quantities, I guess, but they wouldn’t know which one went with which.
I’m not saying it’s impossible, but you’re going to have to get real creative to make it work. You are probably better off doing a custom grid where you run the BAQ as many times as you need to based on input and keep adding to the grid data.
I added two new Subqueries:
ParameterBreakout & ParameterBreakout2 (to lose nulls)
//I made it a CTE, but you don't have to
with [ParameterBreakout2] as
(select
[ParameterBreakout].[Calculated_PartNum] as [Calculated_PartNum],
[ParameterBreakout].[Calculated_Qty] as [Calculated_Qty]
from (select top (100)
(ROW_NUMBER() OVER(ORDER BY PartTran.TranNum)) as [Calculated_RowNum],
([Ice].entry(1, BreakOnComma, '|')) as [Calculated_PartNum],
([Ice].entry(2, BreakOnComma, '|')) as [Calculated_Qty],
([Ice].entry(RowNum, @PartNum_Pipe_Qty, ',')) as [Calculated_BreakOnComma]
from Erp.PartTran as PartTran) as ParameterBreakout
where (ParameterBreakout.Calculated_PartNum is not null))
I added the ParameterBreakout2 query to your SubQuery1 and linked it to
I can try to explain anything if necessary.
The formatting of your parameter strings could be changed if this doesn’t work
for you. Just needs to be easy to parse.
