Tutorial 2: Grouping report data
This tutorial provides instructions for grouping customer data by credit limit. It uses the report that 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, such as 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:
- 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, use the main menu to open the file:
- Choose File->Open File.
- Navigate to and select Customers.rptdesign, then choose Open.
The file opens in the layout editor, as shown in Figure 8-1.
Figure 8-1 Customer report design in the layout editor
Task 2: Save the report as a new file
Rather than editing directly the report that you created in the first tutorial, save Customers.rptdesign as a new file.
- Choose File->Save As. Save As displays the file's current name and location.
- For File name, change Customers.rptdesign to Customers_grouped.rptdesign, then 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.
- In the query, add a comma (,) after phone.
- On the next line, add the following text:
The modified query should look like the one shown in
Figure 8-2.
Figure 8-2 Query with creditLimit field added
- Choose Preview Results to verify that the query returns rows with credit limit information.
- Choose OK to save the data set.
Task 4: Add credit limit data to the report
In this procedure, you insert the credit limit 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 Figure 8-3.
Figure 8-3 Inserting a column to the left of an existing column
A new column appears.
- In Data Explorer, expand Data Sets, then expand Customers. The Customers data set displays the fields specified in the query.
- Drag the CREDITLIMIT field from Data Explorer, and drop it in the detail row cell next to [CUSTOMERNAME].
In the layout editor, the table displays the field that you added. The table 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 shown in
Figure 8-4.
Figure 8-4 Result of adding the credit limit field in the layout editor
- Edit the CREDITLIMIT label so that it appears as Credit Limit.
- Preview the report. The report should look like the one shown in Figure 8-5.
Figure 8-5 Report preview, showing the result of adding credit limit
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. Recall that in the first tutorial, you specified that the rows in the table be sorted by customer name. In this procedure, you group the data by credit limit in intervals of 50,000. When you group data, BIRT sorts the rows into groups first, then it sorts the rows within each group, assuming that you also specify a sort condition at the table level. As you will see when you complete this task, the data rows within each credit limit group will be sorted by customer name.
- Choose Layout to return to the layout editor.
- Right-click the table, and choose Insert Group->Above. New Group, shown in Figure 8-6, displays the group properties you can set.
Figure 8-6 New Group
- Follow these steps to specify grouping by credit limit in intervals of 50000:
- For Name, type 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, type 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, as shown in
Figure 8-7. The table 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.
Figure 8-7 Group header and group footer rows in a report design
- Select the [CREDITLIMIT] data element that appears in the group header row. Do not select the [CREDITLIMIT] data 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, 61100, 113000, 227600. These numbers match the first credit limit value of each group. Within each group, customer names are sorted in alphabetical order. Figure 8-8 shows one of the four credit limit groups.
Figure 8-8 Report preview, showing one of the four credit limit groups
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 a JavaScript expression to display these credit limit ranges. The procedure also shows how to create a column binding with which to associate the JavaScript expression.
- Choose Layout to return to the layout editor.
- Delete the [CREDITLIMIT] data element in the group header, and insert a new data element in its place.
New Data Binding, shown in
Figure 8-9, prompts you to create a column binding for the new data element.
Figure 8-9 New Data Binding
- On New Data Binding, provide a name and expression for the column binding:
- In Name, type the following name:
- Open the expression builder to write a JavaScript expression.
- In the expression builder, type the following expression:
Choose OK. The expression appears in the Expression field on New Data Binding, as shown in
Figure 8-10.
Figure 8-10 Column binding defined
- Choose OK to save the column binding for the data element.
In the layout editor, the data element displays the column binding name, [CREDIT_GRP_HEADER].
- Select the data element. In Property Editor, choose General, then choose B to format the group heading as bold text.
- Preview the report. The group headers display the credit limit ranges. Figure 8-11 shows the 50000 - 99999 group header.
Figure 8-11 Report preview, showing credit limit ranges
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 add the following aggregate information to the report:
- The number of customers in each group
- The number of all customers that are listed in the report
Display the number of customers in each group
- Choose Layout to return to the layout editor.
- In the palette, under Quick Tools, drag an aggregation element and drop it in the second cell in the group footer row. Elements that are in the group footer appear at the end of every group.
- On Aggregation Builder:
- In Column Binding Name, type the following name:
- In Function, select COUNT.
- In Data Field, select CUSTOMERNAME. The expression, dataSetRow["CUSTOMERNAME"] appears in the field.
- In Aggregate On, select Group and credit_group. This value indicates that the COUNT function returns the number of customers in each credit limit group.
Figure 8-12 shows the complete definition for the aggregation element.
Figure 8-12 Aggregation Builder, displaying values for getting the
count of customers in each credit limit group
- Choose OK. In the report design, shown in Figure 8-13, the aggregation element displays the sigma symbol followed by the column binding name.
- Preview the report. The report displays the number of customers at the end of each group. Figure 8-14 shows the number of customers, 37, for the first credit limit group.
Figure 8-14 Report preview, showing a count for each group
- Choose Layout to return to the layout editor.
- Instead of displaying just the number of customers in each group, display the following text before the total:
- Drag a text element from the palette, and drop it in the first cell in the group footer row.
- On Edit Text Item, select HTML from the drop-down list that displays Auto.
- Specify the following text in the text area, shown in Figure 8-15.
In a text element, the <VALUE-OF> tag supports the display of dynamic data. The expression, row["GRP_TOTAL_CUSTOMERS"], is a reference to the column binding you created to return the number of customers in each credit limit group.
- Choose OK to save your edits to the text element.
- Preview the report. Scroll to the bottom of the first credit limit group. The report should look like the one in Figure 8-16.
Figure 8-16 Report preview, showing the text displayed by the text
element
The group footer displays the following information:
- Choose Layout to return to the layout editor.
- Delete the aggregation element from the second cell in the group footer row to remove the second total, which is now redundant.
Display the number of customers in the report
In the previous procedure, you added aggregate data by inserting the aggregation element from the palette. This time, you add aggregate data by creating a column binding through the table's Binding page.
- Select the table.
- On Property Editor, choose the Binding tab.
The Binding page, shown in
Figure 8-17, displays all the column bindings used by elements in the table. The right side of the page displays buttons that represent the actions you can perform with each column binding.
Figure 8-17 Binding page, displaying the column bindings
- Choose Add Aggregation to create a new column binding that defines an aggregate expression.
- On Aggregation Builder, specify the values shown in Figure 8-18.
- In Column Binding Name, type the following name:
- In Function, select COUNT.
- In Data Field, select CUSTOMERNAME. The expression, dataSetRow["CUSTOMERNAME"] appears in the field.
- In Aggregate On, select Table. This value indicates that the COUNT function returns the number of customers in the table.
Figure 8-18 Aggregation Builder, displaying values for getting the
count of customers in the table
- Choose OK.
The new column binding, TOTAL_CUSTOMERS, appears on the Binding page, and is available for use by any element in the table.
- 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 that follows it.
- Select the new row. In Property Editor, choose General, and set 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 that are in the table header appear at the beginning of the section.
- On New Data Binding, create a new column binding:
- In Column Binding Name, type the following name:
- Use the default data type, Any.
- In Expression, choose the expression builder button.
- In the expression builder:
- Type the following text:
- In the lower pane of the expression builder, select Available Column Bindings, select Table, then double-click TOTAL_CUSTOMERS, the column binding you created to get the number of customers in the table.
The expression, row["TOTAL_CUSTOMERS"] appears after the text you typed in the text area, as shown in
Figure 8-19.
Figure 8-19 The expression builder, displaying the expression that
refers to the TOTAL_CUSTOMERS column binding
- Choose OK.
The expression appears in the Expression field on New Data Binding, shown in
Figure 8-20.
Figure 8-20 Column binding, defined
- Choose OK to save the column binding.
The report design should look like the one shown in
Figure 8-21.
Figure 8-21 Report design, showing a total count for customers
- Preview the report. The report displays the number of customers at the beginning of the table, as shown in Figure 8-22.
Figure 8-22 Report preview, showing a total number of customers
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 [CREDITLIMIT] data element from the detail row.
- Preview the report. It should look like the one shown in Figure 8-23.
Figure 8-23 Report preview, without credit limit data for each row
Display group headings on the first row of each group
The credit limit group headings appear in their own rows, above the detail rows of each group. In this procedure, you drop the group headings so that they appear in the first detail row of each group.
- Choose Layout to return to the layout editor.
- Select the cell that contains the group heading, as shown in Figure 8-24. Be sure to select the cell and not the data element in the cell.
Figure 8-24 Group heading cell selected
- 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.
- Preview the report. The group headings appear in the first row of each group, as shown in Figure 8-25.
Figure 8-25 Report preview, showing dropped group headings
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, and set Bottom to 6.0 points.
- Preview the report. A line appears at the end of each group, as shown in Figure 8-26.
Figure 8-26 Report preview, showing a line between groups
Task 9: Preview the report in the BIRT report viewer
So far, you have been checking the report output in the BIRT Report Designer previewer. This time, you will use the report viewer to see what the report looks like when it is deployed. The report viewer provides additional functionality, including the capability to navigate to specific sections of a report using a table of contents. When you create groups in a report, BIRT automatically generates a table of contents, using the group values to show the hierarchy of the report.
- Choose File->View Report->View Report in Web Viewer. The report appears in the report viewer.
- Choose the table of contents button, the left button at the top of the window, to display the table of contents. The table of contents displays the first value in each of the four credit limit groups. When you select a value, the report displays the corresponding section of the report. If you select 61100, for example, the report shows the customer rows in the 50000 - 99999 credit limit range, as shown in Figure 8-27.
Figure 8-27 Select a value in the table of contents to view the corresponding
data
Task 10: Display credit limit ranges in the table of contents
Rather than display the first value of each credit limit group, the table of contents makes more sense if it displays the same credit limit range values
as the report. You accomplish this task by using the same JavaScript expression that you used previously to display credit limit ranges (0 - 49999, 50000 - 99999, and so on) in the group header.
- Return to BIRT Report Designer.
- In the layout editor, select the table, then choose the Groups tab in Property Editor.
- Double-click credit_group in the list of groups. Edit Group, shown in Figure 8-28, displays the properties of the group. Under Table of Contents, Item Expression is set, by default, to the grouping field, row["CREDITLIMIT"].
Figure 8-28 Edit Group, displaying the properties of credit_group
- Choose the expression builder button, and in the expression builder, replace the row["CREDITLIMIT"] expression with the following expression. This expression is the same column-binding expression that is used by the data element, [CREDIT_GRP_HEADER], in the group header. Rather than typing the expression again, you can copy it from the data element and paste it here.
- Choose OK.
- Preview the report in the report viewer to verify the change in the table of contents. The table of contents displays the credit limit ranges, as shown in Figure 8-29.
Figure 8-29 Updated table of contents


|