Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

How to create a running total in Base for a Report

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.