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

  • 14 May 2021
  • 4 replies
  • 645 views
R
Rank
Userlevel 6
Badge +5

Hello,

      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.

Solution：

      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:

 

 

 

4 replies

TimRodman
Rank
Userlevel 6
Badge +1

Thanks for sharing this. @Ryan Brown and I recently started the #AcumaticaTnT segment (click here) on AUGForums.com/Live 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?

Acumatica Podcast: AugForums.com/Podcast ⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡ Video Training Course on Acumatica Generic Inquiries: AugForums.com/Course ⚡⚡⚡⚡⚡⚡ Independent Consulting, focused on Reporting in Acumatica: AugForums.com/Consulting
R
Rank
Userlevel 6
Badge +5

@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.

H
Rank
Userlevel 4
Badge

@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.

Hi @ray20 , can you share more detail about how to get the latest transaction date prior to the date range in report designer?

R
Rank
Userlevel 6
Badge +5

I am sorry, just noticed your msg.  My report designed before is only to search 1 item at a time.
For you case, you probably can do it below:
1, Use @item, @dateto,@datefrom as parameters,
2,  Main report as shown above.
3, Design a sub report, using the below logic:
select top 1 * from [INItemSiteHistDay] where trandate<@dateto and itemid=@item
Make sub report only visible when main report retrieval empty.
You can set a variable in main report, like @lineNbr, if lineNbr<1, means no lines be found, then make sub report be visible.
sub report will show the end balance of item you searched before the date range.
 

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