Apache OpenOffice Community Forum

I AM SERIOUSLY FRUSTRATED! This error has a habit of returning every once in a while causing a working report to suddenly not work at all. I have searched and searched for a possible solution but to date I have not been able to find anything that can help.
Over the past 10 years and with various versions of Open Office this error had a habit of popping up and bringing my reports to a halt. The query side of the report is fine and executes perfectly but the reporting side goes bad. (In this instance I could draw my report earlier today, but after returning to it it suddenly brought up this error). And every time it happens I have to recreate the report from the query (and it looks EXACTLY the same ) and then it goes on again for a year. (Whereafter it blows up again...) I personally believe there's a hidden bug there but that's just my opinion...
Error as follows: (libreoffice version)
An exception of type com.sun.star.lang.WrappedTargetException was caught.
org.libreoffice.report.ReportExecutionException: Failed to process the report
org.libreoffice.report.ReportExecutionException: Failed to process the report
at org.libreoffice.report.pentaho.PentahoReportJob.execute(PentahoReportJob.java:350)
at org.libreoffice.report.pentaho.SOReportJobFactory$_SOReportJobFactory.execute(SOReportJobFactory.java:217)
Caused by: java.lang.IllegalArgumentException
at java.sql.Date.valueOf(Date.java:140)
at org.libreoffice.report.SDBCReportData.getDate(SDBCReportData.java:188)
at org.libreoffice.report.SDBCReportData.convertObject(SDBCReportData.java:335)
at org.libreoffice.report.SDBCReportData.getObject(SDBCReportData.java:307)
at org.libreoffice.report.pentaho.StarReportData.get(StarReportData.java:117)
at org.jfree.report.data.ReportDataRow.<init>(Unknown Source)
at org.jfree.report.data.ReportDataRow.advance(Unknown Source)
at org.jfree.report.data.GlobalMasterRow.advance(Unknown Source)
at org.jfree.report.data.GlobalMasterRow.advance(Unknown Source)
at org.jfree.report.flow.DefaultFlowController.performOperation(Unknown Source)
at org.jfree.report.flow.layoutprocessor.ElementLayoutController.tryRepeatingCommit(Unknown Source)
at org.jfree.report.flow.layoutprocessor.SectionLayoutController.finishElement(Unknown Source)
at org.jfree.report.flow.layoutprocessor.ElementLayoutController.advance(Unknown Source)
at org.jfree.report.flow.layoutprocessor.LayoutControllerUtil.performPrecompute(Unknown Source)
at org.jfree.report.flow.layoutprocessor.ElementLayoutController.performElementPrecomputation(Unknown Source)
at org.jfree.report.flow.layoutprocessor.ElementLayoutController.startElement(Unknown Source)
at org.jfree.report.flow.layoutprocessor.ElementLayoutController.advance(Unknown Source)
at org.jfree.report.flow.AbstractReportProcessor.processReportRun(Unknown Source)
at org.jfree.report.flow.SinglePassReportProcessor.processReport(Unknown Source)
at org.libreoffice.report.pentaho.PentahoReportJob.execute(PentahoReportJob.java:338)
... 1 more

Openoffice 4.1.5 just gives me "SQL Status: S1000

An error occurred while creating the report."
and
"An exception of type com.sun.star.lang.WrappedTargetException was caught."
and
"Syntax error in SQL expression"

As stated. My sql code works perfectly in query mode. Here's the statement anyway!

SELECT "SUPPLIER"."SupplierName", "SUPPLIER"."Bank", "SUPPLIER"."ACB", "SUPPLIER"."AccountNo", "SUPPLIER"."Contact Name", "SUPPLIER"."Settlement", "SUPPLIER"."PaymentTerms", "PAYMENTS"."SUPPLIER", "PAYMENTS"."DATE_DUE", "PAYMENTS"."DATE", "PAYMENTS"."INVOICE", "PAYMENTS"."AMOUNT", "PAYMENTS"."ORDER", "PAYMENTS"."DELIVERY_DATE", "PAYMENTS"."DESCRIPTION", "SUPPLIER"."SupplierID", "PAYMENTS"."OUTSTANDING" FROM "SUPPLIER", "PAYMENTS" WHERE "SUPPLIER"."SupplierName" = "PAYMENTS"."SUPPLIER" AND "PAYMENTS"."DATE_DUE" <= {d '2018-03-02' } AND "PAYMENTS"."OUTSTANDING" > '0' AND "PAYMENTS"."PAID" = '0' ORDER BY "SUPPLIER"."SupplierName" ASC, "PAYMENTS"."DATE" ASC, "PAYMENTS"."INVOICE" ASC

