An alternative for Report Builder?

Getting your data onto paper - or the web - Discussing the reports features of Base
Post Reply
User avatar
charlie.it
Volunteer
Posts: 416
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy

An alternative for Report Builder?

Post by charlie.it »

Quite by accident I found and downloaded this extension of LibreOffice: https://extensions.libreoffice.org/exte ... textension.
Although it was published as early as March 2016, I don't found a reviews on this forum nor on other sites. Have I not looked well?
I was the first to put "I like", I was the first to try it?
Still it works well. I tested with a Query Base with three fields and 9520 records. The running time is quite long (150 seconds) compared with about 30 seconds of Report Builder. But for smaller databases the time is reduced significantly (see chart): 4 seconds for 2198 records.
Any of you ever tried it? Thank you.
Attachments
Schermata 01-2457758 alle 14.17.58.png
charlie
Italian AOO Admin
macOS 14 Sonoma M1: Open Office 4.1.15 - LibreOffice 7.5.7.1

http://www.charlieopenoffice.altervista.org
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: An alternative for Report Builder?

Post by Villeroy »

Looks as if someone was inspired by my [Writer] Stand-Alone Database Reports :D
I do not understand the extension's Basic code, but it seems to do exactly the same as my Python code. It reads some config options from custom properties of a Writer template and fills up a prepared table template with 2 rows. First row is for column labels, second row contains formatting without data. The macro copies the second row and fills it with record set data until the end of the record set.
It is a replacement for the old style report wizard but without grouping. The extension code implements some kind of visual grouping leaving out repeating category values (like merged cells). The best thing about it is that you can use it in stand-alone documents like the reports in OpenOffice.org 1.x. The second best feature is that it dumps raw data only, leaving all the formatting up to you with no limits.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
charlie.it
Volunteer
Posts: 416
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy

Re: An alternative for Report Builder?

Post by charlie.it »

I tried your work. It works, but with the same database used by me for the tests described extension, it seems much slower: to load 2138 records takes 25 seconds instead of 4 seconds. With all of the 9520 records, it never ends ...
charlie
Italian AOO Admin
macOS 14 Sonoma M1: Open Office 4.1.15 - LibreOffice 7.5.7.1

http://www.charlieopenoffice.altervista.org
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: An alternative for Report Builder?

Post by Villeroy »

Thank you very much for this feedback. I will study the Basic code to find out why it works better than my simple code.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: An alternative for Report Builder?

Post by Villeroy »

Update on the Basic extension:
It is faster because it dumps strings into the Writer table. My code is slower because it converts non-textual database types into formattable numbers. My code is very slow because it does not take any advantage from object oriented programming. I think I can tweak the performance a little bit.
The screenshot shows a real-world query in the data source window and the resulting Writer table from the extension code.
There seems to be an issue with the first record. The whole table consists of unformattable text values. If you want a nice representation of your values, you've got to build the display strings in SQL. There is no easy way to apply formatting afterwards, particularly when you do not want English number formats.
basic_report_strings.png
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
gogo555
Posts: 2
Joined: Sat Jan 14, 2017 2:22 pm

Re: An alternative for Report Builder?

Post by gogo555 »

Hi @ all

@Villeroy: Sry I didn't know about your extension, otherways I would have mentioned it of course on http://extensions.libreoffice.org/ . Charlie pointed me to this thread.

I'm curious about why these "#NV#" values are in the first line. Could you plz post a copy of the database + the used ott-document?

The reason is, that "#NV#"-Values come from these lines only:

Code: Select all

do while oThisResultset.Next
     ...
     ...
loop

if oThisResultset.Row=0 then ' empty resultset derived - (first+iStartAtRow line) of the table will be filled with empty strings
	...
	For ii=1 To ci
		myTable.getCellByPosition(ii-1, iStartAtRow).setstring("#NV#")
	next ii
