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 Time for action – creating nested parameters 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 (Time for action – creating nested parameters), and configure the following:
    • Query Name = ListOfYears
    • Query = SELECT distinct YEAR(payment.payment_date) AS payment_year FROM payment ORDER BY 1
  3. 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
  4. 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).
  5. We will head to the Data tab, select Parameters, right-click on it, and select the Add Parameter... option.
  6. On the left-hand side (Datasource), we will press the Time for action – creating nested parameters 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 (Time for action – creating nested parameters), 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.
  7. To continue, we will press OK.
  8. 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
  9. 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}.

  10. We will press OK to continue. If we perform a preview, we will see something like this:
    Time for action – creating nested parameters

What just happened?

We created the ListOfYears 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:

Have a go hero

The steps you should be following are as follows:

  1. Create a new report and save it under the name 08_Adding_Parameters_Plus.prpt.
  2. Create a new JDBC-type data set and configure it to use the sakila database.
  3. Use the following SQL query:
    SELECT first_name, last_name, concat(last_name, ', ', first_name) AS full_name 
FROM actor 
ORDER BY last_name
  4. Create the general layout for the report (label, text field, rectangle, and so on).
  5. 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
  6. Create a second parameter with the following configuration:
    • Name = SelectFirstName
    • Label = First Name -> Contains:
    • Value Type = String
    • Default Value = %
    • Display Type = Text Box
  7. Modify the SQL query so that it uses these parameters.

Pop quiz – format and parameters

Q1. Which of the following affirmations are true?

  1. 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.
  2. Parameters allow the user to interact with the contents of the report.
  3. When creating nested parameters, PRD only allows hierarchies that are two-level deep.
  4. If the parameter must show a long list for a simple selection, the best option is to use a dropdown display type.
  5. 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.
  6. 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.
  7. When adding a new parameter, it is mandatory to always use a data set.
..................Content has been hidden....................

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