I'm running Linux Mint 17.3 KDE on an HP 8570P laptop (intel i7 2.9gHz with 4Gb RAM) and the database itself is a local ODBC connection to a sqlite3 db (don't even get me started on the reasons for it). Any but any assistance will be appreciated

Install libreoffice, libreoffice-base and libreoffice-report-builder from your repositories.

I tried it on Win 7 Home Premium at home earlier and the result is the same. It's something that happens to a report that is used often, I'm sure. I have not had much success with Libreoffice from the repositories which is why I download Open Office. My reports are basically older builds and Libreoffice is not too friendly with old style reports while Open Office opens all my reports without a glitch, normally. (I have been using Open Office since 1.1)
I will however do as you say and see if that solves the problem.

I downloaded LO from the repository but no success. It's definitely something that happens to the reports over time that causes this. Could it be the java code gets corrupted over time?

Try a quick draft of a new report based on the same SQL. It may be a good idea to rebuild the report.

When a report fails as outlined above, is the problem persistent through a close down and restart of the computer?

@RoryOF: Yes. Once that happens I inevitably have to delete the report and reconstruct it.
@Villeroy: I just used the report wizard to quickly construct it using the same SQL and it crashed with the same reason... that's a first. I've reconstructed the sql code and again, crash. Now I'm really at a loss. Same error... "Failed to process the report" etc

I asked my question because I have seen applications fail because of a ""race condition" in Java. Sometimes they would run, sometimes not; eventually the problem was tracked down to their being started too soon after a computer reboot, before the operating system had stabilised. The cure was to wait until the various start-up applications (whatever they were) had loaded and completed.

How complex is your report? Does it include pictures stored in the database? Charts? Subreports? Calculated fields?

For me Calc is the preferred reporting engine. It is rock stable, does not depend on Java, can calculate anything, supports dynamic charts, can build pivot tables from databases, LO 5.4 even has dynamic charts on pivot tables. Calc has sufficient formatting capabilities for anything tabular. With "power filtering" and very simple macros it is possible to implement subqueries. Spreadshets are not embedded in the Base document (which is an advantage). Pictures are the only items you can not report in Calc.

Simple example on Calc+Base combined: viewtopic.php?t=88516&p=416210#p416210

one picture, a logo. and calculated fields. I've attached a jpg image of the report in report builder. The image isn't really necessary. The calculations are simple. I will take your advice and try and do it via Calc. Let you know soon enough.

Well, this is doable. Drag the query icon from the left pane of the data source window into a spreadsheet in order to link the required record(s) as a database range. If you need more than one import range, put them on separate sheets or ensure that they are separated by blank columns and rows.
In LibreOffice you have to adjust a hidden option for the import range. Data>Define... [Import1], [More Options] "Insert or delete cells" should always be checked. In OpenOffice this is checked by default.
All details: [Tutorial] Using registered datasources in Calc

Define a printable area on a separate sheet where you reference the imported data including the logo picture.
Calculation is just what spreadsheets are made for and Calc plays very well with database ranges. For instance, =SUM(INDEX(Import1 ; 0 ; 3)) sums column #3 of the database range named "Import1".
From a single customer record you can pick distinct values like this: =INDEX(Import2 ; 2 ; 1) [first row is the column label. row 2, column 1 is the first value]
menu:View>Print Preview helps to keep the print area within one print page as you fiddle around with the column widths and row heights. Finally you get a cleaner column layout as in your screen shot.
menu:Tools>Protect>Sheet protects the layouted print sheet when you are finished. Sheets with import ranges must not be protected.
I use a special template with prepared cell styles for my Calc reports (booleans as check marks, German currency, German date/time, text attributes for label cells etc.)

If you need more than one record set in your print area, the following macro updates all import ranges: You may use the "sheet activation" event to trigger this macro so it will update the invoice every time you activate the sheet with the print area.
You may use a non-printing push button on the sheet or a customized toolbar button as well.
Unlike "normal" reports, Calc allows you to combine reports with "power filtering" form controls as demonstrated in my above example. "Power filtering" writes filter criteria from a form to a special filter table and combines the stored criteria with report data or filtered subform data.

Have you tried Creating a View and running the report from that?
I have found that with some reports using the ORB I cannot use a query, only a View as the data source.

Hi Nocton
The problem here is that the report worked for nigh on a year and then it decided to crash for no apparent reason. From what I can gather the error has something to do with the source to target (I may be wrong though) so in this case the SQL code translation? No matter how I try and change my SQL code though it doesn't fix it. I have very few extensions and no new extensions installed (Basically pdf import and report builder) so I do not believe it could be an extension interfering. Maybe OO/LO lost their sqlite translaters ?
I'm currently working on Villeroy's solution though. Will keep everyone updated

