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

[LO_Fans]

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.

Does anybody have an idea what went wrong?

[Ratslinger]

Hello,

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.

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?

[LO_Fans]

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.

[Ratslinger]

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]

Hello,

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.