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 is SelectYear and the second is SelectMonth. We will create them and establish a default value, as we did in the previous chapter.
  4. We put an object, Message, in the Report Header section to show us the values that have been chosen as parameters. By doing so, the period under analysis can be seen when we export the report in different formats. We configure the message as follows:
    • Attributes.value = =CONCATENATE("Period: ";IF([SelectMonth]=1; "January"; IF([SelectMonth]=2; "February"; IF([SelectMonth]=3; "March"; IF([SelectMonth]=4; "April"; IF([SelectMonth]=5; "May"; IF([SelectMonth]=6; "June"; IF([SelectMonth]=7; "July"; IF([SelectMonth]=8; "August"; IF([SelectMonth]=9; "September"; IF([SelectMonth]=10; "October"; IF([SelectMonth]=11; "November"; "December")))))))))));"/";[SelectYear])

What just happened?

We created a new report and named it 11_Adding_Charts.prpt. We created a JDBC data set and configured its connection and SQL query. We created and configured the SelectYear and SelectMonth parameters. Also, we added and configured a Message object to show the time period chosen by the end user. We also saw a small preview of the report that we will create by the end of the chapter.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset