Next, we will create a new report, create its data set, and add a crosstab. Then we will configure the crosstab to show us the values of amount
analyzed after crossing years-months
with ratings
.
Create a new report and save it with the name 18_Adding_Crosstabs.prpt.
Create a JDBC data set and configure it as follows:
Connections=
sakila db
Query Name=
DateRatingAmount
Query=
SELECT YEAR(payment.payment_date) AS payment_year, MONTH(payment.payment_date) AS payment_month, film.rating, SUM(payment.amount) AS sum_amount 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","R") payment_year, payment_month, film_rating
- In the Report Header section, add a crosstab object:
Make the crosstab Inline, and when asked about the data set to use, choose DataRatingAmount and click OK. After doing this...