Runing total in browse column

[AHF]

Hi,

How can I put a runing total column in a browse ?

Regards
Antonio[/u]

[nageswaragunupudi]

Only reliable way is to prepare the running totals in advance before starting the browse. If the data is in an array, I add an extra column and work out the running balances first. We can also have a separate array of running totals. If the data is DBF or Recordset I create a two dimensional array of { { RecNo(), nRunningTot }, ... } and use it in the browse. ( Personally I prefer use of Hashes to arrays in this case for reasons for performance and that is what I am doing in my recent programs ).

If we are using Oracle, we are in for luck. We can make the sql query to return a recordset with running totals calculated by Oracle itself. ( Example : SELECT TRNDATE, AMOUNT, SUM( AMOUNT ) OVER ( ORDER BY TRNDATE ) AS RUNNINGTOTAL FROM ..........)

Any attempt to do it in a generic way using browse's skip block can only be for academic fun ( I can do it and works for handful of rows ) but not for practical use. The best, fast and reliable way is to precompute running totals.

[James Bott]

As NageswaraRao says, the problem is performance. It is only feasible with small numbers of records.

If you are allowing the user to edit values in the browse, then you have to recalc the totals after each edit. Instead of counting all the records each time, you can subtract the old value and add back the new value to get the new total. This the way I have done it.

Regards,
James

[nageswaragunupudi]

If you are allowing the user to edit values in the browse, then you have to recalc the totals after each edit. Instead of counting all the records each time, you can subtract the old value and add back the new value to get the new total. This the way I have done it.

Yes. And we should also remember to apply the difference to the edited row and all subsequent rows as well each time the value is edited.

[James Bott]

NageswaraRao,

>Yes. And we should also remember to apply the difference to the edited row and all subsequent rows as well each time the value is edited.

OK, I just noticed that AHF was talking about running totals. I was talking about grand totals. My mistake.

Running totals are even more of a performance issue than grand totals.

James

[Enrico Maria Giordano]

Instead of counting all the records each time, you can subtract the old value and add back the new value to get the new total. This the way I have done it.

James, remember that an application can be used in a network by more than one user and in this situation your technique will fail, if I'm not missing something.

EMG

[AHF]

Thanks to all, I got the idea.

Antonio

[James Bott]

Enrico,

>James, remember that an application can be used in a network by more than one user and in this situation your technique will fail, if I'm not missing something.

Good point. I was using it for editing sales orders and invoices. In this case there was only a single user so it worked fine. These orders and invoices often contained more than a hundred items, so recalculating by retotalling the entire list was too slow. The subtract/add method made it instant.

Multiple users is another matter. I would not even try doing running totals with a list that was available for changing by multiple users.

James

[nageswaragunupudi]

I would rather say, it depends. In most cases where we are required to provide running totals involve financial transactions where modification of the past data is not allowed. Also in a majority of cases, different users will be handling different parts of the data, by virtue of their job allocation and overlaps are rare.

Examples: A salesman would be browsing his invoices. A cashier would be looking at his cashbook.

Even assuming that is permitted in the data we present, we naturally would have built in appropriate features in our design that facilitate quick check if any part of a particular subset of data is modified by other users and our software would alert the user of such modifications and refresh the data.

I would rather say in most, if not all, situations this can be comfortably handled even in heavy multiuser environments.