We will create a new parameter so that the user can select the rating (or ratings) he or she wants to see in the report. In order to do so, we will configure a data set and then define and explain its properties. Among them we will find: Name, Label, Value Type, and Display Type.
Afterwards, we will modify our main data set, RatingFilms, so that it takes its values according to the recently created parameter.
- Firstly, we will perform a preview on the report and explain how to properly present parameters to the user. We will add a parameter so that the user can select the rating (or ratings) he or she wants to analyze. We will go to the Data tab, select Parameters, right-click on it and select the Add Parameter... option, as shown in the following screenshot:
We will be presented with the following window, in which we will create and modify our parameters:
To the left, we find the data sets currently being used in our report. To the right, we see the parameters edition form.
-
We create a new data set so that it obtains the ratings list via a SQL query. On the right-hand side of the window, we will press the
button and then select the JDBC option:
On the left-hand side, we will select the connection to our sakila database, and then create a new query (
) on which we will configure the following:
- Query Name =
ListOfRatings - Query =
SELECT DISTINCT film.rating FROM film ORDER BY 1
- Query Name =
- To continue, we will press the OK button. Now, based on the ratings list we will configure the parameter. On the left-hand side we will select the data set we just created (ListOfRatings), and then head to the right-hand side on which we will configure the following:
- Name =
SelectRating: This is the name of the parameter - Label =
Ratings: This is the label that will be shown to the user - Value Type =
String: This is the type of value of the field with which we will be working - Mandatory =
True: This establishes whether it's optional, or not, for the user to select a value from the list before executing the current report. - Display Type =
Multi Selection Button: This is the way in which the list will be exposed to the user - Query =
ListOfRatings: This is the data set to be used - Value =
rating: This is the value that will be returned by the parameter - Display Name =
rating: This is the value to show to the final user
As we can observe, both Value and Display Name have the same value. Their use becomes important when the value we want to obtain is different from the value we want to show to the user. For example, suppose we have a list of films from which we want the user to select one. In this case we would have to set Value to the ID of the film, and Display Name to the title of the film. In this way, the user would select a film by its title, but we will get the ID of the selected film to use in our query.
- Name =
- To continue, we will press the OK button. What we will do now is modify the SQL query of our data set so that it obtains the ratings dynamically. We will head to the Data tab, select RatingFilms, right-click on it and then select the option Edit Query...:
We will modify the query so that it looks 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) = 2005 AND MONTH(payment.payment_date) = 6 GROUP BY film.film_id ORDER BY film.rating, film.titleWhat we did was replace the code
'G','PG','PG-13','R','NC-17'with the code${SelectRating}, so that the condition of our query is adjusted to the values selected in the parameter we just created. - We will press OK to continue. If we perform a preview, we should see the following:
When we created the SelectRating parameter, we did not specify a default value. This is why no ratings are selected by default. To modify the default value of our parameter, we should either use the Default Value or Default Value Formula option.
Furthermore, when we created our parameter we configured it to be mandatory. This is why the report will not return any data until a value is selected. The * we can see the left of ratings hints about this behavior.
As can be seen in the following screenshot, PRD presents all our parameters in the upper section of the window. It also provides a few options:
- Auto-Update on selection: If this option is activated, any modification on the selected values will trigger a report recalculation, which is also known as live-update.
- Update: This button is used when Auto-Update on selection is disabled, and its function is to manually trigger the recalculation of our report with the current values selected in our Parameters.
We will now select a pair of ratings values, for example G and PG:
After performing such a selection, the value returned by our SelectRating parameter will be 'G','PG', generating a SQL query like this:
WHERE film.rating IN ('G','PG')We created a parameter with the name SelectRating and defined its data set so that it obtains a list of every rating available from the sakila database. Then we configured the properties of SelectRating and among other things defined its Name and Label attributes, set it to Mandatory, chose the display type, and specified its Value and Display Name properties.
Later on we modified our data set so that the RatingFilm attributes takes into account the values selected by the user; that is, we replaced the static filter in our condition with the code ${SelectRating}.
Finally, we performed a preview of our report and explained the way in which parameters are presented, as well as the options Auto-Update on selection and Update.