Share a little trick to show the begin and end inventory balance of a given period

Userlevel 5
Badge +2


      I get a lot of help from other community users. Now,  I have found a trick to fulfill 1 requirement, and I think this trick might help others someday. So I’d like to share it out.
      Note:  if someone has a better way to do it, please let us know.

The requirement is:

          To display the historic Inventory balance of a given period, could be a week, half-month, or any date frame.


      Acutally,Acumatica has recorded the beg and end balance of each item on its transaction day.

But only the first line’s begin qty and last line’s end qty are need to be displayed according to my requirement.

          I have used report designer to fulfill it. below are the steps


1, Create a rpx file, and create 3 variables in detail section.

The key here is assign the begin qty only when the linenbr=1

=IIf( $LineNbr=1, [INItemSiteHistDay.BegQty],$BegQty )

And EndQty= [INItemSiteHistDay.EndQty]

So it would finally get the last line’s endqty

2, Make the detail section visible=false
and make the value be appeared on footer section

The results are like below:




2 replies

Userlevel 3
Badge +1

Thanks for sharing this. @Ryan Brown and I recently started the #AcumaticaTnT segment (click here) on to cover tips and tricks like this.


I’m curious, with your solution, does it only work with Items that have a transaction within the date range? In other words, if there was a transaction prior to the date range that left the Item with an ending balance of, say, 10, would the 10 get picked up and displayed on your report or would it be empty?

Userlevel 5
Badge +2

@TimRodman Hello Tim, you are absolutely right. My solution is under the consumption that the report items are frequently sell item, that they would have transaction every day. Or at least every week.

So, the scenario you mentioned would be empty, the report can not find the ending balance of items without transactions in the date range out.

If I have to show these items. What I can think of right now is to use report and subreport.
The main report would be this existed one right now.
Then I would use another sub report to specifically find the items without transactions.  It would be 2 steps, the first step is to find the latest transaction date prior to the date range, then to find the balance of that latest transaction date.


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 — 2020  Acumatica, Inc. All rights reserved