Have you or someone you know banged your head against a wall because Zoho Inventory can run Inventory Valuation reports, but not by warehouse? Well, despair no longer, because Zoho Analytics has your back.
Whether it’s inventory valuation, item tracking, or sales reports by item, Zoho Analytics can give you any kind of report you need. Plus, you can compile them all into a single dashboard and even share that view (via web tabs) inside other Zoho apps. Click this link for web tabs help in Zoho Books, but know that the principle is the same for any Zoho app.
The SQL code to build this table is a little more complex. View it here on our GitHub page. The first query, warehouse-inventory-count.sql, builds the base report. Essentially, it is a list of every entry in the entire system that affected the Inventory in any warehouse, whether that be inventory coming in through Purchase Orders, inventory moving through Inventory Adjustments, it being committed in a Sales Order or going out in an invoice, etc. It also calls on the Items table to calculate opening stock.
Different businesses may vary in how they want to track inventory. In this use case, inventory is counted as ‘In Stock’ once a purchase order is received, not just paid for. Then, it is counted out once an invoice is created (no draft or void invoices).
In the next query, warehouse-valuation.sql you can aggregate up all the entries and group them by Warehouse and Item, while also bringing in the pricing information to get a valuation by cost and retail price. All of this can be done in one query table, but it is much easier to understand what is going on, and adapt it for other purposes, but using both in separate steps. After running the second query, it should look something like this:
Again, you may not need this specific inventory valuation feature. But, hopefully the structure of these SQL queries can help you create a report that your business really does need.
Learn how to link Zoho Apps with Analytics using SQL. Instantly be taken straight to a specific record within another app from Analytics. ...
Whether you work primarily out of CRM or the Zoho Finance Suite, you can use Analytics to build commissions dashboards. This involves some fairly simple SQL code....