Sage 500 natively provides a Business Insights Analyzer report called Inventory History.  This is a great report that shows the end-of-month quantities-on-hand.  Unfortunately, it only shows data for periods in which an inventory item (item-whse combination) had activity.  If the item-whse combo had no activity for the period, even if it was on-hand, the report does not display data for that item-whse combo for that period.  We wish this report showed data for every inventory record for every month, regardless of whether there was activity in the month.

I do not see a way to modify that report to suite our needs.  It is based on timInvtFiscPerHist, and it appears that table is only written to when there is a transaction.

That said, I'm wondering if anyone has created a Sage Intelligence report to provide what the native Inventory History BIA report offers, except have it show every item-whse record for every period.

Ideally I'd like to have a Sage Intelligence report that allowed me to select a year and an item/item description from a dropdown, and have it display the 12 end-of-month quantities for that item for that year.  A bonus would be if it broke it down to show Beginning Balance, Receipts (purchases), Usage (production consumption), Adjustments (physcial counts and corrections), and then Ending Balance.

Because the database does not include this natively, I'll have to bring in all inventory transactions since the beginning of time, and then calculate the running totals and display them in a useful manner by item by period.  My opinion is that it's not feasible, but I figured I would reach out to the community and ask if anyone has attempetd anything like this before.

Thanks for any thoughts you may have about historical inventory reporting in Sage 500.

Using 500 7.70.2 on SQL 2008 R2 with Excel 2013.  I have SI Report Manager and the Connector module.