In order to allow me to focus on Consulting (click here) and Courses (click here), I continue to review all posts here in the Forums, but, in general, I'm only personally responding to posts that I mark as #AcumaticaTnT (click here). For Questions (click here), others may respond, or you can post over at Community.Acumatica.com (click here).
Questions Running Total
Anyone know if there is an easy way to do a running total in report designer? It is such a common request, I'm kind of hoping that there is. I currently get around it by implementing things such as the below code snippet in custom views and passing it to the report designer. However, using views are slow in some circumstances and its easier to just bring the tables together.
tt.JobValue - sum(tt.FundedAmount) OVER (PARTITION BY tt.JPMBillingRuleID) ->Too easy with standard SQL.
I want to avoid the hard way which involves over complicated table joins, that turn into a mess the more running totals you implement. I have one report for example with 8 distinct running totals that occur over a couple different tables over a couple different partitions. Translating that into Joins would create one nasty looking query.
This would be a perfect situation for a Variable. Just keep adding the Variable to itself.
Take a look at the out-of-the-box Invoice form. There is an example of a formula in detailSection1 that I think is what you are looking for:
=Assign( '$LineNbr', $LineNbr + 1)
I think I figured this out. It seemed a bit awkward, I managed to find a single stack overflow response that indicated the process. It appears that you have to create the variable in a header section. Then you create another variable with the same name in the detail section. I see the assign function in your screenshot and will also look into simply using that. If that works the way I hope, will be much better then creating two variables every time you need one. I also noticed it seemed that you can not use variables in charts that exist in the group footer. But, maybe use of the assign function will fix that.