I need some assistance from the BAQ gurus. I need to be able to get a total quantity for all parts in inventory at any given date (very much like the Running Total field in Part Transaction History Tracker). I have figured out how to manipulate the data from the PartTran table to give me this total but only by using the “Show Summaries” on all the rows that the BAQ returns. This is pretty unusable on a large scale as it returns many, many thousands of rows. I would just like one row per part with that total. Any help is greatly appreciated. Thanks
This is what I have at the moment that works for an individual part. The sort makes the most recent transaction move to the top. But, like I said, this doesn’t work when I try to return all parts because it tries to return every transaction for every part. If anyone has another path to take to get this info I’m open to any suggestions.
select
[PartTran1].[PartNum] as [PartTran1_PartNum],
[PartTran1].[PartDescription] as [PartTran1_PartDescription],
(case
When (PartTran1.TranType like ‘STK-CUS’) then PartTran1.TranQty * -1
When (PartTran1.TranType like ‘RMA-INS’) then PartTran1.TranQty * -1
When (PartTran1.TranType like ‘STK-INS’) then PartTran1.TranQty * -1
When (PartTran1.TranType like ‘STK-MTL’) then PartTran1.TranQty * -1
When (PartTran1.TranType like ‘MFG-VAR’) then 0
Else PartTran1.TranQty
end) as [Calculated_TranQuant],
(PartCost.StdLaborCost + PartCost.StdBurdenCost + PartCost.StdMaterialCost + PartCost.StdSubContCost + PartCost.StdMtlBurCost) as [Calculated_TotalCost],
(TranQuant * TotalCost) as [Calculated_TotalValue],
[PartTran1].[TranDate] as [PartTran1_TranDate],
[PartCost].[StdLaborCost] as [PartCost_StdLaborCost],
[PartCost].[StdBurdenCost] as [PartCost_StdBurdenCost],
[PartCost].[StdMaterialCost] as [PartCost_StdMaterialCost],
[PartCost].[StdSubContCost] as [PartCost_StdSubContCost],
[PartCost].[StdMtlBurCost] as [PartCost_StdMtlBurCost],
[PartTran1].[TranQty] as [PartTran1_TranQty],
[PartTran1].[TranType] as [PartTran1_TranType],
[PartTran1].[SysTime] as [PartTran1_SysTime],
[PartTran1].[SysDate] as [PartTran1_SysDate]
from Erp.PartTran as PartTran1
inner join Erp.PartCost as PartCost on
PartTran1.Company = PartCost.Company
and PartTran1.PartNum = PartCost.PartNum
where (PartTran1.TranDate <= ‘01/25/2019’ and PartTran1.TranType <> ‘STK-STK’ and PartTran1.TranType <> ‘ADJ-CST’ and PartTran1.TranType <> ‘RMA-INS’ and PartTran1.PartNum = ‘G1501720.7Z43454’)
group by [PartTran1].[PartNum],
[PartTran1].[PartDescription],
[PartTran1].[TranDate],
[PartCost].[StdLaborCost],
[PartCost].[StdBurdenCost],
[PartCost].[StdMaterialCost],
[PartCost].[StdSubContCost],
[PartCost].[StdMtlBurCost],
[PartTran1].[TranQty],
[PartTran1].[TranType],
[PartTran1].[SysTime],
[PartTran1].[SysDate]
order by PartTran1.SysDate Desc, PartTran1.SysTime Desc
Can you export that actual BAQ and post it here? In theory, you should be able to make this a subquery and join it at your top level to the individual part numbers (this would give you one row for each part number).
D7BWFTESTER.baq (45.0 KB)
Thanks for the response. Here you go. I have tried to do what you suggested but maybe I’m going about it the wrong way as it never returns anything (maybe just timing out as this query return 1.2 million records when all parts are included).
D7BWFTESTER updated.baq (61.4 KB)
Here is your BAQ that I tweaked. I changed some assumptions. You were trying to add up from the beginning to a date. While technically that should work, as time goes on, your part tran tables are going to get larger and larger so the query will get slower and slower. Plus, looking back 3,4,5 years becomes less and less relevant. So I change it to take your current on hand inventory and work back to the date you selected. I had to change the sign on your calculated field for the transaction qty, since we are now going backwards.
The BAQ needs some cleaning up to be fully usable. I did not handle the nulls when you don’t have any on hand, or if there is no transaction history in your range. You’ll need to make a case statement for that. I didn’t mess with the costs either, I just tackled the quantities. Also, If it were me, I would make another calculated field to combine the part number columns for when one of them doesn’t exist so you can have a single column in your dashboard. Just another case statement if one column is null, use the other one, else use the first one.
And make sure you check this out, there still could be some transaction types, or filters that aren’t perfect. So find some edge cases to make sure you are getting what you expect.
4 Likes
I can’t thank both of you enough for your replies. You are both life savers.
This post was almost everything i was looking for. i was able to follow along and build the baq which has the running totals. this is great. question though how to get accurate cost from that date? the parttran keeps the costs there but how do you only return 1, which needs to be the most recent to the date given, with the current correct costs at that plant. we have 14 plants and run with avg costing. any idea on how to plug into this running total how to pull the current cost for the given date in the baq?
Sorry for the delay on the response. I actually had to modify this to do exactly what you’re asking for here (though for std cost). Hope this helps 
D7STOCKSTATUS.baq (44.6 KB)
Hey thank you for the follow up. This works on getting the costs but its a current cost its pulling. I’m running the BAQ to look at parts from 4/1 and my cost on an item than is 17.11822. Current Costs are 13.33338. I see the field in the part trans, mtl unit cost, but how to grab just that 1 date cost?
Pete
The way that I have done this is 2 queries. One to get the last transaction in Part tran before a date and then use that to get the cost.LastCost.baq (25.8 KB)