Job materials and on-hand inventory for each warehouse

[dcamlin (David Camlin)]

This isn’t a full answer to your question but it should get you started.

In your case, I suppose I would start with the
JobAsmbl table (which gives you JobNum and top level assy part information).
Link JobMtl…
Then PartBin

Probably still want to make onhand qty a calculated sum field like in the below post. But leave the Warehouse column in there so you can see sum of each material part per warehouse.

Total On Hand Inv vs Netting Inv By Warehouse/Bin

Here’s a real quick/dirty example: [image] [image] Here’s example results so far: [image] Notice I’m seeing two different parts, but a LOT of different records. These only appear in (1) Warehouse, but they show up in multiple bins, and we also lot track these and you’ll get a row based on lot number too… so many rows in the results. But we can sum those… Add a Calc Field: [image] The “isnull” portion isn’t required, but depending on what you’re doing, sometimes null values can screw wi…

This would, I think get you all the data you needed. But how you expand on it and present it is up to you so I’m not sure how else to help at this point.

For example, do you want to see each material broken out? or just a true/false that a warehouse has all required materials?

You mentioned you wanted to see shortages… so you could create a calc field of ReqQty - SumOnHand (your calculated field) = ShortageQty

Again… not a full answer but should get your started. Get the BAQ built so that you’re getting all the data you want. Then you can move on to creating a BAQ report, if that’s the end game. A lot of grouping/formatting can be done on the SSRS side.