sign in
sign up

Advanced Item Tracking in Zoho Analytics

  • Difficulty: Beginner
  • Estimated reading time: 15 mins

Advanced Item Tracking in Zoho Analytics

Using this query table, along with Zoho’s summary reports built using it as a base table, you can track your item-level inventory through every possible transaction in one centralized dashboard in Zoho Analytics. You can build something similar without the 100+ lines of SQL code, but this report enables you to create a clean, user-friendly dashboard that only requires one User Filter instead of one for every single report – and there are 8.

Setup

Follow the same proceducures that you would to build any other query table. Simply ensure the correct sync is in place between Analytics and the Zoho Financial Suite. For this table in particular, you will need the ID’s of every type of transaction, the Product ID’s, the Users table and info, Warehouses, Vendors, Customers, etc.

Code Walkthrough

Each of the 8 reports is similar. They are all built on an “Items” table. Then, taking advantage of the various lookup columns, we join other tables to it to bring in other information, like the Salesperson, Warehouse, Status, Vendor, or Customer, etc.

So first, for no particular reason, we have the Inventory Adjustments Items table. From this we can pull the quantity adjusted and the date. To that we join the Inventory Adjustments table, which helps us bring in the Order Number and the Reason. We connect the Warehouses table next to join in the warehouse name, the Items table to get the Item namd and SKU, and last, the Users table so we can see who created the Inventory Adjustment. There are, of course, no vendors or customers associated with Inventory Adjustments, so those remain blank.

The ‘Original Record’ column is a concatenation of the base URL of the record and its record ID, which we then turn into a URL that links to the original record in Zoho Books. After you build, save, and view the Query table, click on that column, click More > Change Data Type > URL.

Now, follow a similar process for the next 7 reports. The order of the columns is not particularly important, but once you set it in the first SELECT command, the column order in subsequent SELECT commands must match. Likewise, table aliases and names are not really important. I have used a variety of naming methods here to demonstrate, but you may experiment.

Some lines to note: 171, 188-89. Here we concatenate the names of both the source and destination warehouses into one field, because it is an Inventory Transfer. You need to join the Warehouses table twice, matching on the different Warehouse ID’s each time. Also, the Type column is important because it allows us to easily filter the Summary reports.

Want to learn even more?
Sign up for one of our Zoho courses!

After the Query Table is Built

Now, you have a list of ALL transactions that affect inventory in any way and (if your business keeps its records clean) the Item Name, SKU, Date, etc. for each one! On top of this report, you can build Summary Reports, one for each kind of transaction. Filter them using the Type column. Then, throw all 8 of those summary reports into a Dashboard and you can then select User Filters that filter all 8 reports at once, because they come from the same base table.

Another neat feature is the ability to create custom links in Zoho Books. For example, you can create a dynamic link, attached to the items module, that opens up a view to this Analytics dashboard and pre-filters it by that item’s SKU. Lots of possiblities here.

SQL Script

				
					SELECT
/* This report aggregates all the Invoice, Package items, Bill items, purchase received items, vendor and credit note items, and transfer 
and inventory adjustment line items. Using these 8 reports, all aggregated into one, you can then create separate Summary Reports for each 
type of transaction, put them all in one dashboard, and filter them all using only ONE user filter.  */
		 ia."Order Number" AS "Transaction #",
		 w."Warehouse Name" AS "Source : Destination Warehouse",
		 u."UserName" as 'Salesperson/Created By',
		 '' as 'Vendor',
		 '' as 'Customer',
		 ia."Reason" AS 'Status/Reason',
		 a."Quantity Adjusted" AS 'Quantity',
		 i."Item Name" as 'Item Name',
		 i."SKU" AS 'SKU',
		 a."Created Time" AS 'Date Created',
		 concat('https://inventory.zoho.com/app#/inventory/adjustments/', ia."Inventory Adjustment ID") as 'Original Record',
		 '' as 'Sales Order #',
		 '' as 'Sales Order',
     /* The Sales order columns are optional. Some companies may want them. They can easily be excluded in the summary tables though. */
		 'Inventory Adjustment' as 'Type'
     /* This column is separate for each of the 8 reports and allows for easy filtering */
