Subject Re: [IBO] Running total column
Author Helen Borrie
At 01:52 AM 9/02/2005 +0000, you wrote:

>I use the TIBO* components with 3rd party DB aware controls. I need to
>display accounting data in a grid, which is more or less trivial except...
>I need to display a column of the running total - that is, for each
>row the running total = (running total of previous row) + current row.
>For example:
># Date Desc. Amount RunTot
>1 1/1 Blah 100 100
>2 2/2 Ding 200 300
>3 3/3 Dong 300 600
>4 4/4 Oops -400 200
>The problem is that I can't use a calculated column, because for each
>row the calculation event will need to access data from all previous
>rows. This results in the current row changing during the event (which
>wreaks havoc on connected controls), and very slow when there are
>several hundred rows.
>Another possible solution is to include a dummy column in the query,
>which creates another column, and then calculate the running totals
>once and store them in that column. However this marks all the rows
>for update, altough nothing really has changed in the underlying DB data.
>What I need is a "virtual column" that I can populate with the values
>I want, without disturbing the update status of the rows.
>Is there any such thing?

Well, you could do this by tacking on a stringgrid and maintaining its Rows
property in sync with the dataset - usually pretty ugly.

A more useful way is to write a selectable stored procedure that outputs
the running total as an actual output field. If you have The Firebird
Book, you can find an example on pp. 625-627.

Take care to write such a SP so that the search parameters you would pass
in a WHERE clause to a regular DSQL query are received as input arguments
to the SP call.