Summer Sun Summer Sun - 1 month ago 16
SQL Question

How to use more than one query in Pentaho Report Designer?

I am trying to add two queries in Pentaho Report Designer so that I could get query results from more than one table.

Here is my case:

Query 1:

SELECT
`raw_crawl_shop_list`.`sales`,
`raw_crawl_shop_list`.`keyword`,
`raw_crawl_shop_list`.`number`
FROM
`raw_crawl_shop_list`
LIMIT 1


Query 2:

SELECT
`dim_keyword_tag`.`keyword`,
`dim_keyword_tag`.`tag`
FROM
`dim_keyword_tag`


My problem:

I want to use the five columns selected from two table respectively (that means there's no relationship between these columns, I just use them seperately), However, in Pentaho Report Designer. I could only use columns selected from one single query.

As the picture shown below:

enter image description hereenter image description here

If I choose Query 1, columns selected by Column 2 disappear, if I choose Query 2 the same with Query 1.

My question:

How could I use two queries in Pentaho Report Designer in a single report OR is there a way to combine these two sql queries in a single one with the three columns from the first and the two columns from the second?

Thank you all!

Answer

There are two possible way of reading two queries in PRD.

  1. Creating a SUB-REPORT and then define the second query. Check this wiki for more.

  2. JOINING the two tables inside a single query, as correctly said by Kotukunui.