FROM  "Inventory Adjustment Items" a
JOIN "Inventory Adjustments" ia ON a."Inventory Adjustment ID"  = ia."Inventory Adjustment ID" 
LEFT JOIN "Warehouses" w ON a."Warehouse ID"  = w."Warehouse ID" 
LEFT JOIN "Items" i ON i."Item ID"  = a."Product ID" 
LEFT JOIN "Users" u ON u."User ID"  = ia."Created By"  
UNION ALL
SELECT
/* The initial order of the columns does not matter so much. You may decide how they appear in the Summary table. But, subsequent SELECT commands in
this report need to have the same order of columns as the first one */
		 II."Invoice Number" AS 'Transaction #',
		 III."Warehouse Name" AS "Source : Destination Warehouse",
		 IIII."UserName" AS 'Salesperson/Created By',
		 '' as 'Vendor',
		 c."Customer Name" as 'Customer',
		 II."Status" AS 'Status/Reason',
		 I."Quantity" AS 'Quantity',
		 I."Item Name" AS 'Item Name',
		 IIIII."SKU" AS 'SKU',
		 I."Created Time" AS 'Date Created',
		 concat('https://books.zoho.com/app#/invoices/', I."Invoice ID") as 'Original Record',
		 s."Sales Order#" as 'Sales Order #',
		 concat('https://books.zoho.com/app#/salesorders/', so."Sales order ID") as 'Sales Order',
		 'Invoice Item' as 'Type'
FROM  "Invoice Items" I
/* The LEFT JOIN ensures that every row in the parent table appears even if there is no match on the 'right side'. This produces somewhat useless, empty data,
but at least we can see which records need to be completed. */
JOIN "Invoices" II ON II."Invoice ID"  = I."Invoice ID" 
LEFT JOIN "Warehouses" III ON III."Warehouse ID"  = I."Warehouse ID" 
LEFT JOIN "Users" IIII ON II."Salesperson"  = IIII."User ID" 
LEFT JOIN "Items" IIIII ON IIIII."Item ID"  = I."Product ID" 
LEFT JOIN "Customers" c ON c."Customer ID"  = II."Customer ID" 
LEFT JOIN "Sales Order Items" so ON so."Item ID"  = I."SO ItemID" 
LEFT JOIN "Sales Orders" s ON s."Sales order ID"  = so."Sales order ID"  
UNION ALL
 SELECT
		 II."Packing Number" as 'Transaction #',
		 IIII."Warehouse Name" AS "Source : Destination Warehouse",
		 u."UserName" as 'Salesperson/Created By',
		 '' as 'Vendor',
		 c."Customer Name" as 'Customer',
		 V."Status" AS 'Status/Reason',
		 I."Quantity Packed" AS 'Quantity',
		 I."Item Name" AS 'Item Name',
		 VI."SKU" AS 'SKU',
		 II."Packing Date" AS 'Date Created',
		 concat('https://books.zoho.com/app#/packages/', I."Package ID") as 'Original Record',
		 so."Sales Order#" as 'Sales Order #',
		 concat('https://books.zoho.com/app#/salesorders/', s."Sales order ID") as 'Sales Order',
		 'Package Item' as 'Type'
