Base Report: Level-break field does not display / get populated

rolandgiesler · July 16, 2025, 2:45pm

I have a single “Subtotal” that I’d like to display on a report whenever the “Name” of a client changes. I have added it in to a group footer. The label for that field shows, but the accumulated total doesn’t.

I have looked quite a few example that people have posted in these forums and I cannot see where I’m going wrong. Some help would be much appreciated.

System:
$ cat /etc/os-release
PRETTY_NAME=“Ubuntu 24.04.2 LTS”
NAME=“Ubuntu”
VERSION_ID=“24.04”
VERSION=“24.04.2 LTS (Noble Numbat)”
VERSION_CODENAME=noble
ID=ubuntu
ID_LIKE=debian
HOME_URL=“https://www.ubuntu.com/”
SUPPORT_URL=“https://help.ubuntu.com/”
BUG_REPORT_URL=“Bugs : Ubuntu”
PRIVACY_POLICY_URL=“Data privacy | Ubuntu and Canonical Legal | Ubuntu”
UBUNTU_CODENAME=noble
LOGO=ubuntu-logo

$ libreoffice25.2 --version
LibreOffice 25.2.4.3 33e196637044ead23f5c3226cde09b47731f7e27

Villeroy · July 16, 2025, 5:27pm

How can we see where you’re going wrong. Here’s another sample (report qReport2):
FilterData_FB.odb (91.6 KB)

rolandgiesler · July 17, 2025, 8:11am

It doesn’t make sense to send the .odb report here since it’s uses a MySQL database so the report doesn’t open for editing. This is why I posted the screenshots.

Villeroy · July 17, 2025, 9:06am

What is the report’s data source? MySQL table, a view, a parsed query or direct SQL query?
Views and direct SQL queries are interpreted by the database backend (“pass-through queries” in MS Access terms).
Direct table access and parsed queries are interpreted by the Base frontend.
Does it make any difference when you switch from one to another, for instance from a parsed query to a direct SQL query?

rolandgiesler · July 17, 2025, 10:25am

The view is necessary because the query contains UNION that the report query parser cannot handle. So I created a view, but the MySQL view is not shown in the list as table, which is why opted for SQL Command. Yes or No for Analyse SQL Command both gives the same non-result.

RobertG · July 17, 2025, 10:43am

Why isn’t the view visible in tables? Here, with MariaDB, all views are visible.

You will need “Analyze SQL command” set to ‘Yes’, because you want to sort the report by grouping. This won’t work with ‘No’.

Data field “Payment” shouldn’t be NULL (empty).

Then I would start the report navigator and would have a look at the function “AccumulationPayment”.

rolandgiesler · July 17, 2025, 10:54am

I have scanned to list again for tables and the view is actually there! So I selected that, have set “Analyse SQL command” to “Yes”.

Are you saying that if these are null values for some of the payment values then the total won’t work?

Villeroy · July 17, 2025, 12:01pm

I confirm that the accumulation feature of the report builder fails with Null values within the figures to be summed up. IMHO, this is a bug.
Workaround for sum, but not for min and max: Coalesce("Value",0)

rolandgiesler · July 17, 2025, 1:05pm

Coalesce(“Value”,0) will work for me. Let my try that.

RobertG · July 17, 2025, 2:43pm

I won’t use much functions in ReportBuilder. I would prefer to calculate in the view of the database.

Villeroy · July 17, 2025, 3:01pm

Sure, but the aggregation of a group written to each group footer should be possible to do in report design. Is this even possible in pure SQL?

RobertG · July 17, 2025, 3:38pm

I’m using correlated subqueries for this like

SELECT "a"."Bill", "a"."Product", "a"."Price", 
(SELECT SUM("Price") FROM "Table" WHERE "Bill" = "a"."Bill") AS "SumBillPrice" 
FROM "Table" AS "a"

With MariaDB/MySQL will also work directly GROUP … WITH ROLLUP: SELECT WITH ROLLUP | MariaDB Documentation