Chapter 5. Adding a Relational Data Source

In this chapter, we will create a copy of the report created in the previous chapter and modify it so that it takes its data from a relational source. We will explain what JDBC is, what a JDBC driver is, and how it is used.

The topics we will be covering in the chapter include the following:

• Downloading the JDBC driver for MySQL and copying it inside Pentaho Report Designer (PRD)

• Using the UI to create/edit JDBC-type data sets and creating a JDBC connection to our sakila database, with its corresponding SQL query

• Modifying the following sections: Report Header, Group Header, Details Header, and Details Footer; we will configure the last two to be repeated on each page

• Deleting certain objects from the report, adding some new ones, and modifying others so that they all correspond to our new data source

• Aggregation functions, explaining the most used ones; we will add a new function into our report and use its output in order to modify the style of a...

Learning about JDBC driver

Java Database Connectivity (JDBC) is an Application Programming Interface (API), which defines how a client should access and interact with a database from Java. Each provider (IBM, Oracle, Sun, Microsoft, and others) implements the mechanism (the "How To") for its own product, allowing the client not to be bothered with the details of each implementation and worrying only about its interface. In Java slang, this is known merely as a controller or JDBC driver.

Note

A JDBC driver, generally speaking, consists of one or various files with a .jar extension, which must be copied on a certain path (Java CLASSPATH, that is) so that applications can make use of it instead.

In order for a JDBC driver to be used, Java programs require the following information:

• URL: This is a string that specifies, among other things, the protocol, location of the server, port, and name of the database.

• Driver: This is the name of the class that implements the java.sql.Driver interface.

• User...

Time for action – configuring drivers

At this time, we will download the JDBC driver from the official MySQL assets and then copy it into the Pentaho Report Designer.

In order to download the MySQL JDBC driver we will open the following web page: http://dev.mysql.com/downloads/connector/j/5.1.html.

1. Move downwards in the page and find the tab named Generally Available (GA) Releases. We will choose the right file, depending on our operative system, from the following choices:

   • mysql-connector-java-x.x.xx.tar.gz

   • mysql-connector-java.x.x.xx.zip

2. Click on the Download button and follow the steps to perform the download.

   In order for PRD to be able to make use of the recently downloaded JDBC driver, we need to copy it to a certain path.

3. Open the compressed file we just downloaded and browse its contents until we locate the JDBC driver: mysql-connector-java-x.x.xx-bin.jar.

4. Copy this .jar to [PRD_HOME]/lib/jdbc.

5. For the PRD to load the JDBC driver we just added, we need to restart the PRD.

What just happened...

Creating a new data set

In this section, we will see how to create a new data set.

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 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:

   

   Note

   By default, PRD brings a series of example data sets. To see these connections in detail, we can go to the...

Time for action – modifying the header and detail

Next, we will modify our report's layout. We will delete the old objects from the Report Header section and the Details Header section, hide the Page Header section and enable the Group Header section of our report. Then, we will add three new labels (Country, Customer, and Amount) and modify the data to be shown on the Details section. Also, we will configure the behavior of Details Header.

1. We should now delete every label () as well as the horizontal-line () from the Report Header section and the Details Header section.

2. Then we will go to Report tree structure (Structure tab) and search the tree for the following node: Master Report | Group | Group Header. We will select this node and modify Attributes.hide-on-canvas to false.

   This modification will enable the visualization of the Group Header in the Work Area.

3. Then we will go to Report tree structure (Structure tab) and search the tree for the following node: Master Report | Group | Details...

Time for action – modifying the report's footer

We will configure the Details Footer section for it to be repeated on every page of our report. We will configure the top border of the Details Footer section. We will modify the name of the labels on the Details Footer section to match the ones in our data set. Finally, we will modify the SumQty function to perform a sum on the sum_amount field.

1. We will go to Report tree structure (Structure tab) and search the tree for the node by navigating to: Master Report | Group | Details Footer. We will select this node and modify it in the following way:

   • Style.repeat-header: true

   This modification will make this section present on every page.

2. We will delete horizontal-line from the Details Footer section.

3. We will go to Report tree structure (Structure tab), navigate to Master Report | Group | Details Body | Details Footer, and set the following:

   • Style.top-style: solid

   • Style.top-size: 4.0

   • Style.top-color : #009933

4. At this point, we will modify the text...

Aggregation functions

So far, we have introduced two functions into our report:

• ProductCounter: This performs a cumulative count on the rows in the Details section. This function belongs to the Running category.

• SumQty: This performs a cumulative count on the sum_amount fields of the rows present in the Details section. This function also belongs to the Running category.

Every function in the Running category performs calculations, sum, counts, and so on in a cumulative fashion, that is, they take into consideration the values obtained on previous pages. For example, the Count function appearing on the first page performs a count of the rows of the said page, from the second page on it will add the current page count to the total count of the previous one. In the same way, the functions Maximum and Minimum, when applied to the first page only take into consideration the minimum and maximum values, respectively, as shown on the first page. In the following pages, they will only update these...

Time for action – using functions to configure styles

We will now create a function to obtain the maximum value for sum_amount present in our report. We will then use the said value to obtain its corresponding customer and apply a bold style onto it.

1. In the Summary category, we will create a new function Maximum and configure the following:

   • Functi on Name = MaxSumAmount

   • Field Name = sum_amount

2. Now we will select the Message object we placed in the Details section of our report and configure the following:

   • S tyle.bold: =IF([sum_amount]=[MaxSumAmount];"true";"false")

   This formula will analyze row by row if the current value of sum_amount equals MaxSumAmount (the maximum value for sum_amount). If such a condition is true, the name of the customer shall be put in bold, if it is false, it will retain the current style.

   Note

   In later chapters, we will discuss greater detail the definition of formulas.

3. If we now perform a preview on our report, we will be able to see how the style for the customer...

Encoding charset

The encoding charset is a method that allows the conversion of a character in a natural language into a symbol of a different representation system as a number in an electronic system. The conversion is performed after applying a series of codification rules. This concept is very important since its application allows us to show symbols belonging to different languages into our reports.

To modify the encoding charset of our report, we must go to Report tree structure (the Structure tab), search the tree for the Master Report node, select it, and put into Style.encoding the charset we wish to use, for example:

• Style.encoding = LATIN1

Note

If you are having encoding problems and the report is correctly configured, check that Java and data base are correctly configured.

Have a go hero

In this section, we will invite you to create your own report from the ground up, and to employ in this endeavor the knowledge you have acquired up to this point. The report we are inviting you to...

Summary

In this chapter, we created a copy of the previously created report and modified it to use a relational source to obtain its data. We then learned about JDBC and JDBC drivers, downloaded the MySQL JDBC driver, and copied it into Pentaho BI Server and PRD.

We also created a JDBC connection with our sakila database and wrote an SQL query to obtain data to fill our report. We learned how to modify certain sections and objects of our report; among these modifications, we configured Details Header and Details Footer sections to be repeated on every page.

We discussed the most commonly used aggregation functions and created a formula to use a function to modify the style of an object. We also explained how to use and apply an encoding charset to our report.

Finally, in order to strengthen the acquired knowledge we proposed the creation of a new report from the ground up.

In the next chapter we will explain how to create Groups in our reports.