Ask Your Question

Form & Report: Does LO Base 6.4 - Firebird 3.0 accommodate extracting data from a directly-run-SQL-command Query with Firebird Window Function?

asked 2020-02-17 17:25:15 +0100

LO_Fans gravatar image

updated 2020-07-14 16:23:20 +0100

Alex Kemp gravatar image

Hi, I'm a newbie in LO Base and have just migrate from HSQLDB 1.8 to Firebird 3.0.

I'm trying to make a running total column in a LO Base 6.4 query with Firebird 3.0's Window Function, i.e. SUM ("Value") OVER (Order by "Date", "ID") as "RunningTotal"

However, Firebird 3.0's Window Function only works under the mode Run SQL Command Directly. Ratslinger told me this in my previous question (I'm actually wondering why LO Base 6.4 doesn't accommodate Firebird's 3.0 Window Functions).

So it works charmingly with super fast processing time. However, as I created report from the Query, the "RunningTotal" just dissappear.

image description

Does anybody have an idea what went wrong?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2020-02-17 20:13:36 +0100

Ratslinger gravatar image


There are some open issues regarding the use of aliases in forms and reports (here is one tdf#124408).

If possible create query without any aliases and it will work in both form and report. If not possible, then it will work if you use the query to create a view and then used the view instead.

In testing without a query alias in a report, the windowing function seems to be a new problem. Again this is overcome by using a view instead.

image description

Additionally, you do not need the window function to generate a running total in Report Builder. For that see example in this post -> How to reuse an accumulated value in a Report?

edit flag offensive delete link more


Again, it works like a charm. Thank you Ratslinger.

After eliminating the aliases and using the generated names (i.e "CASE" and "SUM"), the report fields shown correctly.

Regarding the window function, I need it to work on accounting journals. The data in field Value have to be defined as positive or negative before summed cumulatively. I don't know how to do this only with the report's accumulated value function.

LO_Fans gravatar imageLO_Fans ( 2020-02-18 04:04:58 +0100 )edit

I don't know how to do this only with the report's accumulated value function.

As most don't, this is why I placed the link in the answer which explains how this is done.

Ratslinger gravatar imageRatslinger ( 2020-02-18 05:04:15 +0100 )edit


Please do not close questions as other may have additional information.

Instead, please help others to know the question has been answered by clicking on the ✔ in upper left area of answer which satisfied the question.

Ratslinger gravatar imageRatslinger ( 2020-02-24 01:24:25 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-02-17 17:25:15 +0100

Seen: 66 times

Last updated: Feb 17 '20