Pentaho Report Designer mySQl query select

Question

Using Pentaho Report Designer, I can set up a data source to connect to my MySQL database which is hosted locally using phpmyadmin.

Testing the connection works, pulling in variables for setting up queries works, running previews of said queries displays the correct output, but I can not add any db fields into the report. Dragging/Dropping from the data pane simply won't work.

Anyone any idea what the problem is?

Answer 1

ideally there are two possible ways of selecting/Dragging the field in the report designer.

Step 1 : Select the Database Query and right click on it to select "SELECT QUERY" option. This brings down a list of columns. This makes the query fields draggable on the report.

Step 2 : Try to use the left panel. There you will find "text-field" or "number-field". Drag these labels on the report. Once you have done it, click on the field and you will be given an option to select the fields. You can select any of the fields as per the requirement. If it is not available, then you might have to enable the STEP I and then try STEP II.

Try any of the steps above. Hoping this will solve your problem :)

Answer 2

Just an update for anyone in the future who may have the same problem. I solved it by adding a "Limit" in the query editor.

Example:

SELECT batchjobs.name, batchjob_params.format, batchjob_params.notes FROM batchjobs, batchjob_params LIMIT 1

Without the "LIMIT 1", the query contents would not appear for selection in the report designer!

Answer 3

I had faced the similar issue, what i did was, put quotes around the parameters that were passed in the query in the kettle transformation from your report. Strange but i does solved the issue.