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.
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.
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 the left-hand side of the window, the connection to the sakila database, create a new query (), and configure the following:
Query Name = ListOfYears
Query = SELECT distinct YEAR(payment.payment_date) AS payment_year FROM payment ORDER BY 1
We will press the OK button to continue. Now, on the left-hand side, we will select the data set we just created (ListOfYears), head to the right-hand side, and configure the following:
Name = SelectYear
Label = Year:
Value Type = Number
Mandatory = True
Display Type = Drop Down
Query = ListOfYears
Value = payment_year
Display Name = payment_year
We will press the OK button to continue. We will now create another parameter so that the final user can select a month in which films were rented to be present in the report. We will also configure this new parameter to receive the value the user has chosen in the previous parameter (SelectYear).
We will head to the Data tab, select Parameters, right-click on it, and select the Add Parameter... option.
On the left-hand side (Datasource), we will press the button to create the data set belonging to our new parameter. Once there, we will select, on the left-hand side, the connection to the sakila database, create a new query (), and configure the following:
Query Name = ListOfMonths
Query = SELECT distinct MONTH(payment.payment_date) AS payment_month_number, DATE_FORMAT(payment.payment_date,'%M') AS payment_month_string FROM payment WHERE YEAR(payment.payment_date) = ${SelectYear} ORDER BY 1
This query will take into account the present value of the SelectYear parameter; that is why the code WHERE YEAR(payment.payment_date) = ${SelectYear} is included, and returns a list of months in which payments have been made. To obtain the number of the month in which the payments were done, the following code was used: MONTH(payment.payment_date). To obtain the name of the month we use this code: DATE_FORMAT(payment.payment_date,'%M'). Internally, we will use the number of the month. The name of the month will be used to show the final user the corresponding options.
To continue, we will press OK.
On the left-hand side we will select the data set we just created (ListOfMonths). We will then head to the right-hand side and configure the following:
Name = SelectMonth
Label = Month:
Value Type = Number
Mandatory = True
Display Type = Drop Down
Query = ListOfMonths
Value = payment_month_number
Display Name = payment_month_string
We will now press the OK button to continue. Next, we will modify the SQL query of our data set to take into account the values for year and month as selected by the final user. To do this, we will head to the Data tab, select RatingFilms, right-click on in it, and then select the Edit Query... option.
We will modify the query to look like the following:
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 (${SelectRating})
AND YEAR(payment.payment_date) = ${SelectYear}
AND MONTH(payment.payment_date) = ${SelectMonth}
GROUP BY film.film_id
ORDER BY film.rating, film.title
What we just did was replace the code 2005 with the code ${SelectYear}, as well as the 6 code for ${SelectMonth}.
We will press OK to continue. If we perform a preview, we will see something like this:
What just happened?
We created theListOfYears parameter so that the final user can select the year he or she wishes to analyze. Later on, we created the ListOfMonths parameter and configured it so that the months' list to be shown depends on the year previously selected by the user. Finally, we modified the SQL query of the data set belonging to RatingFilms, so that it takes into account the values currently selected in the mentioned parameters.
Have a go hero
Now that we have concluded this chapter, we will invite you to create your own report. By doing so you will be able to apply some of the things we have learned so far. The proposed report should look like this:
The steps you should be following are as follows:
Create a new report and save it under the name 08_Adding_Parameters_Plus.prpt.
Create a new JDBC-type data set and configure it to use the sakila database.
Use the following SQL query:
SELECT first_name, last_name, concat(last_name, ', ', first_name) AS full_name
FROM actor
ORDER BY last_name
Create the general layout for the report (label, text field, rectangle, and so on).
Create a new parameter and configure it in the following way:
Name = SelectLastName
Label = Last Name -> Starts with:
Value Type = String
Default Value = %
Display Type = Text Box
Create a second parameter with the following configuration:
Name = SelectFirstName
Label = First Name -> Contains:
Value Type = String
Default Value = %
Display Type = Text Box
Modify the SQL query so that it uses these parameters.
Pop quiz – format and parameters
Q1. Which of the following affirmations are true?
When configuring the format of a number field or date field, among the default options, we will only find those available that are related to the type of the data.
Parameters allow the user to interact with the contents of the report.
When creating nested parameters, PRD only allows hierarchies that are two-level deep.
If the parameter must show a long list for a simple selection, the best option is to use a dropdown display type.
If the parameter shows a short list for a simple selection, and it is desired to visualize every option available, the best option is to use a Single value list display type.
If the parameter shows a short list for multiple selections, and it is desired to utilize as little screen space as possible, a good option would be to use a Multiselection button display type.
When adding a new parameter, it is mandatory to always use a data set.