Skip to main content
Solved

Rolling Inventory Graph - Historical

T
Freshman I
Forum|alt.badge.img
  • tveld
  • Freshman I
  • 41 replies

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

Best answer by bryanb39

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.  

 

View original
M
1 person likes this
  • M
    maichinger
Did this topic help you find an answer to your question?

3 replies

T
Freshman I
Forum|alt.badge.img
  • tveld
  • Author
  • Freshman I
  • 41 replies
  • January 22, 2024
tveld wrote:

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
Pro II
Forum|alt.badge.img+6
  • bryanb39
  • Pro II
  • 177 replies
  • Answer
  • January 25, 2024

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
M
Chris Hackett
2 people like this
  • M
    maichinger
  • Chris Hackett
    Chris Hackett
J
Freshman I
Forum|alt.badge.img
  • josborn
  • Freshman I
  • 32 replies
  • July 2, 2024

Which table would I add to the GI for the GL Balance?

Reply


Most helpful members this week

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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