By using this website, you agree to our Terms of Use (click here)
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.
So I spent a bit of time on this. In most languages, the "=" would be an assignment operator. However, in report designer, it appears to function as a comparison only. In the expression editor $test = $test + [NewInvoiceBurnRate.Cumulative_Billing_EAC_Funding] does not work. So how do you add the variable back to itself to create a running total. Unfortunately, google brings me back my own question as a top result lol.
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)
Hi Tim,
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.