Can't get Report to execute due to errors

This query works in queries but using it in a report produces all kinds of errors, one of which is below.

Using table ‘tax’: There is a relationship WHERE tax.addno=address.id. I want to GROUP BY address.name but that hasn’t worked out yet either.

SELECT year AS Year,  payno AS 'Payment No', type AS 'Type', datedue AS 'Due Date', amtdue AS 'Amt Due', datepaid AS 'Date Paid', amtpaid AS 'Amt Paid', checkno AS 'Check No', visano AS 'Visa No', address.name AS 'Agency'
FROM tax, address
WHERE addno=address.id && year=2017 && amtpaid>0
ORDER BY type;

TYPICAL ERROR:
An exception of type com.sun.star.lang.WrappedTargetException was caught.

EDIT:

The following are images of the report written in the wizard based on subject query

image description

Hello,

If a query is working it should not be a problem using it in a form or to generate a report. You state the report generates all kinds of errors but only present a generic TYPICAL ERROR. There is no direction here. To add to that, the entire question is about a problem with executing a report but there is absolutely nothing describing the report you are trying to generate. Headers? Footers? Grouping? Calculations? What is end product wanted? Need help to give help.

Have now built tables & report based upon your SQL. My query is an exact copy of this posted SQL. Report runs without problem or error.

When I get stuck my general approach is to break the problem down. In this case make a copy of the report. Verify that that does not work. Then start deleting things, and testing, and deleting things and testing, etc, half at a time if you want, … sort of a binary search is the quickest way. Just make the report simpler and simpler until it works, and then figure out what the exact change is that goes from good to bad, or bad to good. No one can help you do this, you have to do it yoursel

This is a general problem with both Libreoffice and Open office and unfortunately over the years has not been addressed. I am again experiencing the same problem on Kubuntu 20.4 and have previously posted this problem in the Open Office forums. There is a workaround that Villeroy gave that works quite well and at least there was some form of help there even though the actual problem was probably never addressed.
https://forum.openoffice.org/en/forum/viewtopic.php?f=42&t=92590

@the_watchmann,

Do not know what you are referring to. This post had a resolution. The problem was the connector for MySQL/MariaDB. JDBC did not work but the Native connector did.

Hello,

As already stated, I don’t see a problem with what you have presented. I am basing this on you using MySQL as your database. Here is what you have show in the question so far (obviously this data is different than yours):

Report Query:

Report Layout:

and finally the running of the report:

This was run on Linux Mint 18.3 using LO v5.4.3.2 and MySQL Server 5.7

There must be something else to your problem as I so far cannot duplicate.

Edit:

I have now been able to generate errors upon report execution:

image description

I was able to do this by simply switching to a different connection type. I typically use MySQL (Native) and simply changed to JDBC. This in turn then produced the error. The problem is in the connector.

If this answers your question please tick the :heavy_check_mark: (upper left area of answer). It helps others to know there was an accepted answer.

This is the connector I use on MySQL - click here, At bottom of post v1.2. I have used this with MariaDB on Debian. Don’t recall any obvious problems. It’s an extension so install as such.

To change connectors, from main Base screen menu select Edit->Database->Connection Type.... Then on the dialog select MySQL (Native) from the Database Type: from the dropdown list. Then Next button. On next screen you should be able to just enter the DB name & server (probably localhost) and then Finish button. That should change the connector used.

Thanks,

I am running Ubuntu MATE 16.04 with MariaDb - Server version: 10.0.31-MariaDB-0ubuntu0.16.04.2 Ubuntu 16.04

My connection is -

image description

I thought when I Installed LO that MySql (NATIVE) was not available.

How can I get the MySql(NATIVE) driver?

EDIT:

Just looking around;

Seems MySql driver is installed but I can’t fifgre out how to use it to connect to LO Base. Any Hints?

Please do NOT use an answer (it is for response to original question only) for a response to an answer. Instead use a comment to the answer you are responding to or edit original question to add additional information.

Please read the above comment!