Tutorial 3: Building a report with side-by-side subreports
This section provides step-by-step instructions for building a report that displays a list of customers. For each customer, the report displays order and payment information. The order and payment information are in separate subreports that appear next to one another. The customer report is the master, or outer, report, and the order and payment subreports are the detail, or inner, reports.
Each report accesses data from a different table in the sample database, Classic Models. The customer report uses data from the Customers table. The orders subreport uses data from the Orders table. The payments subreport uses data from the Payments table.
A common field, customerNumber, links the reports. The value of the linking field in the master report determines the data that appears in the detail reports. For example, if the customerNumber in the master report is 173, the detail reports display the order and payment information for the customer whose ID is 173.
The following illustration shows a portion of the finished report.
In this tutorial, you perform the following tasks:
Task 1: Create a new report
- This task assumes you have already created a project for your reports. If you are using Rich Client Platform (RCP), there is no requirement for a project. Choose File->New->Report.
New Report appears.
- Select a project in which to store your report.
- Type the following text as the file name:
- Choose Next.
- Select Blank Report, then choose Finish.
Your new report appears in the layout editor.
Task 2: Build a data source
Before you begin designing your report in the layout editor, you build a data source to connect your report to the Classic Models database.
- Choose Data Explorer.
If it is not open, choose Window->Show View->Data Explorer.
- Right-click Data Sources, then choose New Data Source from the context menu.
New Data Source appears.
- Select Classic Models Inc. Sample Database from the list of data sources, use the default data source name, then choose Next.
New Data Source displays connection information about the new data source.
- Choose Finish.
BIRT Report Designer creates a new data source that connects to the sample database. It appears within Data Sources in Data Explorer.
Task 3: Build a data set for the customer report
In this procedure, you build a data set to specify the data to extract from the Customers table. The customer report you create later uses this data set.
- In Data Explorer, right-click Data Sets and choose New Data Set from the context menu.
New Data Set appears.
- Type the following text for data set name:
- Use the default values for the other fields:
- Data Source shows the name of the data source you created previously.
- Data Set Type specifies that the data set uses a SQL SELECT query.
- Choose Finish.
Edit Data Set appears. It displays all the tables in the database. You can expand each table to display the columns in the tables. It also displays the basic clauses of a SQL SELECT statement.
- Expand the Customers table.
The columns in the Customers table appear.
- Specify the following SQL SELECT statement to specify the data to retrieve. You can type the column and table names, or drag them from the left side to the appropriate location in the SELECT statement.
This statement selects the customerName and customerNumber columns from the Customers table.
- Choose Preview Results to make sure the query is valid and that it returns the correct data.
If you created the SELECT statement correctly, you should see the following results. These are the data rows that the query returns.
- Choose OK.
Task 4: Build a data set for the orders subreport
In this procedure, you build a data set to specify the data to extract from the Orders table. The orders subreport you create later uses this data set.
- In Data Explorer, right-click Data Sets and choose New Data Set from the context menu.
New Data Set appears.
- Type the following text for data set name:
- Use the default values for the other fields, then choose Finish.
Edit Data Set appears.
- Expand the Orders table to display the columns in the table.
- Specify the following SQL SELECT statement to specify the data to retrieve:
This statement selects the orderNumber and orderDate columns from the Orders table. The WHERE clause has a parameter marker for the value of customerNumber. At run time, the orders subreport gets the current customerNumber value from the customers report.
- Create a data set parameter to supply the customerNumber value in the WHERE clause:
- Choose Parameters from the left side of the window.
Edit Data Set displays parameters information.
- Specify the following values in the first line of the table:
- Name: CustID
- Data Type: Decimal
- Direction: Input
- Default value: 103
103 is one of the values in the customerNumber column. A default value is required for BIRT Report Designer to run the query for testing purposes.
Edit Data Set should look like the one in the following illustration.
- Choose Preview Results to make sure the query is valid and that it returns the correct data.
If you created the SELECT statement and created the data set parameter correctly, you should see the following results. These are the data rows that the query returns for customer number 103.
- Choose OK.
Orders appears in the list of data sets in Data Explorer.
Task 5: Build a data set for the payments subreport
In this procedure, you build a data set to specify the data to extract from the Payments table. The payments subreport you create later uses this data set.
- In Data Explorer, right-click Data Sets and choose New Data Set from the context menu.
New Data Set appears.
- Type the following text for data set name:
- Use the default values for the other fields, then choose Finish.
Edit Data Set appears.
- Expand the Payments table to display the columns in the table.
- Specify the following SQL SELECT statement to specify the data to retrieve:
This statement selects the paymentDate, checkNumber, and amount columns from the Payments table. The WHERE clause has a parameter marker for the value of customerNumber. At run time, the payments subreport gets the current customerNumber value from the customers report.
- Create a data set parameter to supply the customerNumber value in the WHERE clause:
- Choose Parameters.
Edit Data Set displays parameters information.
- Specify the following values in the first line of the table:
- Name: CustID
- Data Type: Decimal
- Direction: Input
- Default value: 103
- Choose Preview Results to make sure the query is valid and that it returns the correct data.
If you created the SELECT statement and created the data set parameter correctly, you should see the following results. These are the data rows that the query returns for customer number 103.
- Choose OK.
Payments appears in the list of data sets in Data Explorer.
Task 6: Create the customer master report
You use a list element to create the master report and organize the orders and payments subreport within it. The list iterates through the customer data rows, and for each customer record, creates the related orders and payments subreports. For the sake of simplicity, the customer report displays just the customer name. It can, of course, display additional data, such as customer address, phone number, and credit limit.
- Choose Palette.
Palette appears.
- Drag a list element from the palette and drop it in the report.
The list element appears in the report.
- Associate, or bind, the list with the Customers data set:
- In Property Editor, choose the Binding tab.
- For Data set, select Customers from the drop-down list.
- Choose Data Explorer, then expand Data Sets--Customers.
The columns you specified in the query appear below Customers.
- Drag customerName from Data Explorer and drop it in the detail band of the list.
The list shows the data set field you added.
- Choose Preview to preview the report.
The report should look like the one in the following illustration. The report lists all the customer names in the order in which the query returns them.
- Sort the customer names in ascending order:
- Choose Layout to return to the layout editor.
- In the layout editor, select the list element. Hover your mouse over the bottom left corner until you see the List tab, then select the tab.
- In Property Editor, choose the Sorting tab.
Property Editor displays the sort page.
- Choose Add to create a sort expression.
A row appears under Sort on.
- Select the area under Sort Key, then select the arrow button that appears, and select customerName from the drop-down list.
- Use the default value, Ascending, for Sort Direction.
- Preview the report. Customer names should appear in ascending order.
Task 7: Create the orders subreport
The orders subreport lists the orders for each customer in a row and column format. It displays the order number and date of each order. To iterate through the orders data set rows and display them in a row and column format, you use the table element.
- Choose Layout to return to the layout editor.
- Drag a table element from the palette and drop it below the customerName data element, in the detail band.
Insert Table appears. It prompts you to specify the number of columns and detail rows to create for the table.
- Specify 2 columns and 1 detail row, then choose OK.
A table with two columns and one detail row appears in the layout editor.
- Choose Data Explorer, then expand Data Sets--Orders.
The columns you specified in the query appear below Orders.
- Drag orderNumber from Data Explorer and drop it in the first cell of the table's detail row.
The table shows the data set field you added. It also shows the label that the layout editor automatically adds to the header row. This label displays the field name and serves as the column heading.
- Drag orderDate from Data Explorer and drop it in the second cell in the detail row.
The report page should look like the one in the following illustration.
- Sort the order rows by order number.
- Select the orders table.
- In Property Editor, choose the Sorting tab.
Property Editor displays the sort page.
- Choose Add to create a sort expression.
A row appears under Sort on.
- Select the area under Sort Key, then select the arrow button that appears, and select orderNumber from the drop-down list.
- Use the default value, Ascending, for Sort Direction.
- Preview the report.
The report should look like the one in the following illustration.
The same order records appear for every customer because when you created the data set parameter, CustID, you specified a default value of 103 for customerNumber. Therefore, the orders subreport always displays the order records for customer 103.
The solution is to dynamically update the value of the CustID parameter each time the customer row in the master report changes. This procedure is described next.
Task 8: Link the orders subreport to the master report
You link the orders subreport to the master report by binding the CustID parameter to the customerNumber field in the customers report. Each time the customer report gets to a new customer row, the CustID parameter is updated with the new customerNumber value.
- Choose Layout to return to the layout editor.
- Select the orders table.
- In Property Editor, choose the Binding tab.
The Binding page displays the CustID parameter, as shown in the following illustration. Its value is set to the default, 103, which you specified when you created the data set parameter.
- Change the parameter value to the customerNumber field in the customers report:
- Select the Value field, then choose the button that appears on the right.
Expression Builder appears.
- Select Available Data Sets.
Two data sets appears in Subcategory.
- Select Customers.
A list of fields in the Customers data set appears in the last column.
- Double-click customerNumber.
The following expression appears at the top of Expression Builder:
Choose OK.
The Binding page displays the new value of row["customerNumber"] for the CustID parameter.
- Preview the report.
Task 9: Create the payments subreport
The payments subreport lists, in a row and column format, the payments made by each customer. It displays the payment date, check number, and amount of each order. To iterate through the payments data set rows and display them in a row and column format, you use a table element.
- Choose Layout to return to the layout editor.
- Drag a table element from the palette and drop it below the orders subreport, in the detail band.
Insert Table appears. It prompts you to specify the number of columns and detail rows to create for the table.
- Specify 3 columns and 1 detail row, then choose OK.
A table with three columns and one detail row appears in the layout editor.
- Choose Data Explorer, then expand Data Sets--Payments.
The columns you specified in the query appear below Payments.
- Drag the following fields from Data Explorer and drop them in the first, second, and third cells, respectively, in the detail row of the table:
- paymentDate
- checkNumber
- amount
The table shows the data set fields you added. It also shows the labels that the layout editor automatically added to the header row. The labels display the field name and serve as the column headings.
The report page should look like the one in the following illustration.
- Sort the payment rows by payment date.
- Select the payments table.
- In Property Editor, choose the Sorting tab.
Property Editor displays the sorting page.
- Choose Add to create a sort expression.
A row appears under Sort on.
- Select in the area under Sort Key, then select the arrow button that appears, and select paymentDate from the drop-down list.
- Use the default value, Ascending, for Sort Direction.
- Preview the report.
The report should look like the one in the following illustration.
As with the orders subreport when you first created it, the same payment records repeat for every customer because when you created the parameter, CustID, for the Payments data set, you specified a default value of 103 for customerNumber. Therefore, the payments subreport always displays the payment records for customer 103.
Just as you did for the orders subreport, you need to dynamically update the value of the CustID parameter each time the customer in the master report changes.
Task 10: Link the payments subreport to the master report
You link the payments subreport to the master report by binding its CustID parameter to the customerNumber field in the customers report.
- Choose Layout to return to the layout editor.
- Select the payments table.
- In Property Editor, choose the Binding tab.
Property Editor displays the CustID parameter. Its value is set to the default, 103, which you specified when you created the data set parameter.
- Change the parameter value to the customerNumber field in the customers report:
- Select the Value field, then choose the button that appears on the right.
Expression Builder appears.
- Select Available Data Sets.
Two data sets appears in Subcategory.
- Select Customers.
A list of fields in the Customers data set appears in the last column.
- Double-click customerNumber.
The following expression appears at the top of Expression Builder:
Choose OK.
The Binding page displays the new value of row["customerNumber"] for the CustID parameter.
- Preview the report.
Now it displays different payment records for different customers. Not all customers have payment records. To display only customers that have payments or orders, you change the query for the customer report. This task is described next.
Task 11: Display only customers that have orders or payments
The database contains customers that do not have orders or payments. The query for the customer report returns all customers. When you generate the report, there are customer rows that show only the column headings for the orders and payments tables, as shown in the following illustration.
You can exclude customers that do not have orders or payments by changing the query for the customer report.
- Choose Layout to return to the layout editor.
- In Data Explorer, expand Data Sets, right-click Customers, then choose Edit.
The query for the Customers data set appears.
- Add the following SQL lines to the end of the existing query:
The WHERE EXISTS clause checks the Orders and Payments tables for customerNumber values that match the customerNumber values in the Customers table. Only rows that have matching customerNumber values are selected.
The complete query should look like the one in the following illustration.
- Choose Preview Results to verify that the query returns rows, then choose OK.
- Preview the report. Scroll down the report to check the output.
The report no longer displays customers that do not have orders or payments.
Task 12: Display the subreports side by side
Now that the subreports display the correct data, you can focus on laying out the subreports next to one another. You cannot place two tables next to one another because BIRT Report Designer creates block-level elements, which means that each element starts on a new line. To display tables side-by-side, you insert the tables in a grid. The grid enables you to arrange elements in a row and column layout. Unlike the table element, the grid does not iterate through data rows.
- Choose Layout to return to the layout editor.
- Drag a grid element from the palette and drop it into the Detail row above the orders table.
Insert Grid appears. It prompts you to specify the number of columns and rows to create for the grid.
- Specify 2 columns and 1 row, then choose OK.
A grid with two columns and one row appears in the layout editor.
- Move the orders table to the first grid cell. You can do this by selecting the Table tab in the bottom left corner, then dragging the table and dropping it in the grid cell.
- Move the payments table to the second grid cell.
The report layout should look like the one in the following illustration.
- Preview the report.
The report should look like the one in the following illustration.
Task 13: Format the report
Now that the report displays the correct data and layout, you can turn your attention to improving the report's appearance. You perform the following tasks in this section:
Highlight the customer names
- Choose Layout to return to the layout editor.
- In the layout editor, select the customerName data element.
- Choose the Properties tab of Property Editor.
- Select General from the list under Properties.
Property Editor displays the general formatting properties of the element.
- For Size, choose Large to display the element's text in a larger size.
The text increases in size.
- Choose B to format the data as bold text.
The data appears in bold.
Edit the column headings
When you insert a data set field in a table, BIRT Report Designer automatically adds in the group header row a label with the table column name. Often, table column names are not in a form that is appropriate for reports, and you need to change them.
- Double-click the first column heading in the orders table.
The text is highlighted.
- Replace orderNumber with the following text, then press Enter:
- Repeat the previous steps to change the rest of the column headings to the following text:
The report layout should look like the one in the following illustration.
- Preview the report. The report should look like the one in the following illustration.
Change the date formats
When you insert a data element of date type, BIRT Report Designer displays dates according to your system's locale setting. BIRT Report Designer provides many different date formats you can select if you do not want to use the default. In this procedure, you create a style that changes the orderDate and paymentDate values from, for example, Jun 3, 2005 12:00 AM to 6/3/05.
- Choose Layout to return to the layout editor.
- Select the orderDate data element, the element that displays row["orderDate"].
- Choose Element->New Style from the main menu.
New Style appears.
- For Custom Style, specify the following text:
- Choose Format DateTime from the list of style properties on the left.
Format DateTime appears in the right side of the window.
- Choose the m/d/yy format, for example, 5/2/05, from the drop-down list. The values in the drop-down list dynamically update with the current date.
- Choose OK.
The Date_data style automatically applies to the orderDate data element.
- Apply the Date_data style to the paymentDate data element.
- Select the paymentDate data element.
- Right-click the selected element, then choose Style->Apply Style->Date_data.
- Preview the report.
The dates have changed from Jun 3, 2005 12:00 AM format to 6/3/05.
Change the number formats
When you insert a data element of integer type, BIRT Report Designer displays numbers according to your system's locale setting. BIRT Report Designer provides many different number formats you can select if you do not want to use the default. In this procedure, you create a style that changes the amount values, for example, from 48425.69 to $48,425.69.
- Choose Layout to return to the layout editor.
- Select the amount data element in the payments table.
- Choose Element->New Style from the main menu.
New Style appears. It displays properties in the general category.
- For Custom Style, specify the following text:
- Choose Format Number from the list of style properties on the left.
Format Number appears in the right side of the window.
- Specify the following formatting attributes:
- For Format Number as, select Currency from the drop-down list.
- For Decimal places, use the default value of 2.
- Select Use 1000s separator.
- For Symbol, select $ from the drop-down list.
- Use the default values for the other attributes.
The following illustration shows the values you specified.
- Choose OK.
The layout editor appears. The Currency_data style is applied to the amount data element, as indicated by the element's Style property in Property Editor.
- Preview the report.
The numbers changed from 48425.69 format to $48,425.69.
Increase the vertical space between elements
In this procedure, you increase the space between each customer name and the lines before and after it. You can adjust the vertical space between elements in several ways:
- You can increase the top or bottom padding or margins of elements.
- You can organize the elements in a grid and adjust the heights of the grid rows.
- You can organize the elements in a grid and use empty rows, with heights specified, to provide space between elements.
Formatting with a grid is easier and provides more predictable results. Padding and margins property values can yield different results in different web browsers. In this procedure, you use the third method.
- Choose Layout to return to the layout editor.
- Place the customerName data element in the grid that contains the two tables by completing the following steps:
- Select the grid. Hover your mouse over the bottom left corner until you see the Grid tab, then select the tab.
Guide cells appear at the top and left of the selected grid.
- Right-click the guide cell on the left of the grid's first row, then choose Insert->Row->Above, as shown in the following illustration.
A new row appears above the selected row.
- Drag the customerName data element from its current location and drop it in the first cell of the new grid row.
The report design should look like the one in the following illustration.
Because the layout editor shows the borders of individual data elements, grids, tables, and cells, it can sometimes be difficult to see where an element is placed. If you need a clearer view of the containers and the elements within the containers, use the Outline view to get a tree view of the report design. You can also use the Outline view to select a particular element if it is too difficult to select, for example, the grid rather than the table within the grid. To open the Outline view, choose Window->Show View->Outline.
- Add a new grid row above and below the row that contains the customerName data element, using the procedures described earlier.
- Select the grid, then select the first row in the grid, as shown in the following illustration.
- In the General properties of Property Editor, set the row's height to 0.2 in, as shown in the following illustration.
- Select the third row in the grid, and set its height to 0.1 in.
The report design should look like the one in the following illustration.
- Preview the report.
The report should look like the following illustration. There is more space above and below the customer name.
Increase the horizontal space between the orders and payments tables
In this procedure, you increase the space between the orders and payments tables. As with vertical spacing, you can adjust the horizontal space between elements in several ways:
- You can increase the left or right padding or margins of elements.
- You can organize the elements in a grid and adjust the widths of the grid columns.
- You can organize the elements in a grid and use empty columns, with widths specified, to provide space between elements.
Again, formatting with a grid is easier and provides more predictable results. Padding and margins property values can yield different results in different web browsers. In this procedure, you use the third method.
- Choose Layout to return to the layout editor.
- Select the grid. Hover your mouse over the bottom left corner until you see the Grid tab, then select the tab.
Guide cells appear at the top and left of the selected grid.
- Right-click the guide cell above the first column, then choose Insert->Column to the Right, as shown in the following illustration.
A new column appears between the first and third columns. By default, BIRT Report Designer creates columns with the same widths.
- Select the column you just added, and use Property Editor to set its width to 0.4 in, as shown in the following illustration.
The width of the second column decreases.
- Preview the report.
There is more space between the orders and payments tables.
Add borders around the tables
In this procedure, you add a box around the orders and payments tables to clearly identify them as two separate reports.
- Choose Layout to return to the layout editor.
- Select the orders table. Hover your mouse over the bottom left corner until you see the Table tab, then select the tab.
Guide cells appear at the top and left of the selected table.
- Choose Border in Property Editor, then set the border properties:
- Set Style to a solid line.
- Choose one or more buttons to add borders, as shown in the following illustration.
- Repeat the previous steps to draw a border around the payments table.
- Preview the report. The report should look like the one in the following illustration.
Increase the space between the table borders and its contents
The top and left borders of the tables are too close to the table data. In this procedure, you increase the space between the top and left borders and the content.
- Choose Layout to return to the layout editor.
- Select the first cell in the group header row of the orders table. Be careful to select the cell and not the data element in the cell.
The title that appears in Property Editor shows the name of the element you selected. Make sure it displays the following text:
- Choose the Padding properties in Property Editor, then set Top and Left to 0.1 in.
In the layout editor, extra space appears at the top and left of the cell.
- Select the cell below the cell you just formatted and set its Left padding property to 0.1 in.
- Select the cell that contains the Order Date label and set its Top padding property to 0.1 in.
- Select the cells in the same positions in the payments table, and apply the same padding settings you used for the cells in the orders table.
- Preview the report. The report should look like the one in the following illustration.
Align the tops of the tables
Scroll down the generated report and check the output. Notice that where there are more orders records than payment records, the tables align at the bottom, as shown in the following illustration. By default, the vertical alignment of elements are set to baseline.
- Choose Layout to return to the layout editor.
- Select the grid cells in which the tables are placed, as shown in the following illustration. To select multiple cells, use Shift-click.
- Choose the General properties in Property Editor, then set Vertical alignment to Top.
- Preview the report. Scroll down to check that tables of different sizes are aligned at the top.
