
Advanced Inventory Tracking in Zoho Analytics
- Peter
- Difficulty: Beginner
- Estimated reading time: 15 mins
If you are an inventory manager, this blog post could change your life. If you are not, it will at least teach you how to build some wicked Zoho Analytics inventory tracking for your business.
While talking not too long ago with Zoho, we learned that similar inventory reports are in the works. And by “in the works”, they really meant “oh, 1 1/2 – 2 years down the road we’ll get to it.” That’s a long time to wait for this kind of item-level tracking! So, we built our own reports that do the job nicely.
Whether you want to fix erroneous reporting, perform an audit, run analytics on inventory movement or more, the following Query table will help you get there. While reading this post, reference the Item-Level Tracking repository on our Github page.
The problem is that in Zoho Books and Inventory, there is still no universal search by Item Name or SKU. You can’t easily see all the transactions for a particular item. However, in Analytics, if you end up building separate reports for each transaction type, you get something like the following, where you have to add a filter for each report (and that’s just 4!). So, if you use this code, you can build a very clean, effective report, shown at the end.
At approximately 190 lines of SQL code, it looks intimidating! But, once you figure out how to build the SELECT query for one type of transaction, the other 7 are really just variations of the same. Each iteration starts off with a base table of transaction items (invoice items, package items, etc).
Then, there is a series of JOIN’s for each iteration. The most common are to join the Users, Warehouses, and Items tables, on their respective ID’s. For some reports, you will JOIN the Customers or Vendors tables. The trick is to make sure you are matching on the right fields.
LEFT JOINS ensure that you are not omitting any records, even if the match doesn’t work because there is no data on the “right side”.
Another feature in every iteration is the column “Original Record”. It is a concatenation of the base URL of every transaction (in Zoho Books) with its unique record ID. See **__insert_link_to_link_analytics_records_to_books_post__**
Also, we create that simple “Type” column, just adding in our own text, to enable easy filters.
Then, in the last iteration for Transfer Items, note that we JOIN the Warehouses table twice, to pull in the information for both the Source and Destination warehouse.
this should be a pic of the filtering by type
Then, drag and drop all the columns you want! I actually like to drop them all in the ‘Group By’ section because it gives a really nice layout and we don’t actually need to summarize anything.
Once you finish one, name and save it. Then, instead of starting from scratch, click ‘Save As’ and name it for your next report. Then, you only have to change the filter and your next report is complete! You may want to add/remove Customer or Vendor depending on which report it is.
Once you have all 8 reports, throw them all into a single dashboard. Because all these summary reports come from the same base table, when you go to add user filters like SKU or Warehouse, they will filter all 8 reports at once!
Zoho Analytics integrates with a multitude of apps. If you have an inventory manager or other coworker that prefers to work from Zoho Books or Inventory and not from Analytics, integrations with these apps will allow you to create custom buttons and webtabs.
Webtabs allow you to include a view of your Zoho Analytics dashboard inside of another Zoho app. No more switching tabs and apps—see it all in one place!
Custom links are dynamic and enable you to place buttons in different modules that can link back to a pre-filtered Zoho Analytics dashboard. For example, you could place that button in the Items module and when someone clicks on it, it takes them to the Inventory Tracking dashboard you just built, already filtered by that item! Then they can see every transaction for that item, all in one place.
With the SQL code already available in GitHub and an explanation written out here, we hope you can learn how to build and even improve these reports!
If it still seems a little too much to tackle on your own, consider looking at our Zoho Analytics course bundle which will teach—and show—you everything you need to know from beginning to end. Also, check out our detailed Analytics and Tableau comparison.
Then, if you have additional questions beyond that or other difficult tasks to be done in your Zoho system, consider reaching out to us here.