Tutorial 2: Grouping report data
This tutorial provides instructions for grouping customer data by credit limit. It starts with the report you built in Tutorial 1: "Building a simple listing report." In the first tutorial, you built a simple report that listed customers in alphabetical order. In this report, you organize customers into credit limit groups of $50,000.00, for example, 0 - 49999, 50000 - 99999, 100000 - 149999, and so on.
Before you begin this tutorial, you must complete the first tutorial.
In this tutorial, you perform the following tasks:
Task 1: Open the report design
In the first tutorial, you created Customers.rptdesign in a project folder named My Reports.
- Open Customers.rptdesign, using one of the following procedures:
- If you are using BIRT Report Designer, open the file through Navigator.
- If necessary, open Navigator by choosing Window->Show View->Navigator.
Navigator shows all the project folders and report files you create.
- Navigate to the My Reports folder, then double-click Customers.rptdesign.
- If you are using BIRT RCP Report Designer, choose File->Open Report, find Customers.rptdesign and open it.
The file opens in the layout editor.
Task 2: Save the report as a new file
Rather than editing directly the report you created in the first tutorial, save Customers.rptdesign as a new file.
- Choose File->Save As.
Save As appears. It displays the file's current name and location.
- For File name, change Customers.rptdesign to Customers_grouped.rptdesign. Choose Finish.
BIRT Report Designer makes a copy of Customers.rptdesign. The new file appears in the layout editor.
Task 3: Add the credit limit field to the data set
In order for the report to display credit limit data, you must add the creditLimit field to the data set.
- Choose Data Explorer.
- Expand Data Sets, then double-click Customers.
Edit Data Set displays the SQL query for the Customers data set.
- Add a comma (,) after phone.
- On the next line, add the following text:
The modified query should look like the one in the following illustration.
- Choose Preview Results to verify that the query returns rows with credit limit information, then choose OK.
Task 4: Add credit limit data to the report
In this procedure, you insert the creditLimit field in the existing table.
- In the layout editor, select the table.
Guide cells appear at the top and left side of the table.
- Right-click the guide cell above the first column, then choose Insert->Column to the Left, as shown in the following illustration.
A new column appears.
- In Data Explorer, expand Data Sets--Customers.
- Drag the creditLimit field from Data Explorer and drop it in the cell next to row["CustomerName"].
The table shows the expression, row["creditLimit"], of the field you added. It also shows the label element that the layout editor automatically added to the header row. This label serves as the column heading and displays the field name as static text.
The report should look like the one in the following illustration.
- Preview the report. The report should look like the one in the following illustration.
Some of the customers have a credit limit of 0. These are new customers who have not yet been approved for a line of credit.
Task 5: Group customer data by credit limit
The report is currently sorted alphabetically by customer name. In this procedure, you group the data by credit limit in intervals of 50,000.
- Choose Layout to return to the layout editor.
- Right-click the table and choose Insert Group->Above.
New Group appears.
- Follow these steps to specify grouping by credit limit in intervals of 50000:
- For Name, specify the following text as the group name:
- For Group On, select creditLimit from the drop-down list.
- For Interval, select Interval from the drop-down list.
- For Range, specify 50000.
- Use the default values for the other options.
- Choose OK.
The table in the report design displays a group header and a group footer row. It also shows the data element that the layout editor automatically added to the group header row. This data element serves as the group heading and, in the generated report, displays the first credit limit value of each group.
- Select the data element, row["creditLimit"], that appears in the group header row. Do not select the row[''creditLimit''] element that appears in the detail row.
- In Property Editor, choose General, then choose B to format the group heading as bold text.
- Preview the report. Scroll down the report to view all the data.
The report organizes data into four credit limit groups. At the beginning of each group, you see the following numbers in bold: 0, 51600, 100600, 210500. These numbers match the first credit limit value of each group.
Task 6: Display credit limit ranges in the group header
Rather than display the first value of each group in the group header, the report is easier to navigate if it displays the credit limit range for each group, as follows:
This procedure shows how to write JavaScript code to display these credit limit ranges.
- Choose Layout to return to the layout editor.
- Double-click the data element, row["creditLimit"], in the group header.
Expression Builder appears. The field name expression appears in the top of the window.
- Replace the expression with the following JavaScript code:
- Choose OK.
- Preview the report. The group headers display the credit limit ranges, as shown in the following illustration.
Task 7: Display aggregate information
One of the benefits of grouping data is that you can add summary, or aggregate, information at the beginning or end of each group. In this procedure, you complete the following tasks:
- Display the number of customers in each group.
- Display the number of all customers listed in the report.
- Choose Layout to return to the layout editor.
- Display the number of customers in each group:
- Drag a data element from the palette and drop it in the first cell in the group footer row. Elements placed in the group footer appear at the end of every group.
Expression Builder appears.
- Expand BIRT JavaScript Objects, then expand Total.
The list of aggregate functions appears.
- Specify the following expression:
You can type the full expression, or double-click the count( ) function to insert it in the expression area.
Total.count( ) returns the number of rows. Because you place the expression in the group footer, the function counts the number of rows in each group.
- Choose OK.
- Preview the report. The report displays the number of customers at the end of each group, as shown in the following illustration.
- Choose Layout to return to the layout editor.
- Display the number of all customers:
- Select the table.
Guide cells appear at the top and left side of the table.
- Right-click the guide cell on the left of the first row, Table - Header, then choose Insert->Row->Above.
A new table header row appears above the row that displays the column headings. It appears in color because it inherited the properties of the row below it.
- Select the new row, and in Property Editor, change its background color to white.
- Drag a data element from the palette and drop it in the first cell in the new table header row. Elements placed in the table header appear at the beginning of the section.
Expression Builder appears.
- Specify the following expression:
- Choose OK.
The report design should look like the one in the following illustration.
- Preview the report. The report displays the number of customers at the beginning of the table, as shown in the following illustration. Because you placed the Total.count( ) expression in the table header, the function counts the number of rows in the table.
Task 8: Format the report
Now that the report displays the correct data, you can focus on improving the report's appearance. You perform the following tasks in this section:
Remove credit limit data from the detail rows
To verify that data appears in the correct credit limit groups, it is useful to display each customer's credit limit. Now that we have verified the data, we can delete the individual credit limit information from the report.
- Choose Layout to return to the layout editor.
- Delete the data element, row["creditLimit"] from the detail row.
- Preview the report. It should look like the one in the following illustration.
Display group headings on the first row of each group
The credit limit group headings appear in their own rows above the details rows of each group. In this procedure, you drop the group headings so that they appear in the first detail row.
- Choose Layout to return to the layout editor.
- Select the cell that contains the group heading, as shown in the following illustration. Be sure to select the cell and not the data element in the cell.
- In the General properties of Property Editor, set Drop to Detail.
In the report design, the group heading still appears above the detail row because, technically, the element is still in the group header row. The Drop property specifies how BIRT displays the group heading in the generated report.
- In the General properties of Property Editor, set Padding--Top to 12 points.
This setting aligns the group headings' ranges more precisely with the detail rows' data.
- Preview the report. The group headings appear in the first row of each group.
Separate each group with a line
Drawing a line to separate each group makes it easier to see the groups of data.
- Choose Layout to return to the layout editor.
- Select all the cells in the group footer row. To select multiple cells, use Shift- click.
- Choose Border in Property Editor, then set the border properties:
- Set Style to a solid line.
- Choose the button that shows the bottom border.
- Add more space between the line and text above it. While the cells are still selected, choose the Padding properties in Property Editor, then set Bottom to 6.0 points.
- Preview the report. Lines appear at the end of each group, as shown in the following illustration.