FROM  "Package Items" I
LEFT JOIN "Packages" II ON I."Package ID"  = II."Package ID" 
LEFT JOIN "Sales Order Items" s ON s."Item ID"  = I."SO ItemID" 
LEFT JOIN "Sales Orders" so ON so."Sales order ID"  = s."Sales order ID" 
LEFT JOIN "Warehouses" IIII ON IIII."Warehouse ID"  = s."Warehouse ID" 
LEFT JOIN "Shipment Order" V ON V."Shipment ID"  = II."Shipment ID" 
LEFT JOIN "Items" VI ON VI."Item ID"  = I."Product ID" 
LEFT JOIN "Customers" c ON c."Customer ID"  = so."Customer ID" 
LEFT JOIN "Users" u ON u."User ID"  = so."Salesperson"  
UNION ALL
 SELECT
		 po."Reference number" AS 'Transaction #',
		 III."Warehouse Name" AS "Source : Destination Warehouse",
		 u."UserName" as 'Salesperson/Created By',
		 v."Vendor Name" as 'Vendor',
		 '' as 'Customer',
		 'Received' as 'Status/Reason',
		 I."Quantity Received" AS "Quantity",
		 II."Item Name" AS "Item Name",
		 IIII."SKU" AS "SKU",
		 I."Created Time" AS 'Date Created',
		 concat('https://books.zoho.com/app#/purchaseorders/', II."Purchase Order ID") as 'Original Record',
		 '' as 'Sales Order #',
		 '' as 'Sales Order',
		 'Purchase Received Item' as 'Type'
FROM  "Purchase Receive Items" I
LEFT JOIN "Purchase Order Items" II ON I."Purchase Order Item ID"  = II."Item ID" 
LEFT JOIN "Warehouses" III ON III."Warehouse ID"  = II."Warehouse ID" 
LEFT JOIN "Items" IIII ON II."Product ID"  = IIII."Item ID" 
LEFT JOIN "Purchase Orders" po ON po."Purchase Order ID"  = II."Purchase Order ID" 
LEFT JOIN "Users" u ON u."User ID"  = po."Created By" 
LEFT JOIN "Vendors" v ON v."Vendor ID"  = po."Vendor ID"  
UNION ALL
 SELECT
		 II."Bill Number" AS 'Transaction #',
		 III."Warehouse Name" AS "Source : Destination Warehouse",
		 u."UserName" as 'Salesperson/Created By',
		 v."Vendor Name" as 'Vendor',
		 '' as 'Customer',
		 II."Bill Status" as 'Status/Reason',
		 I."Quantity" AS "Quantity",
		 I."Item Name" AS "Item Name",
		 IIIII."SKU" AS "SKU",
		 II."Bill Date" AS 'Date Created',
		 concat('https://books.zoho.com/app#/bills/', I."Bill ID") as 'Original Record',
		 '' as 'Sales Order #',
		 '' as 'Sales Order',
		 'Bill Item' as 'Type'
FROM  "Bill Item" I
LEFT JOIN "Bills" II ON II."Bill ID"  = I."Bill ID" 
LEFT JOIN "Warehouses" III ON III."Warehouse ID"  = I."Warehouse ID" 
LEFT JOIN "Vendors" v ON II."Vendor ID"  = v."Vendor ID" 
LEFT JOIN "Items" IIIII ON IIIII."Item ID"  = I."Product ID" 
LEFT JOIN "Users" u ON u."User ID"  = II."Created By"  
UNION ALL
 SELECT
		 c."Credit Note Number" as 'Transaction #',
		 w."Warehouse Name" AS "Source : Destination Warehouse",
		 u."UserName" as 'Salesperson/Created By',
		 '' as 'Vendor',
		 cus."Customer Name" as 'Customer',
		 c."Credit Note Status" as 'Status/Reason',
		 cn."Quantity" AS "Quantity",
		 i."Item Name" AS "Item Name",
		 i."SKU" AS "SKU",
		 c."Created Time" AS 'Date Created',
		 concat('https://books.zoho.com/app#/creditnotes/', cn."CreditNotes ID") as 'Original Record',
		 '' as 'Sales Order #',
		 '' as 'Sales Order',
		 'Credit Note Item' as 'Type'
