By using this website, you agree to our Terms of Use (click here)
We are using a default report in Acumatica called Historical Inventory Balance. We are wanting to know if there is a way to get the report to show the left column all Inventory ID's and across the top the weeks or periods with the beginning quantity for each Inventory ID. It is basically just transposing the report. If there is a way to get the beginning quantity in a GI would also be good and then we can create a report from there. We do know that you can use a pivot table to get a GI to look the way we want. Also, we know that the beginning quantity is a DAC on the Inventory Transaction History.
Question: Are we able to create a GI and/or report that shows all the Inventory ID's by week or period showing their beginning quantity?
Example of how we want the Report/GI to look like:
It could probably be done, but it would take some work. The tricky thing with that report is that there are two joins to INItemSiteHist. I think it's because INItemSiteHist doesn't have a record if there is no activity in a financial period.
Tim, that would be consistent with how Acumatica implements GL balance tracking in GL.GLHISTORY.
In order to get a full trial balance, you need to link to the history table twice; once to find a current record and once to find the most recent historical record. If there is no activity in the GL for a particular period, there will not be a row in the GLHISTORY table. Therefore, you have to find the most recent period with activity via GLHistoryByPeriod.LastActivityPeriod.
Agreed. Same approach.
Getting the inventory data isn't hard. But I would like to know how you plan to get dynamic columns (pivot table) in a GI. Columns are static in a standard GI.
Ya, I think you'd need to put a Pivot Table on top of the Generic Inquiry.