Inventory QtyOnHand Graph Over Time for Date Range
There was a question today about how to create a graph of inventory QtyOnHand over time for a specific date range. The purpose of this post is to describe the process.
Today's QtyOnHand Inventory
The current QtyOnHand (Quantity on Hand) for any product is available in the table M_StorageOnHand. This table is updated in real-time with current values.
Historical QtyOnHand Inventory - Calculate it
There is no table of historical QtyOnHand values over time in iDempiere out of the box. Said another way, there is no table that will tell you the inventory of a product 5 weeks ago. Instead, you must calculate the quantity by taking the current QtyOnHand in M_StorageOnHand and adjusting it by the changes in M_Transaction. The Inventory Valuation Report is a good example of how to calculate the the inventory level at a given date.
Historical QtyOnHand Inventory - Create Your Own Snapshot
You can create your own historical QtyOnHand table. It is a fairly simple process. You would simply create a java or a database function that inserts the M_StorageOnHand records into ChuBoe_StorageOnhand_History (newly created table). You can automate this task by creating an iDempiere Process (for either java or sql function) and creating a Schedule to execute the Process on a timer (say once per day at 1AM).
Be aware that this table will grow quickly over time. One way to minimize the table's growth is to delete mid-month or mid-week records for entries older than two years. This means you would have the inventory levels for the first of the month/week going back as far as you have been keeping the records. If you choose to only keep first of the month entries, you table will grow 28 times more slowly. The down side of this decision is that you lose granularity for entries older than two year; however, most people do not care.
Calculating Values over Time with SQL
PostgreSQL has some cool tools to graph data over time. Here is a nice tutorial to take the above information and plot it for a given date.
Graphing Data
Once you have the data by date, then you can create iDempiere in-window reports/spreadsheets, graphs (like the Sales Rep Dashboard), or reports (say using Jasper Reports). In-window reporting is where you take the data/results and in expose it through a view in an iDempiere window. The data is read only because it is coming from a view; however, there is much you can do with it. You can export it, sort it, or graph it.