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?

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

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 oINItemSiteHistDay] 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.
 

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= tINItemSiteHistDay.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:

 

 

 

Hi @ray20, Can you share your GI if you don’t mind?