Solved

Rolling Inventory Graph - Historical

  • 10 January 2024
  • 2 replies
  • 79 views
T
Rank
Userlevel 4
Badge

Hello Community,

I have a graph that I need to create that will show 6 or so months at a time of inventory value.  I attempted to create a GI similar to the Historical Inventory Valuation and it is not working how I had hoped.  My GI seems to be working for the most part, but the ending balances do not match those on that report writer form.  Which has a few formulas mixed with different aliases to create the ending balance. 

 

Any suggestions of a report that I could use to create this graph with?  

Thank you,

Trisha

icon

Best answer by bryanb39 26 January 2024, 00:38

View original

2 replies

T
Rank
Userlevel 4
Badge

Hello Community,

I have a graph that I need to create that will show 6 or so months at a time of inventory value.  I attempted to create a GI similar to the Historical Inventory Valuation and it is not working how I had hoped.  My GI seems to be working for the most part, but the ending balances do not match those on that report writer form.  Which has a few formulas mixed with different aliases to create the ending balance. 

 

Any suggestions of a report that I could use to create this graph with?  

Thank you,

Trisha

 

Community,

Does anyone have any ideas on this request?

Thank you,

Trisha

bryanb39
Rank
Userlevel 6
Badge +5

Do you have any in transit inventory? That standard report does not report in transit, that can be millions for us.  

The simplest GI would be your Inventory GL Balance.

Next simplest with some functionality for inventory item, class, warehouse etc would be summing up the INTranCost table and grouping by Period.

The key to get the balance for each period is to create a self join. Add INTranCost twice, give them Aliases Current and History. 

Join all the key fields, DocType, RefNbr, LineNbr, CostID, CostDocType, CostRefNbr

Then add a row in the relationship for Current.FinPeriodID > History.FinPeriodID of the child.  

Sum up TranCost * InvtMult of history. 

Group by Current.FinPeriodID. 

This can take a while to run, we do this from SQL since we have 5 tenants with millions of rows.  But just depends on how much data.  

 

https://www.fiverr.com/acudatica

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved