Time for action – creating and configuring the second Subreport

We will create another Subreport, but this time we will place it in the Details section so that it is executed for each row. Then we will edit the internally used Parameters and import the value of the field city_id. We will create a JDBC data set that will be filtered by the value imported from the principal Report. Finally, we will configure the layout of the Subreport. The steps for creating and configuring the second Subreport are as follows:

  1. We add a Sub-report object (Time for action – creating and configuring the second Subreport) to the Details section. We drag-and-drop it to the area that we have left empty.
  2. After doing this, PRD will ask us if we want the Subreport to be Banded or Inline. We should click on the Inline button.
  3. When PRD asks us about the data set to be used, we click on the option Cancel.
  4. We go to the Structure tab, choose the sub-report node, and configure the following:
    • Attributes.name = SubReportTable
  5. This Subreport will display information related to different cities that belong to the country chosen by the user. To make this happen, we need to make the Subreport receive the city that the main Report is currently analyzing.
  6. We go the Data tab, select the Parameters node, right-click on it, and choose the option Edit Sub-report Parameters....
  7. In this case, the value to be imported is the city identification (city_id).

    In the table on the left-hand side (Import Parameter), we click on the Time for action – creating and configuring the second Subreport button to create a new row and then establish the following:

    • Outer Name = city_id
    • Inner Name = importCityId
  8. Now we create the data set for this Subreport. It will be a JDBC data set, and we will configure it as follows:
    • Connections = sakila db
    • Query Name = Actors
    • Query =
      SELECT actor.actor_id, CONCAT(actor.last_name,", ",actor.first_name) AS full_name, SUM(payment.amount) 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
INNER JOIN customer ON customer.customer_id = rental.customer_id
INNER JOIN address ON address.address_id = customer.address_id
INNER JOIN film_actor ON film_actor.film_id = film.film_id
INNER JOIN actor ON actor.actor_id = film_actor.actor_id
WHERE address.city_id = ${importCityId}
GROUP BY actor.actor_id
ORDER BY sum_amount DESC
LIMIT 0,10
  9. We will configure the size of the Sub-report in our report. Then we will open the Subreport and add and configure a small table. We go to the main Report and enlarge the area covered by the Sub-report object so that it fills the empty space as shown in the following screenshot:
    Time for action – creating and configuring the second Subreport
  10. We go to the Subreport SubReportTable and begin to design it. We create the following layout:
    Time for action – creating and configuring the second Subreport
  11. In the Details Header section, we place two labels and a horizontal line and configure them according to the previous image.
  12. In the Details section, we place the fields full_name and sum_amount and configure them according to the previous image.
  13. In the Details Footer section, we place a horizontal line and configure this as well according to the previous image.

    If we preview it, we will see the following screenshot:

    Time for action – creating and configuring the second Subreport

What just happened?

We added a Sub-report object to the Details section and configured it to be of the inline type. We created an internally used Parameter called importCityId that will take on the value of the city_id field in the main report. Then we created the Subreport's data Set and configured it so the SQL query is filtered by importCityId.

In the main Report, we enlarged the size of the Sub-report object, SubReportTable. Then in SubReportBarChart, we created a table as indicated in the guide. This table displays a list of actors and amounts.

Have a go hero

Now we propose that you modify the report you have already created, and that you add a Subreport to show the country information. Within this Subreport, you will create a data set that will be filtered by the values taken from the main Report, and the information will then be represented by a Scatter Plot chart. The first page of the report will display two pre-existing charts, and the second page will display the Subreport as shown in the following screenshot:

Have a go hero

The steps you should follow, broadly speaking, are as follows:

  1. Create a copy of the report 11_Adding_Charts.prpt and save it with the name 14_Adding_Subreports_Plus.prpt.
  2. Place a Sub-report object in the Report Header section under the last chart.
  3. Go to the Subreport, create a JDBC data set, and configure it as follows:
    • Connections = sakila db
    • Query Name = CountryAmount
    • Query =
      SELECT country.country, 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 customer ON customer.customer_id = payment.customer_id
INNER JOIN address ON address.address_id = customer.address_id
INNER JOIN city ON city.city_id = address.city_id
INNER JOIN country ON country.country_id = city.country_id
WHERE year(payment.payment_date)=${importSelectYear}
AND month(payment_date) = ${importSelectMonth}
GROUP BY country.country
ORDER BY sum_amount DESC
LIMIT 0,30
  4. Go to the Subreport and add a chart object to the Report Header section.

Pop quiz – Subreports

State whether the following are true or false.

Q1. Within a Subreport, you cannot add other Subreports.

Q2. The behavior and general use of a Subreport is similar to a normal report except for a few exceptions.

Q3. Within a Subreport, Parameters can be defined so that the end user can choose a value.

Q4. When importing Parameters from a Subreport, if the name of the variable that contains the value to be imported is country_id, the name of the variable that will receive this value can also be named country_id.

Q5. If a Sub-report object is placed in the Report Header section or the Details section, its behavior will be the same regardless of the design of the main Report.

Q6. If we add a banded type Sub-report, we will not be able to modify its size or position.

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

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