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.

  1. Open the 01_Hello_World.prpt report from the PRD UI. To do so, click on the Time for action – creating a new data set icon from the Shortcuts menu, and search for the report 01_Hello_World.prpt; select it, and click on the OK button.
  2. Now navigate to File | Save As... and save the current report with the following name: 03_Adding_Relational_DS.prpt.
  3. 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.
  4. 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:
    Time for action – creating a new data set

    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 SettingsuserName.pentaho eport-designeruserorgpentaho eportinguidatasourcesjdbcSettings
  5. Now we will create a new connection to our sakila database. To do so we will select the Time for action – creating a new data set 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.

  6. 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.
  7. At this stage, we will create a new query and in order to do so click on the Time for action – creating a new data set 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
  8. To verify that we have performed every step correctly, click on the Preview button to obtain a quick view of the relevant data.
    Time for action – creating a new data set

    Note

    In the Query section, we can find the Time for action – creating a new data set 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.

  9. 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.

  10. Right-click on our newly created CountryCustomerAmount data set and select the Select Query option.
    Time for action – creating a new data set
  11. Now select the parent node of our old data set, that is, Table, and then click on the Time for action – creating a new data set 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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset