How to create a running total in Base for a Report

[MaxPixel]

The Question

Given a Base table that contains a DATE column and a DECIMAL column, how would I accomplish a Report that contains a graph that reflects the accumulation of value (from the DECIMAL column) across time (as defined by the DATE column).

What are all of the pieces necessary, and how would they fit together?
If multiple viable approaches exist, what are the pros/cons?

Context

I’m migrating some calculations from a spreadsheet that they outgrew into Base. I need to figure out how to accomplish a “running total” or “running balance” for a graph on a Report.

For clarity, given the values [5, -2, 3], the graph would show something like

6|       o-->
5| o--\  |
4|    |  |
3|    o--/

My findings so far

OO forums: Problem in Per Row Computation - The gist of this thread is that referencing previous/next fields in SQL is extremely advanced, and you’re better off importing the data to a spreadsheet

LO Ask: Can we have calculated fields in LibreOffice Base? - SQL UPDATE and TRIGGER features can be used to make the equivalent of a formula cell

What I haven’t seen is anything about macros. From what I understand, macros/scripting are used to make entire enterprise applications on Access and Base, so I suspect that my best path forward might be to make a macro that is fired whenever the report is opened, and which iterates the table in order to populate a backing table that feeds the chart, though I’m not sure if there is a need for special queries or other elements, or if this could be done in memory so that a backing table is not necessary.

[Ratslinger]

Since you want a chart based upon information you don’t have yet and this data is a cumulative figure, a macro is probably the best way to go.

If you have no experience in writing macros, this is a steep learning curve. Some information can be found in the LO documentation (click here) in Chapter 9 - Macros. Much more detailed information can be found in OOME by Andrew Pitonyak (PDF-click here).

There are two ways to generate a Chart in base - in Report Builder or displayed on a Form. Using a macro, generate this accumulated figure by reading existing data, and for each point required in the chart, save the appropriate information.

If the Chart is to be in Report Builder, as each point is generated, write a record out to a temporary table to be used within Report Builder. If the Chart is to be displayed on a form, the data is to be saved in an array. The completed array is passed to an area where the chart is created. For more information on creating a Chart in a Form, with working samples, see this post - click here.

If this answers your question please click on the (upper left area of answer).

[MaxPixel]

Thanks for confirming that writing macros is the best approach, and for providing those references.

If you’re able to link any of the essential steps like “reading existing data” or “saved in an array” to the recommended relevant documentation, I think that would be a great addition to the answer.

I’ll start looking for likely candidates in the documentation and Pitonyak book, and report back with the finished solution.

[Ratslinger]

@MaxPixel The actual code for “reading existing data” or “saved in an array” is already in the chart sample in the provided link. The specific code needed to read existing data is entirely dependent upon what table is being read. An ‘array’ is just a data structure in Basic.