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.
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.
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
[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