Ask Your Question
0

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

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

LO_Fans 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 reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by LO_Fans
close date 2020-03-02 03:48:15.199698

1 Answer

Sort by » oldest newest most voted
0

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

Ratslinger gravatar image

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.

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

Comments

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 +0200 )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 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2020-02-24 01:24:25 +0200 )edit

Question Tools

1 follower

Stats

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

Seen: 31 times

Last updated: Feb 17