Rollup in the Libreoffice Base parser

[manolis1]

I have the following query, that works when send directly to MariaDB, but not when send via the parser.

SELECT `ANK`.`company`, `ANK`.`journal`,`ANK`.`pb`, `ANK`.`db`, `DEUTERO`.`dbdesc`, SUM( CASE WHEN `ANK`.`year` = 2024 THEN `ANK`.`dc` END ) AS `T`, SUM( CASE WHEN `ANK`.`year` = 2024 - 1 THEN `ANK`.`dc` END ) AS `T_prev`, SUM( CASE WHEN `ANK`.`year` = 2024 THEN `ANK`.`dc` END ) - SUM( CASE WHEN `ANK`.`year` = 2024 - 1 THEN `ANK`.`dc` END ) AS `Diff`, ( SUM( CASE WHEN `ANK`.`year` = 2024 THEN `ANK`.`dc` END ) / NULLIF ( SUM( CASE WHEN `ANK`.`year` = 2024 - 1 THEN `ANK`.`dc` END ), 0 ) - 1 ) AS `Percent` FROM `ANK` LEFT JOIN `DEUTERO` ON `ANK`.`db` = `DEUTERO`.`db` WHERE `ANK`.`year` >= 2024 - 1 AND `company` = 'VD' AND `ANK`.`omada` = '6' AND `ANK`.`month` <= 12 AND `ANK`.`journal` = 'ΓΗ' GROUP BY `ANK`.`pb`, `ANK`.`db` WITH ROLLUP

I would like the years to be parameters, but as the ROLLUP function does not seem to be understood by the parser, what are my alternatives?

Libreoffice 25.2.5.2, MariaDB 12, Linux 6.14

[CRDF]

I think you could try with a View.

[manolis1]

Could you elaborate a bit?

[CRDF]

Sorry!
Forget it.

[Villeroy]

If this is for a report, the report can do the rollup.
If this is for a form, a “power filter” with a calculated subform can do.
When importing to a spreadsheet, the job is trivial.

[manolis1]

• It is supposed to be drawn into a libreoffice writer file, were the user is supposed to comment on deviations.
• The writer file itself is generated by egroupware and contains already some fields from the egroupware database as well as instructions.
• I prefer to avoid using the spreadsheet as an intermediate step (this is what users do now)
• The report builder seems interesting, though it would probably not be the solution in the case at hand.

[Villeroy]

@manolis1

I prefer to avoid using the spreadsheet as an intermediate step

I was thinking of a spreadsheet as the final step. I use Calc as reporting engine with database ranges that are linked to Base record sets.

@manolis1

(this is what users do now)

Let me guess: they actually store record sets in spreadsheets, which is a no-go indeed.

@manolis1

The report builder seems interesting, though it would probably not be the solution in the case at hand.

Basically, it produces a Writer document with a tabular structure.