Time for action – creating a new data set
Now we will open the previously created report and save it with a different name. This copy shall serve as the base report, which we will use throughout this chapter. Later we will create a new data set of JDBC type.
- Open the
01_Hello_World.prptreport from the PRD UI. To do so, click on the
icon from the Shortcuts menu, and search for the report 01_Hello_World.prpt; select it, and click on the OK button. - Now navigate to File | Save As... and save the current report with the following name:
03_Adding_Relational_DS.prpt. - We will now create a new data set of JDBC type. In order to do so, go to the Data Panel (inside the Panel tab), right-click on Data Sets and select the JDBC option.
- We will be presented with a new window on the left side of which we will be able to observe the connections that have already been defined by the current user:
Note
By default, PRD brings a series of example data sets. To see these connections in detail, we can go to the following folders:
- On a Linux system:
/home/userName/.pentaho/report-designer/user/org/pentaho/reporting/ui/datasources/jdbc/Settings - On a Windows system:
C:\Documents and Settings\userName\.pentaho\report-designer\user\org\pentaho\reporting\ui\datasources\jdbcSettings
- On a Linux system:
- Now we will create a new connection to our
sakiladatabase. To do so we will select the
option and complete the newly opened form with the following data:
- Connection Name:
sakila db - Connection Type:
MySQL - Access:
Native (JDBC) - Host Name:
localhost - Database Name:
sakila - Port Number:
3306 - User Name:
root - Password:
root
Note
This Connection Configuration window is used by every tool in the Pentaho Suite.
- Connection Name:
- Once the form is completed, click on the Test button to verify that the data we entered is correct, and then click on OK to continue.
- At this stage, we will create a new query and in order to do so click on the icon found on the Available Queries section. We should now select the new query and modify the Query Name section to have the value
CountryCustomerAmount. Finally, in the Query section, we will copy the following query:SELECT country.country, customer.first_name, customer.last_name, SUM(payment.amount) sum_amount FROM payment INNER JOIN customer ON customer.customer_id=payment.customer_id INNER JOIN address ON address.address_id=customer.address_id INNER JOIN city ON city.city_id=address.city_id INNER JOIN country ON country.country_id=city.country_id GROUP BY payment.customer_id ORDER BY country.country LIMIT 0,30
- To verify that we have performed every step correctly, click on the Preview button to obtain a quick view of the relevant data.
Note
In the Query section, we can find the
button. It brings up a quite useful visual editor, which can assist us in the creation of queries. With this editor you can drag-and-drop tables to use, check the fields that will be included, and so on. It is ideal for users who have no knowledge of SQL. - Finally, click on OK.
PRD supports simultaneous data sets on the same report, but only one of them can be selected to be used in the report. At this point, we have two data sets available in our report, but ZoneProductQty is selected.
- Right-click on our newly created CountryCustomerAmount data set and select the Select Query option.
- Now select the parent node of our old data set, that is, Table, and then click on the
option (available on the Shortcuts menu).
What just happened?
We just opened the previously created 01_Hello_World.prpt report and saved it with a different name: 03_Adding_Relational_DS.prpt. We created a JDBC-type data set and defined the connection to our sakila database. Then we created a new query and performed a preview on the data. We also discarded the old data set and selected the new one.