Well, the report still crashes when using a view as source. Thought I'd give it a try.

Hi!
In my little experience about reports, I knew that old-fashioned reports don’t match really well with queries, so I normally use the SQL source, which is much more adecuate as data source for reports.
Also the LibreOffice evolution has provoked little changes in query results.
I had several reports in which the order is different whether it was oppened by LibreOffice or by Openoffice.
I don’t know how the evolution affects reports in Linux systems
I sugest (if you didn’t do it yet) to change the data source changing to the SQL or changing a bit (or totally) the query definition.

Bye!

The problem with the spreadsheet report is that you can easily link a single record or a handfull of records to a rectangular print range.
I made a quick draft with the "Bibliography" data source dumped on the first sheet, a scrollbar report with one record at a time and another one with up to 100 records on the third sheet. The scrollbar scrolls without macro code but needs adjustment when the size of the import range changed. The macro code refreshes the import range and adjusts the max. value of the scrollbar to the new size of the import range.
The print settings are based on set up print areas and page style "Report" with a picture in the header.
The print preview displays both reports, the single tile on the scroll report and the visible tiles of the tall report.

The second report is made of 100 tiles (one tile for each of max. 100 records), separated by page breaks, with a number sequence in column A referencing the correct row of the import range. Another calculated field filters out the superfluous tiles beyond the record count.

This can be used without any macro but I added one snippet to copy one selected tile down the rows.
1. Create a database range with header row and one tile including the calculated helper fields.
2. Adjust the Basic constant cCopies if you may need more or less than 100.
3. Select the first tile without header row and run the macro.
4. Apply the row filter.

The refresh button on that sheet refreshes the import range and the filtered range with its filter setting.

Thanks Villeroy. That is really going out of your way to assist and it's truly appreciated.
My base reports are working again. I recreated them the very next day and they worked. There has to be a bug in there somewhere....
Looking at what you did I had never thought of the scrollbar option and was wondering how to display the items individually. Your help is again, truly appreciated. And yes, I'm taking your advice and completing my spreadsheet based on your example. This basically has solved my problem, though I guess it wasn't the 'solution' to the OOo 'bug'.

This is really weird. Happened again with another report. Went back on a month old backup and reinstated it, only to find the error happen the minute I changed the date. Could this be date related. Did it a second time from the backup and tried again, same problem. If I leave the date unchanged, no problem, the minute I change it to 2021, bang, exception! After that, even if I change the date back the report seems corrupted... I will eventually have to change all my reports to Villeroy's method because that seems stable and is working well for me. Maybe a dev could have a look at this? I can send the db and both copies of the odb file in question for analysis at any time.

the_watchmann wrote:Maybe a dev could have a look at this? I can send the db and both copies of the odb file in question for analysis at any time.

You can file a bug report: [Tutorial] Reporting bugs or suggestions.
However, very unlikely to be investigated given the lack of manpower on AOO.
If using LibreOffice, it may be worth it (have not read the whole topic, not sure if you still use AOO).

And I think I have the answer... took a while but I hope this helps.
It is not a bug. Wrapped target exception means that the report itself was generating an error when trying to read/write the data from the query. In simple terms the formatting on the report does not conform to something read in your query and the exception is thrown when report builder tries to write it and it conflicts with your report formatting. At least, this is the case with my report. (It was a date with an extra digit). After I fixed the date, the report reappeared. So I think, when this happens, go through your report's source table/query and make sure everything is as it should be first.
I really hope this helps anyone with future headaches like this one.

What is the data type of the table column when this error is thrown? It is text (VARCHAR), right? And the text content looks like "23/03/2020", right?
Either you store dates in a date column, then you can format the values any way you want.
Or you display the strings as literal text as stored in the column or you use some text manipulation to modify the text so the text "23/03/2020" is converted into another text "03/23/2020" representing a US date. But a text value is not a date value. This interpretation of "23/03/2020" happens only in our brains but not in a computer.
SUBSTR("txt",4,3) || SUBSTR("txt",1,3) || SUBSTR("txt",7,4) converts a US date string into a string representing a normal date and vice versa if the number of digits and separators is constant.
CAST(SUBSTR("txt",7,4)|| '-'|| SUBSTR("txt",4,2) ||'-'|| SUBSTR("txt",1,2) AS DATE) converts a US date string into a true date value. By creating an ISO date string "2020-23-02" and then converting this into a date value.