Running Balance in Grid

Applications Grid / Reports
#1

Can anyone recommend how to perform a running balance in a Grid application as a calculated field?

#2

It’s the sum of a column? And the column is a calculated field? (sorry if this is a stupid question). Within a row you can perform a script to calculate a new field by using a onrecord event. This will trigger for each and every column. This calculated column can be summed by using the summary and totals secion left in the menu.

#3

What I would like to achieve is the following

Name, Paid, Running Balance
A, 10, 10 (10+0 since first row)
B, 20, 30 (10+20)
C, 50, 80 (30+50)

#4

[QUOTE=bengrech;20543]What I would like to achieve is the following

Name, Paid, Running Balance
A, 10, 10 (10+0 since first row)
B, 20, 30 (10+20)
C, 50, 80 (30+50)[/QUOTE]

Create a new field RB. In the onscriptinit set a global variable [glob_old]=0;. In the onrecord event: [glob_old]=[glob_old]+{Paid}; {RB} = [glob_old];

That should do it.

#5

Hi Albert,

Your idea is good but It calculate balance only for one page. It is not give running balance for all pages. If you put the [global_old]=0; in onApplicationinit instead of onScriptinit.it will calculate running sum for all pages. But this time the problem is the changing pages(prev, next navigation). It will calculate and add running total again and again without stopping. Therefore It gives wrong runnging balance.

I am thinking that how this will be solved ?
Thank you.

#6

Hi,
you have to solve this with sql.
Go to the SQL section, in the SQL Preparation field type: @bal = 0
Add the following line to your SQL statement (field selection): @bal := @bal + paid as balance

That’s it.

jsb

#7

Many thanks jsbinca will give it a try!

#8

Hi jsbinca,

Yes, your code works and calculate running balance total for only each one page(only current shown page). It does not caltulate running balance total from first page to end page.

Thank you.

#9

Hi,
are you doing anything else with your data?
I have a grid with appr. 200 pages (50 records a page) and it is calculated from the first to the last page.
Try adding a ORDER BY balance to your SQL-Statement.

jsb

#10

Hi jsbinca and Albert,

Yes, It is working now properly after adding ORDER BY.

Thank you both of you again.