GSOC ReportBuilder Weekly Update

[Adam Seskunas]

Hello all,

This past week I finished adding the capability to add database data to a
report. You can now add a database Field and it will be displayed in a
Report. The patch is meant to be a proof of concept and as such, I have
commented out the code that writes headers, footers and some other features
of a report.

https://gerrit.libreoffice.org/c/core/+/185810

As mentioned in last weeks email, I had been reading some documentation
about the JFreeReport library in order to understand how the database data
gets added to a report. During my research for this project, one major
question that I had was what exactly the JFreeReport library(now called
Pentaho) was responsible for in generating a report and why it had been
used.

Leading up to the project and then during these beginning stages I have
spent a considerable amount of time debugging and reading source code in
order to figure out how a report is generated and I still wasn't clear on
exactly what functionality the JFreeReport was adding to a report.

In any case, when reading the JFreeReport documentation, it was apparent
that two features of the library were the main selling points for using it.
These features are the ability to group data in a report based on
characteristics of that row of data, similar to how a SQL statement would
group data. And the second selling point was the use of formulas in a
report.

The use of formulas is given a lot of attention in the base documentation,
towards the end of the document.
https://documentation.libreoffice.org/assets/Uploads/Documentation/en/BG7.2/BG72-BaseGuide.pdf

After talking it over with my mentor Michael we decided I had best look
into how formulas and grouping are implemented in JFreeReport, with the
idea that the JFreeReport library could be doing some heavy lifter here and
that replacing this functionality could be difficult, and the sooner this
is known, the better.

Formulas in a report are in OpenFormula format and can be used on data from
the database, standalone(such as a function like TODAY()) or even custom
functions using the OpenFormula format.

Greping around the code in
https://opengrok.libreoffice.org/xref/core/reportbuilder/java/org/libreoffice/report/pentaho/

Led me to the implementation of the formulas which are located in
workdir/UnpackedTarball/jfree_report/libFormula

which has the following README.txt

    LibFormula provides Excel-Style-Expressions. The implementation provided
    here is very generic and can be used in any application that needs to
    compute formulas.

    The syntax and expressions ar based on the OpenFormula-specifications,
but
    with carefully pruning away all the spreadsheet specific parts (like
    references to cell-ranges).

This was news that I wasn't hoping to see. It turns out that the
JFreeReport library has lots of code that not only contains all of the
actual OpenFormula functions(SIN(), TODAY(), etc) but also a parser used to
add database information to functions and a compiler to execute functions.
It seems that in the case of formulas, JFreeReport is indeed doing some
heavy lifting.

I'm aware that there are OpenFormula functions that are used in calc and
that calc has a parser and compiler as well. I see a few issues with using
the calc code with reportbuilder

1. The parser will have to adapted to be able to handle database
information. This issue should be solvable, there is existing java code
that does this that could either be ported to C++ or called from the C++
code.

2. Offering the correct functions. The documentation for Base specifically
mentions that there are a smaller number of functions available when you
export a report to a spreadsheet, and looking through the functions in
workdir/UnpackedTarball/jfreereport_libformula/source/org/pentaho/reporting/libraries/formula/function
A quick count gives 295 functions with 83 in math alone. So determining
which functions aren't available in calc and then implementing them in C++
could be quite a bit of work.

So for now, I will continue to look into the possibility of reusing the
calc code. I will also look into how JFreeReport implements the grouping
functionality. My new short term goal will be to produce some documentation
with the hope that it can inform the decision on how to proceed with the
project moving forward.

Cheers,
Adam
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice/attachments/20250619/83b46239/attachment.htm>