Generating reports in BIRT

In the first article on BIRT technology, there was made a simple report template that displays some data from the database in a tabular form. This is a simple database, which is used in a small web application. The database contains information about events, such as: name, description, date and time, information about the author (the user who added the event) and the theme of the event (for example, “Sport”, “Culture”, “Education”, etc.).

The scheme of this database is shown in the screenshot below:

The template of the report is as follows:

This template is very simple in its structure and does not demonstrate the most features of this technology.

In this article, let’s consider some features for making more complex and detailed reports, such as grouping, aggregating, data formatting, parameter passing.

It is very convenient to use the data grouping in the report for better presentation of information. For example, you can select any groups in the data set. Also, it is possible to group the events according to themes, so a specific group can be seen, like events related with sports, for example. And within this group you can select one more group – the author of the event. In this case, the structure of the report can be described using this scheme:

Now try to implement this in BIRT. Use a template from the first article as a basis, just with slightly changed request (the receiving of the value of the event subject identifier was added).

In order to add the grouping to the report, you need to move the cursor to the table, and click Table icon (after pointing the table, this icon will appear on the left under the table). Then, right-click on the “Detail Row” line and select “Insert Group”.

In the dialog box, specify the group name (“Name” field) and the field by which the grouping will take place (“Group On” field). Click OK.

After performing this operation, there can be some changes in the template, e.g., there are new lines responsible for the header and footer of the added category.

Delete [theme_id] field, which was automatically substituted into the template while its creation, instead it put [theme_name]

Similarly, add the second grouping, this time for [user_login] field.

Now make [theme_name] and [user_login] fields displayed at the same level as the data from “Detail Row”. To do this, select the cell that contains the given field (note, you need to select the cell, not the Data element contained in it). Then, in the property’s editor on the General tab, change the value of the Drop property from None to Detail.

There are two columns in the report, which contain information about the time and date of the event. Let’s say that the way these fields are reported is not satisfying, and this information should be displayed in one cell, in the format “dd.mm.yyyy, hh: mm”.

To do this, delete the content of [event_date] cell, and then right-click → Insert → Text.

Then, with the help of <VALUE-OF></VALUE-OF> tag, you can dynamically substitute values ​​from the necessary fields of the data set.

For date and time formatting, use format attribute. For dates, the value of this attribute is “Short Date” (dd.mm.yyyy), for time it’s “Short Time” (hh: mm).

Please, note: it is necessary to change the type from Auto to HTML. If this cannot be done, the expression will be just displayed, while reviewing the report.

After performing these operations, the only thing you need is to delete the column which contains [event_time] element and change the width of the column with the new formatted date.

To do this, select the column, in the property’s editor go to the Advanced tab (it is at the bottom) and change the Width property, setting its value to 100 pixels.

As a result, the template looks like this:

Groups are usually added in order to derive any summary information about the group. For example, add a count of the events number for each theme. To do this, in first_group header, in the same column as the event name, add “Aggregation” field (right-click on the cell → Insert → Aggregation).

Set the name of aggregation (“Column Binding Name” field), select COUNT function in “Function” field, and in “Expression” field set the binding to event_name field from the Data Set. Press OK.

As a result, you can see that the cell has the element Aggregation.

Combine all the cells of the footer into one for better view:

Then set the left margin equal to 25pt:

Add borders at the top and bottom ( Width property is set to Thin ):

And set the background color:

As a result, the template looks like this:

Run the report. To do this, click the “View Report” button on the toolbar:

On the web browser, a new tab with the report will be opened:

The numbers 1, 3, 2, etc. in the report results of each group may not be entirely clear to a stranger. Make a conclusion of the results for each group in the format: “Total news by theme: number“.

To do this, replace Aggregation element with Text first. Using an already known <VALUE-OF></VALUE-OF> tag, add a dynamic display of news quantity in the group.

