Hi, I’m reaching out for some help. I’m working with a client who needs a report showing the historical on-hand quantities of items. Specifically, they need the on-hand quantity for today, 7 days ago, 30 days ago, and 100 days ago. Could you help me create a saved search that will provide this data so I can generate the report for them? Or is there any other way to get the required data?

Hi Vinay, I do have a SuiteQL query that does that if it helps: It has 3 parameters, itemId , this returns just one item but you can remove locations, a array with location ids if you want a set of locations stockDate , the day that you wanna the on hand if you remove the first 2 parameters, it will give you a report for all items and locations. “SELECT transactionline.item, transactionline.location, sum(transactionline.quantity), sum(TransactionAccountingLine.amount) from transaction, transactionline, Location, TransactionAccountingLine where transactionline.item = ” + itemId + ” and transactionline.transaction = transaction.id and transactionline.isinventoryaffecting = ‘T’ and transaction.posting = ‘T’ and transaction.trandate <= ‘“+stockDate+“’ and transactionLine.location = Location.id and Location.location.id in (“+locations.join(‘,’)+“) and transactionLine.id = TransactionAccountingLine.transactionline and transactionLine.transaction = TransactionAccountingLine.transaction group by transactionline.item, transactionline.location”

Thank you Luiz!