## Tutorial 2: Grouping report data

This tutorial provides instructions for grouping customer data by credit limit. It starts with 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:

- Open the report design.
- Save the report as a new file.
- Add the credit limit field to the data set.
- Add credit limit data to the report.
- Group customer data by credit limit.
- Display credit limit ranges in the group header.
- Display aggregate information.
- Format the report.
- Preview the report in the BIRT report viewer.
- Display credit limit ranges in the table of contents.

### 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:

1. Open Navigator by choosing Window->Show View->Navigator. Navigator shows all the project folders and report files you create.
2. 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:

1. Choose File->Open File.
2. 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.

1. Choose File->Save As. Save As displays the file's current name and location.
2. 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.

1. Choose Data Explorer.
2. Expand Data Sets, then double-click Customers. Edit Data Set displays the SQL query for the Customers data set.
3. Add a comma (,) after phone.
4. On the next line, add the following text:

    creditLimit

The modified query should look like the one shown in Figure 8-2.

Figure 8-2 Query with creditLimit field added
5. Choose Preview Results to verify that the query returns rows with credit limit information.
6. 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.

1. In the layout editor, select the table. Guide cells appear at the top and left side of the table.
2. 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.
3. In Data Explorer, expand Data Sets, then expand Customers. The Customers data set displays the fields specified in the query.
4. Drag the CREDITLIMIT field from Data Explorer, and drop it in the detail row cell next to [CUSTOMERNAME].
BIRT Report Designer creates a named column, which is bound to the data-set field. Select Data Binding displays this column binding.
5. Choose OK to accept the default column binding.
In the layout editor, the table displays the field that 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 shown in Figure 8-4.

Figure 8-4 Result of adding the credit limit field in the layout editor
6. Edit the CREDITLIMIT label so that it appears as Credit Limit.
7. 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.

1. Choose Layout to return to the layout editor.
2. 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
3. Follow these steps to specify grouping by credit limit in intervals of 50000:

1. For Name, type the following text as the group name:

    credit_group

2. For Group On, select CREDITLIMIT from the drop-down list.
3. For Interval, select Interval from the drop-down list.
4. For Range, type 50000.
5. Use the default values for the other options.
6. Choose OK.

The table in the report design displays a group header and a group footer row, as shown in Figure 8-7. 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.

Figure 8-7 Group header and group footer rows in a report design
4. 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.
5. In Property Editor, choose General, then choose B to format the group heading as bold text.
6. Preview the report. Scroll down the report to view all the data. As Figure 8-8 shows, 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, customers are sorted in alphabetical order.

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:

    0 - 49999
    50000 - 99999
    100000 - 149999

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.

1. Choose Layout to return to the layout editor.
2. Select the table, and choose Binding on Property Editor.
3. Create a new column binding:

1. Choose Add.
2. In Expression Builder, type the following expression:

    for(i=50000; i<300000; i+=50000){
    if( row["CREDITLIMIT"] < i ){
      rangeStart = i-50000;
      rangeEnd = i-1;
      break;
    }
    }
    displayString=rangeStart + " - " + rangeEnd;

3. Choose OK. The new column binding appears on the binding page of Property Editor with the column name highlighted.
4. Replace the name New Binding with the following name:

    CREDIT_GROUP_HEADER

4. Update the CREDITLIMIT data element in the group header to use the new column binding by double-clicking the data element, and selecting CREDIT_GROUP_HEADER, then choosing OK.
5. Preview the report. The group headers display the credit limit ranges. Figure 8-9 shows the 50000 - 99999 group header.

Figure 8-9 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 complete the following tasks:

- Display the number of customers in each group.
- Display the number of all customers that are listed in the report.

1. Choose Layout to return to the layout editor.
2. Display the number of customers in each group:

1. Drag a data element from the palette, and drop it in the first cell in the group footer row. Elements that are in the group footer appear at the end of every group.
2. On Select Data Binding, create a new column binding:

1. On a new row, in Name, type the following text:

    GROUP_CUSTOMER_COUNT

2. Use the default data type, Any.
3. In Expression, type the following aggregate expression:

    "Customers: " + Total.count()

Total.count( ) returns the number of rows.
4. Use the default Aggregate On value, credit_group. This value indicates that the Total.count( ) function returns the number of rows in each credit limit group.
5. Select the column binding you just created, and choose OK. The data element uses the selected column binding.

3. Preview the report. The report displays the number of customers at the end of each group, as shown in Figure 8-10.

Figure 8-10 Report preview, showing a count for each group

3. Choose Layout to return to the layout editor.
4. Display the number of all customers:

1. Select the table.
Guide cells appear at the top and left side of the table.
2. 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.
3. Select the new row, and in Property Editor, change its background color to white.
4. 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.
5. On Select Data Binding, create a new column binding:

1. On a new row, in Name, type the following text:

    TOTAL_CUSTOMER_COUNT

2. Use the default data type, Any.
3. In Expression, type the following aggregate expression:

    "Number of customers: " + Total.count()

4. Use the default Aggregate On value, ALL. This value indicates that the Total.count( ) function returns the number of rows in the entire table.
5. Select the column binding you just created, and choose OK. The data element uses the selected column binding.

The report design should look like the one shown in Figure 8-11.

Figure 8-11 Report design, showing a total count for customers
5. Preview the report. The report displays the number of customers at the beginning of the table, as shown in Figure 8-12.

Figure 8-12 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.
- Display group headings on the first row of each group.
- Separate each group with a line.

#### 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.

1. Choose Layout to return to the layout editor.
2. Delete the [CREDITLIMIT] data element from the detail row.
3. Preview the report. It should look like the one shown in Figure 8-13.

Figure 8-13 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.

1. Choose Layout to return to the layout editor.
2. Select the cell that contains the group heading, as shown in Figure 8-14. Be sure to select the cell and not the data element in the cell.

Figure 8-14 Group heading cell selected
3. 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.
4. In the Padding properties of Property Editor, set Padding--Top to 12 points. This setting aligns the group headings more precisely with the detail rows' data. Remember that, in the first tutorial, we added a 12-point padding to the top of the detail row cells.
5. Preview the report. The group headings appear in the first row of each group, as shown in Figure 8-15.

Figure 8-15 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.

1. Choose Layout to return to the layout editor.
2. Select all the cells in the group footer row. To select multiple cells, use Shift- click.
3. Choose Border in Property Editor, then set the border properties:

- Set Style to a solid line.

- Choose the button that shows the bottom border.

4. 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.
5. Preview the report. A line appears at the end of each group, as shown in Figure 8-16.

Figure 8-16 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.

1. Choose File->View Report in Web Viewer.
The report appears in the report viewer.

2. 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-17.

Figure 8-17 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.

1. Return to BIRT Report Designer.
2. In the layout editor, select the table, then choose the Groups tab in Property Editor.
3. Double-click credit_group in the list of groups.
Edit Group displays the properties of the group. TOC Item Expression is set, by default, to the grouping field, row["CREDITLIMIT"].
4. Choose the ellipsis (...) button, and in Expression Builder, replace the row["CREDITLIMIT"] expression with the following expression. This is the same column binding expression that is used by the data element in the group header. Rather than typing the expression again, you can copy it from the data element and paste it here.

    for(i=50000; i<300000; i+=50000){
    if( row["CREDITLIMIT"] < i ){
      rangeStart = i-50000;
      rangeEnd = i-1;
      break;
    }
    }
    displayString=rangeStart + " - " + rangeEnd;

5. Choose OK.
6. 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-18.

Figure 8-18 Updated table of contents