Create All-You’ll-Ever-Need Inventory Reports with Zoho Analytics
- Peter
- Difficulty: Beginner
- Estimated reading time: 15 mins
Zoho’s Infinite Customizability on Display Yet Again
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:
Once you have this table, you can build all sorts of useful reports using charts or pivot tables. Analyze quantities, movement, items, quickness of sale, which warehouses sell more and of what, etc.
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.
For more information on Zoho Analytics, check out our blog post on apps Zoho Analytics integrates with, as well as our Zoho Analytics vs Tableau comparison.
Want to learn even more?
Sign up for one of our Zoho courses!
Want this functionality, but don't want to do it yourself?
Click here to speak to us!
Need help with your Zoho system?
Hire a full time Zoho System Admin from our poolof highly qualified program graduates!
