Time for action – creating a new report

We will now create a new report that we will use in this chapter's exercises. We will create a JDBC data set that connects to the Sakila database, and we will establish its SQL query. Next, we will create two parameters to choose the year and month for which we want data, and finally we will add the Message object to show the period under analysis.

1. We will create a new report and save it with the name 11_Adding_Charts.prpt.

2. We will create a JDBC data set and configure it as follows:

   • Connections = sakila db

   • Query Name = Ratings

   • Query =

       SELECT film.rating, SUM(payment.amount) sum_amount, count(1) 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 YEAR(payment.payment_date)=${SelectYear}
       AND MONTH(payment_date) = ${SelectMonth}
       GROUP BY film.rating

3. We will create two parameters. The first...