Designing a report

[日本語]

by Giulio Toffoli

In this tutorial, we cover the basics of how to design a report. The report creation process has three main steps:

  1. Creating a data source or a database connection used to fill the report.
  2. Designing the report, including the layout of its elements and parameters to represent the data.
  3. Runing the report, which includes compiling the JRXML source file in a Jasper file and filling in the data for export or onscreen display.

To keep things simple, this tutorial uses a plain JDBC connection, which is a connection to a database. The data to print is retrieved using an SQL query embedded in the report.

Let's start by creating the connection. You can use any database and table you want, but if you want to test the same exact query and create the same report as the tutorial, you can use the sample database included with JasperReports. Once you download JasperReports (from here) and unpack the archive, go to the demo/hsqldb directory and run the following command:

java -cp ../../lib/hsqldb-1.8.0-10.jar org.hsqldb.Server

When the HSQLDB server starts up correctly, the terminal output appears as follows:

Figure 1 Figure 1

To create the new connection in iReport, open the datasources/connections dialog by clicking its icon in the tool bar, as shown in Figure 2.

Figure 2 Figure 2

Click New, choose Database JDBC connection as shown in Figure 3, and use the following settings for the sample HSQL database, as shown in Figure 4 below:

  Name: JasperReports Sample
  JDBCDriver: org.hsqldb.jdbcDriver
  Url: jdbc:hsqldb:hsql://localhost
  Username: sa
  Password: [none]

 

Figure 3 Figure 3
Figure 4 Figure 4

As said above, you can use your favorite database, in that case you have to provide the right URL, the JDBC driver and credentials. iReport proposes a list of well known database drivers. If the one you need is not present, just type the name. If it is present and it is highlighted in red it means that the driver is not available in the classpath and must be added. To do so, select Tools > Options > iReport > Classpath and add the required JARs to the classpath. Also, it's good practice to test the connection before saving it. When done, the new connection appears in the connections list and is automatically selected as the default, active connection:

Figure 5 Figure 5

Now, let's create the new report. Select File > New.

Figure 6 Figure 6

In the template chooser that appears, select a blank report and click Open this Template. Select a name and location to story the new file, click next and then finish. Your blank report now appears in the designer:

Figure 7 Figure 7

Click the button representing a database query in the designer tool bar:

Figure 8 Figure 8

This opens the Query dialog to define the query that retrieves data to fill the report. The SQL query is stored inside the report and can accept parameters to filter the data using the syntax $P{} (for standard parameters) and $P!{} (to use a parameter as SQL chunk). Again we want to keep things simple in this tutorial, so we will not use any parameters. Type the following query to select all the fields/columns in the orders table:

select * from orders
Figure 9 Figure 9

Of course, you can use any table for this example. The orders table is available in the sample database we are using. If you don't know SQL, you can use the visual SQL query builder by clicking the Query designer button. As you type the query, iReport executes it to extract all the fields (columns) available in the query. The fields are listed on the bottom portion of the window. If there is an error in the SQL, you'll get a message that explains what's wrong. When the query is valid, click OK. iReport lists all the discovered fields in the report inspectory, so they can be used in the report and displayed using text fields and other elements. To print the value of a field in the report, drag a field object from the report inspector to the design view. For example, drag a field into the detail band, a section of the report that is printed for each record/row in the query results. The title and the summary bands are printed just once in each report, while the page header and footer are printed once at the top and bottom of each page, respectively.

Figure 10 Figure 10

When the field object is dragged inside the detail band, iReport creates a textfield element and sets the textfield expression for that element to $F{SHIPNAME}. This is a simple expression to print the value of the field SHIPNAME (the syntax $F{}). Repeat the same operation for other fields and format the textfields element by stretching, dragging and aligning them. It is important that the textfields are all placed within the detail band. To add other elements (such lines or labels), drag them from the palette shown in Figure 11 into the designer view, then resize and arrange them as desired.

Figure 11 Figure 11

In this case, we add a label in the title band for the report title, we add column labels using label elements placed in the column header band, and we place a thin line just under the textfields in the detail band. Finally, we resize some bands and remove others by setting a their height to 0. Change the height of band by dragging its bottom edge. A shortcut to reduce the height of the band is to double-click on its bottom edge, this will set it to the bottom edge of its lowest element.

You can also set properties such as the height of the band in the property sheet under the palette. The property sheet lets you view and edit the properties of the currently selected element in the designer. Click on an element in the designer or in the report inspector to select it and view its properties. For bands, you can also click in an unused part of the band. Figure 12 shows the final design of the sample report.

Figure 12 Figure 12

To preview the report, switch to the preview mode by clicking Preview in the designer tool bar. The preview compiles the report in the background and fills it with data retrieved by the query through the JDBC connection. As shown in Figure 13, the detail band repeats for every row in the query results, creating a simple table report.

Figure 13 Figure 13

Congratulations, you have just created your first report! Now you're ready to export a report.

 

Feedback