So, I’m a little stumped by a requirement that I was recently given for a new KPI. For the last 6 months or so, I have been reporting the Stock Status (grouped by ProdCode in a BAQ) as a monthly KPI. Last week, I was asked if this could be changed to show the stock value at each month over the last 5 years, meaning they want to see one bar chart with the monthly stock value (by ProdCode) for 2017-2021. I know that I could run a stock status for each month for the last 5 years and use those reports to summarize the historical values, but that seems like an extremely daunting task to be performed (running the Stock Status report 51 times and then loading the results into a UD table, and then adding a new record to the UD table each month moving forward). Anybody have any better ideas to better automate something like this?
-Jay
Don’t waste your time running the Stock Status Report if you are looking for historical values (value of the the stock in $$$). The date entered when running the SSR is only used to calculate the QOH on that date. regardless of the date entered, it uses the current cost.
Making a BAQ to simulate the SSR functionality is not very straightforward.
If you want to try it, the first hint is that SSR works by taking the current QOH and backing out all part transactions that happened after the date specified when running the report.
Thanks for the insight on that Calvin! It pretty much solidifies what I was already afraid of.
-Jay
Your best bet is to use the the GL for the inventory. The GL tables store value for each period. These could manipulated to arrive at the month end values and used in the chart.
Vinay Kamboj
And if you search this site you should find several topics that include query examples & common “gotchas”.
But… if you’re live on E9, I’m not sure how easy it will be using that version BAQ designer. I’m wondering if this might be a good candidate for building up some of the “stuff” as intermediate custom views to the SQL db first?