Thanks Ken.
I’ll occasional go back to this, just for the challenge. Even if I don’t need to do it, I find doing these types of things are the best learning tools.
And I was aware of the TranType affecting the sign of the TranQty. But I’m sure there are even more demons around every corner.
guys, i tried for BAQ, half done…but as @ckrusen said…there are even more demons around every corner…there are so many difficulties and confusions with summerizing total inward and total issues. Need to rectify all transaction types…
Forgive me if I have the wrong end of the stick but:
Partbin - you can get partbin, onhandqty, warehouse code,
Part - you can get part description,
Partcost - you can get the STD costs.
Join all 3 together and you’ll get a mirror of the stock status.
I built the above in Excel through SQL.
If you want it more detailed by transaction…Yes I’d suggest you use parttran.
@Rowley150 :
Good idea, but partbin shows the current stock of till date. If i want to retrieve stock from parttran, how we will get the stock on CutOff date like Stock Status ? Now here, tranDate comes into picture. Now assume that on 1st of feb, the part XYZ had null stock, and today it is 200 Nos onHandQty. Its not possible to retrieve the stock of Part XYZ for 1st Feb by partbin,part,partcost, becoz the main link of tranDate is not there. So the only option is PartTran, so we should have to make BAQ for PartTran, am i correct ?
Thanks
Yes - PartTrans needed to duplicate the “As Of” feature for the Stock Status report.
I think a BAQ filtered by PartTran.InventoryTran = True should get you pretty close to start with.
Beyond that I’m pretty sure I ran into a few TranTypes/exceptions the last time I looked at this in E9.
But can’t remember specifics right now…
Hey guys,
Yuhhhhhhhhhhh…finally a BAQ is ready for Stock Status . I have applied a tracker view for cutoff date also.
Thank you very much guys for your suggestions.
Thanks.
Prakash,
Can you share what you did then? We have a BAQ like Mark’s using Part -
PartBin - PartCost tables. But would be interested to compare it to yours.
Hey randy,
I will be happy to share BAQ with all of you, i just finalizing the ‘Cut Off Date’ functionality. It is not working as as expected, still working on it. I will post the BAQ as soon as i finshed.
Thanks
No worries, I’m sure we’ve all had our share of BAQ quirks to hunt down and
correct. 
Hello guys,
Still fighting with ‘Cut off Date’ . Criteria / parameter in BAQ, Date Range filter in Tracker view not working. Tried all available tricks…but still BAQ is incomplete without ‘Cut off date’…
I think , i have used 4 instatnces in BAQ ,screenshot is attached. Is it the problematic ? If i had not used,then i cant apply crieteria for 3 instances at a time.
Anybody has any suggestions ? please share…
Thanks
Have you tried adding the CutOff data as a criteria on all 4 PartTran tables/aliases and having the those 4 criteria reference a single mandatory BAQ parameter.
When the BAQ is called from the dashboard it will automatically call popup the cutoff date which will also make sure they query isn’t run wide open.
Hi guys,
Sharing the Stock status BAQ and Dashboard with all of u, please make modifications as per your requirements as i displayed only Part number, Description, OnHandQty, UOM. In BAQ there are 6 more columns reffered for calcualting final OnHandQty as multiple IF-ELSE statements are not allowed in calculated filed. You can hide rest of the columns in trackerview.
Have a nice weekend.
Thanks guys…
Stock Status by cutofDate Tracker.dbd (156.4 KB)
Hi Guys,
I am trying to make this dashboard more specific like warehouse wise, lot wise. But version 9.05.702A doesn’t support group by clause. I am facing some difficulties while displaying qtys warehouse wise. Now i can display total on hand qty on those warehouses, whichever was on stock on cut of date. But it displaying in totality. I want to bifurcate it warehouse wise… Any help,suggession will be appreciated.
The test part has 48 qtys in MAIN warehouse and 2 Qtys in WIP warehouse, and results coming in below format,it means sum is not calculated warehouse wise. How to bifurcate it ?
Thanks
E9 series being Progress based is more limited than E10 on the grouping. For reports, we ended up doing the final groupings in Crystal.
Dashboards we’d use the Group By on the list/grid views or copy/paste it into excel
No other way to show group by partnum and Warehouse ? i am stucked on group by…
Thanks for sharing your dashboard
Just an FYI
I noticed some On Hand Qtys were off when I tested your example here.
One part has quantities in two different plants and a transfer qty.
I want to take look thru the design details but… not sure when I’ll get the time.
Will let you know when/if I see anything… if it’s related to your warehouse issue.
How to bifurcate it
Maybe view(s) to aggregate some values beforehand, and then an external BAQ?
Or maybe executive query(s)?
Sometimes it can be tough to get everything you want with just E9 BAQ phrase builder.
Hi Bruce,
In this case, external query doesn’t works. BAQ designer throws Connection time out error. As external BAQ property "Connection Timeout " is cannot be modified, the default timeout 30 seconds cannot returns this huge calculations back to BAQ results. So there are less chances of success with external query.
Thanks
Yes, I remember this now.
And now that I think about it, executive queries are not realtime, so not a solution either.
Definitely a challenge to use only the BAQ tools in E9.
Hey Bruce,
Luckily, now external query working for us.we have succesfully resolved time out error by reducing criteria at primary level,at the time of SQL server view creation. So now, just create a view for few parts,with all transaction classes, summerize it by warehouse or lotwise in BAQ. I said few bcoz, it will reduce your real time execution ,so you can avoid Time-Out error. You can devide parts as per class,starting Initial or other criteria you want.
I think, the dashboard of warehouse wise stock will cover Stock Status Report, except Extended cost, it will not be a big deal to add extended cost in our view…
Thanks