Chapter 7. Adding Parameters

This chapter will be dedicated to parameters. Through the use of parameters, the final user will be able to interact with the report. A parameter is usually represented through a user interface component, which allows the selection or input of values.

Pentaho Report Designer (PRD) eases the task of parameter creation by giving us a simple and intuitive interface.

In this chapter:

• We will create both simple parameters and nested parameters

• We will make a parameter obtain its values through a data set

• We will discuss the different kinds of parameter visualization available, exposing the features of each one of them

• We will discuss how to make our SQL queries dynamically constructed according to the values the final user has provided through the use of parameters

As an extra, we will invite you to create your own report from the ground up, as well as give you a few tips to assist you in its creation.

Starting practice

In this chapter we will create a new report. We will then do the necessary changes in its layout for the final result as follows:

We are going to allow the end user to select the rating they want to analyze, as well as the year and month in which the films were rented.

Time for action – creating a new report

We will create a new report from scratch. In order to do this, we will need to create the report itself, then create a JDBC-type data set and establish its SQL query. Finally, we will create a general layout, which means we will need to add to our report a series of objects and configure them properly.

1. We will create a new report and save it under the name, 07_Adding_Parameters.prpt.

2. We will create the following layout for our report:

   

3. At this stage, we need to create a JDBC-type data set, establish the connection with our sakila database, and then create the following query:

   SELECT film.rating, film.title, 
   SUM(payment.amount) AS sum_amount, COUNT(1)  AS count_rental
   FROM payment
   INNER JOIN rental ON rental.rental_id=payment.rental_id
   INNER JOIN inventory ON inventory.inventory_id=rental.inventory_id
   INNER JOIN film ON film.film_id=inventory.film_id
   WHERE film.rating IN ('G','PG','PG-13','R','NC-17')
   AND YEAR(payment.payment_date) = 2005
   AND MONTH(payment_date...

Parameters

Parameters enable the final user to interact with the contents of the report, allowing the reports to present dynamically generated content and adapt to the needs of the user.

Pentaho Report Designer (PRD) allows the inclusion of parameters in its reports by means of a fairly nice UI. Parameters in PRD can be quite simple, such as the selection of a value from a list, or more complex, such as when nested parameters are created so that setting a value in one of them causes the modification of the values of others. This means that the data set obtained by the nested parameter depends on the values selected in another parameter. In the first case we could establish a list containing every year in which films were rented, and have the user select one of them:

In the second example, we could create two parameters so that one of them holds every country, and the other one holds every city available, and have them behave in a way such that when the user selects a country, the other parameter...

Time for action – adding parameters

We will create a new parameter so that the user can select the rating (or ratings) he or she wants to see in the report. In order to do so, we will configure a data set and then define and explain its properties. Among them we will find: Name, Label, Value Type, and Display Type.

Afterwards, we will modify our main data set, RatingFilms, so that it takes its values according to the recently created parameter.

1. Firstly, we will perform a preview on the report and explain how to properly present parameters to the user. We will add a parameter so that the user can select the rating (or ratings) he or she wants to analyze. We will go to the Data tab, select Parameters, right-click on it and select the Add Parameter... option, as shown in the following screenshot:

   

   We will be presented with the following window, in which we will create and modify our parameters:

   

   To the left, we find the data sets currently being used in our report. To the right, we see the parameters...

Time for action – creating nested parameters

We will create two new parameters. The first one will ask the user to select the year in which a film was rented, and the second one will ask the user to select the corresponding month.

Later on, we are going to nest these two parameters. The months in the second parameter will be shown according to the year selected in the first parameter. This way, the second parameter will only offer selection of those months in which there is relevant information present.

1. Finally, we will modify our main data set to take into account the values present in these two new parameters. We will create a parameter so that the final user can select the year in which films were rented, to be shown in the report. We will head to the Data tab, select Parameters, right-click on it, and select the Add Parameter... option.

2. On the left-hand side (Datasource), we will press the button to create the data set belonging to this new parameter. At this stage we will select, on...

Summary

We created a report from scratch and configured it so that its layout matches the one proposed in this guide. We also added and configured the objects needed (label, text field, and so on), and set up a JDBC-type data set.

We explained what a parameter is and what it allows us to do. We discussed different types of parameters and their typical uses. We mentioned and explained the main features of every display type available.

We added to our report a simple parameter, then configured its data set and its main properties. Later on we modified the main data set so that it receives the value currently present in the parameter, thus creating a dynamic data set.

Next, we created two parameters so that the final user could select the year and month on which to perform an analysis. The month parameter was nested with the year parameter so that its values depend on the year selected by the user.

As done previously, we modified the main data set so that it takes into account the values present...