FROM  "Credit Note Items" cn
JOIN "Credit Notes" c ON c."CreditNotes ID"  = cn."CreditNotes ID" 
LEFT JOIN "Warehouses" w ON w."Warehouse ID"  = cn."Warehouse ID" 
LEFT JOIN "Items" i ON i."Item ID"  = cn."Product ID" 
LEFT JOIN "Users" u ON u."User ID"  = c."Created By" 
LEFT JOIN "Customers" cus ON cus."Customer ID"  = c."Customer ID"  
UNION ALL
 SELECT
		 v."Vendor Credit Number" as 'Transaction #',
		 w."Warehouse Name" AS "Source : Destination Warehouse",
		 u."UserName" as 'Salesperson/Created By',
		 ven."Vendor Name" as 'Vendor',
		 '' as 'Customer',
		 v."Vendor Credit Status" as 'Status/Reason',
		 vci."Quantity" AS "Quantity",
		 i."Item Name" AS "Item Name",
		 i."SKU" AS "SKU",
		 v."Created Time" AS 'Date Created',
		 concat('https://books.zoho.com/app#/vendorcredits/', v."Vendor Credit ID") as 'Original Record',
		 '' as 'Sales Order #',
		 '' as 'Sales Order',
		 'Vendor Credit Item' as 'Type'
FROM  "Vendor Credit Items" vci
JOIN "Vendor Credits" v ON v."Vendor Credit ID"  = vci."Vendor Credit ID" 
LEFT JOIN "Warehouses" w ON w."Warehouse ID"  = vci."Warehouse ID" 
LEFT JOIN "Items" i ON i."Item ID"  = vci."Product ID" 
LEFT JOIN "Users" u ON u."User ID"  = v."Created By" 
LEFT JOIN "Vendors" ven ON ven."Vendor ID"  = v."Vendor ID"  
UNION ALL
 SELECT
		 o."Order Number" as 'Transaction #',
     /* We concatenate two SELECT commands into one column in order to see bouth the source and destination warehouse for the transaction. */
		 concat(w."Warehouse Name", ' : ', ware."Warehouse Name") AS "Source : Destination Warehouse",
		 u."UserName" as 'Salesperson/Created By',
		 '' as 'Vendor',
		 '' as 'Customer',
		 o."Status" as 'Status/Reason',
		 t."Transferred Quantity" AS "Quantity",
		 i."Item Name" AS "Item Name",
		 i."SKU" AS "SKU",
		 t."Created Time" AS 'Date Created',
		 concat('https://books.zoho.com/app#/inventory/transferorders/', o."Transfer Order ID") as 'Original Record',
		 '' as 'Sales Order #',
		 '' as 'Sales Order',
		 'Transfer Order Item' as 'Type'
FROM  "Transfer Order Items" t
LEFT JOIN "Transfer Order" o ON o."Transfer Order ID"  = t."Transfer Order ID" 
/* We join the Warehouses table to this report twice because we need both the source and destination warehouse */
LEFT JOIN "Warehouses" w ON w."Warehouse ID"  = o."Destination Warehouse" 
LEFT JOIN "Warehouses" ware ON ware."Warehouse ID"  = o."Source Warehouse" 
LEFT JOIN "Items" i ON i."Item ID"  = t."Product ID" 
LEFT JOIN "Users" u ON u."User ID"  = o."Created By"
Click here to copy these scripts. For more information on Zoho Analytics, read our blog post on Analytics integrations, as well as our Zoho Analytics vs Tableau comparison. And, for more Zoho-wizardry, check out our GitHub page.

Want to learn how to create Invoice and collect payment from Zoho Creator?

Learn more

Or learn how complex assignment rules config Via Zoho CRM modules works?

Learn more

Want this functionality, but don't want to do it yourself?

Click here to speak to us!
Contact Us

Need help with your Zoho system?

Hire a full time Zoho System Admin from our poolof highly qualified program graduates!
Schedule a call

Revenue Operations Experts

© 2024 - The Workflow Academy

Envelope Facebook Github Linkedin
close
Login