Time for action – creating a new report

The following are the steps for creating a new report:

  1. In the following section, we will 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. Then we will create a Parameter to choose the country we want to analyze. Finally, we will adapt our report to the layout proposed in this guide. We create a new report and save it with the name, 13_Adding_Subreports.prpt. We create a JDBC data set and configure it as follows:
    • Connections = sakila db
    • Query name = CountryCity
    • Query =
        SELECT DISTINCT country.country_id, country.country, city.city_id, city.city 
  FROM country
  INNER JOIN city ON city.country_id = country.country_id
  INNER JOIN address ON address.city_id = city.city_id
  INNER JOIN customer ON customer.address_id = address.address_id
  INNER JOIN rental ON rental.customer_id = customer.customer_id
  WHERE country.country_id = ${SelectCountry}
  ORDER BY country.country
  2. We create a new Parameter and its corresponding JDBC data set is as follows:
    • Query Name = ListOfCountries
    • Query =
        SELECT DISTINCT country.country_id, country.country
  FROM country
  WHERE country.country_id IN (20,24,29,34,48,67,74)
  ORDER BY country.country

    Now we configure the Parameter as follows:

    • Name = SelectCountry
    • Label = Country:
    • Value Type = Number
    • Default Value = 20; this value is arbitrary and represents Canada
    • Mandatory = True
    • Display Type = Drop Down
    • Value = country_id
    • Display Name = country

    Now we will recreate the layout of the following image. After doing this, we will be ready to start the interesting part of this chapter, Subreports!

    Time for action – creating a new report
  3. In the Report Header section, we place an Attributes.value label with the text Adding Subreports.
  4. In the Group Header section, we place a rectangle and the field country, and configure their formats and colors. We expand the area of this section so it is similar to the layout presented earlier. This is where we will place our first Subreport.
  5. In the Details section, we place a rectangle and the field city, and, as before, we configure their formats and colors. We should also expand the area of this section, as this is where we will add our second Subreport.
  6. Finally, we go to the Structure tab, select the Master Report, go to Group node, and configure the following:
    • Attributes.group = [country_id]

What just happened?

We created a new report and named it 13_Adding_Subreports.prpt. We created a JDBC data set and configured its connection and SQL query so that it returns the countries and cities where films have been rented. We created and configured the Parameter SelectCountry. The layout that our report should have was presented, and with this in mind, we took the necessary steps to adapt our report to the guide's requirements.

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

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