end if
The "if" only runs if .row=0 which is as far as I know only possible if there's an empty resultset.
Furthermore, filling of the myTable is stopped after this if block, so (if you didn't use bottom-lines in myTable) there should not be any other lines below the one with the "#NV#".

The first release (v0.1.0) got an update about these '#NV#'-values:

Code: Select all

 ' optimized filling of table-rows in case of SQL-errors: 
'	if SQL-Error: "###SQLERROR###", "###ERROR###" or whatever is returned by BRX_eSQL,
'	if empty resultset: "#NV#"
Currently if the resultset is empty there's no way to avoid the '#NV#'s using the extension. I might implement that, but the table will then of course stay empty, and in this case it's not clear whether it's empty because there's no record or because the record is empty.

@formatting
I've a version that gets formatted values out of data shown in a base-form (including referred/concatted values from list-fields). The type comes from the recordset and is derived from the field-definitions of the database. This is a bit tricky because I work mainly with MySQL and I just copied the code to use it with the HSQLDB. I did this ~3 years ago, and I think there were no issues - anyway:
1. there are no tests with other backend-databases
2. the code is for exporting data to calc - this has to be changed of course...

I decided to take the sCellValue = oThisResultset.getstring(n) =>> myTable.getCellByPosition(x,y).setstring(sCellValue) because of a simple idea:
There's just one person on earth that knows exactly what he wants to see in a report - the one that programs it. If he wants "€ 2,50-" out of "2.5" he can manage it - there's no need for another type-conversion-tool (as there exists at least thousands, but not a single one that does exactly what userXYZ needs at a specific moment)
gogo
--
Linux - Debian based, LibreOffice 4.xx+5.xx
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: An alternative for Report Builder?

Post by Villeroy »

The database query showing the error is "qHistory" in this database: download/file.php?id=22208 [sorry, this demo is not usable as a real life driver's log because it has some calculation error I never fixed]
gogo555 wrote:@formatting
It's not about formatting. It is about data types. The target types of a text table are text and floating point numbers (doubles). All dates, times, booleans are sub-types of doubles. In this respect Writer cells behave just like Calc cells.

Dates are day numbers starting with day zero 1899-12-30.
Times are fractions of days. 12pm = 0.5, 6pm = 0.75
6/7/2009 08:00am --> 40000.3333 (day #40000 after day zero 1899-12-30 plus one third of a day)
Booleans are formatted zeros and non-zeros, 0 and 1 when coming from a database.

The simple source types (no arrays, no binary) for all types of database backends are mapped to SDBC data types. My Python code converts any non-textual SDBC type into a double, leaving the all formatting up to the user who is free to apply any format he wants to his text table cells. I'd expect this to work with all possible types of databases.

This way you can import time stamps and apply very complex formats such as
"Montag, den 2. Januar 2017 um 5 Uhr nachmittags"
"Monday, January 2 2017 at 5pm"
2,99795E+08 m/s
The end user can display currencies of all countries, percent values or you can format boolan TRUE as a check mark √ without touching macro code or SQL.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
gogo555
Posts: 2
Joined: Sat Jan 14, 2017 2:22 pm

Re: An alternative for Report Builder?

Post by gogo555 »

... ok - I now know where the lines below the #NV#-line come from - I think you've started the extension more than one time within an opened document ;)
I knew about this before the release but I didn't implement a warning for that case.
This means that there was no malfunction of the extension, because the first time the extension was started the sql returned an empty resultset.

I've attached your odb with a report-template. Extract it to the same directory and open the report by the new button inside the Base-Form. There are some examples about formatting and calculations inside. And you can see a problem that can occur in rare cases when the SQL-output is not formatted correctly by itself. Explanation: The first steps for this extension came from a little bit of code used to fill in invoices. And we get into troubles if we have an income of € 2,66 and in the database 2,65555555555555555 shows up :crazy:
report.png
btw: plz don't laugh about how the day-count was achieved - the DAYS() function in HSQLDB doesn't work, so I made a VERY crude way around...
Attachments
GasMoney_with_ott.zip
(26.31 KiB) Downloaded 460 times
gogo
--
Linux - Debian based, LibreOffice 4.xx+5.xx
Post Reply