BAQ-DashBoard Question

I am still new to BAQ’s & Dashboard’s:

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

Thanks In Advance.

Hello Robert,

Have you looked at the Availability Report under Engineering? It seems to do what you’re asking.

1 Like

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

1 Like

As a BAQ so that hopefully it would become a dashboard for departments to use vs wasting paper to print

I just looked at this report - the only thing missing would be what is on order for each item.

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.

1 Like

Starting to sound like what you want is MRP…

Part, PartRev, PartMtl, Part1, PartWhse, Subquery = Open Po Qty
Parm= PartNum and Qty
Calc Fields with some rules.
Thanks for the help

1 Like

Use the PartDtl table. You can get everything you need from it. It’s like time phase, but with all the parts.

WI_BOM.baq (38.5 KB)

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 removed your parameter and added one called “PartNum_Pipe_Qty”

I populated it as follows

PartNum_Pipe_Qty = partnum1|qty1,partnum2|qty2

[Part][PIPE Character][Qty][Comma][Part][PIPE Character][Qty]

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

Part on Calculated_PartNum = Partnum

I removed your parameter from Part.

I replaced your parameter on RequestedQty with:

(PartMtl.QtyPer * ParameterBreakout2.Calculated_Qty)

Last I added ParameterBreakout2.Calculated_Qty to your Display Columns, and selected Group By

You can thank @ckrusen & @jdtrent for the hints

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.

WI_BOM_Modified.baq (75.2 KB)

1 Like

Thanks will load and test it Monday.

1 Like