Important to note: in the expression row[“Aggregation”], “Aggregation” is the name of the aggregation, set in the “Column Binding Name” field while creating it, so if you set another name, you need to use your own.

Save changes. Now the report template looks like this:

The result after running:

Quite often, the report needs to be configured on the basis of some variable parameters. In order to make BIRT report parameterized, perform the following steps:

1) There is “Report Parameter” category in Data Explorer panel. Right-click on this category and select “New Parameter“.

2)  In the opened dialog, specify a name of the parameter (“Name” field), a type (“Data type” field), a format (“Format as” field) and a default value (“Default value” field).

Report the date, events before which are not interesting to us. Therefore, Date will be a type, and the default value will be the current date. The date is in “Short Date” format.

Then go to the properties of Data Set. To do this, open the “Data Sets” category in “Data Explorer” tab, double-click the data set, move to Parameters tab, click New.

In the opened dialog set a parameter name (“Name” field), type (“Data type” field), and bind it with the report parameter created in the previous step (in “Linked to Report Parameter” drop-down list choose the created parameter):

Save it and go to Query tab. Change SQL query by adding a condition of events selection (line 11):

After these steps, run the report and see what you get. After running the report, BIRT displays the standard window for entering parameters. Do not change anything, click OK.

As a result, there is a report with an only record:

Try to enter another value:

During BIRT integration into an existing web application, a necessity to write your own parameter input page can occur. For these purposes, there is a tag library in BIRT. It describes a parameter page. Connect .tld file to JSP page, and describe the parameter’s page with special tags.

The .tld connection takes place in the 4th line. In the 13th line, the parameter page is declared using parameterPage directive, which is declared in the same .tld file, which has been connected before. Also, by means of reportDesign attribute, specify which template the parameter page belongs to. And by means of the pattern attribute specify the way of displaying the report (frameset, preview, run, etc.). In the 14th line, describe the element for data entry. In order to connect the input field and the report parameter, an attribute name of the field should be the same as a name in the report parameter.

Then, parameter’s page can be used similar to the one provided by BIRT. After running the parameters page, you can specify the value of the parameter, and after clicking “Run Report” button the report will be performed.

Quite often it is better to display any information not in a tabular form, but as visualization, in particular, as charts. BIRT also provides such an opportunity.

As an example, display a diagram, which displays statistics for 5 categories with the biggest amount of events.

In order to create a chart, right-click on an empty space in the report template → Insert → Chart. In the appeared dialog, in “Select Chart Type” panel select Pie and click Next button.

On the next tab, configure a data set to fill the graph. Take the data from the database, so in the “Select Data” group, select the “Use Data From” radio button:

Then, select “New Data Set” in the drop-down list:

The dialog window for creating a new Data Set will be opened then. In the “Data Set Name” field, enter the name of the data set and click Next:

Now generate a SQL request to get data from the database. There are two columns in the set: theme_name with the name of the theme and events_count with the number of events on this theme. Write down the request and click Next:

Then, specify responsibilities of every column. For “Slice Size Definition” group (1), select events_count from the drop-down list, further select “Category Definition”(2) from theme_name list:

Next, configure the filter. To do this, click Filters button and in the opened dialog window click Add:

Configure a filter in this dialog window. Make sure, that there are themes in the set with the largest number of registered events. To do this, select “events_count” in the first field, select the function “Top n” in the drop-down list, and enter the last field the number of records, in this case it is 5:

All the necessary steps for data set configuring for the report. Click Next and go to the last tab, named Format Chart. Make some visual changes in the report displaying, change a chart title, add a title to the legend, and display the number of events for each category.

To change a chart title, go to “Title” and enter a new name in “Char Title” field:

Next, go to Legend tab, and in “Title” field, enter the name that will be displayed above the legend:

Then, set the display of events quantity for each category. To do this, go to Value Series tab, and click Labels button:

Set Inside value in Position field, Format group of the opened dialog:

The following information occurs in the preview window after completing all the steps:

Run the report: