Task 4:

Build a data set for the orders subreport

In this procedure, you build a data set to indicate what data to extract from the Orders table. The orders subreport that you create later uses this data set.

1	In Data Explorer, right-click Data Sets, and choose New Data Set from the context menu.

2	On New Data Set, type the following text for the data set’s name:

Orders

3	Use the default values for the other fields, then choose Next.

4	On Query, in Available Items, expand CLASSICMODELS, then expand the Orders table to display the columns in the table.

5	Use the following SQL SELECT statement to indicate what data to retrieve:

SELECT Orders.orderNumber,

Orders.orderDate

FROM Orders

WHERE Orders.customerNumber = ?

This statement selects the ORDERNUMBER and ORDERDATE columns from the Orders table. The WHERE clause has a parameter marker for the value of CUSTOMERNUMBER. When the report runs, the orders subreport gets the current CUSTOMERNUMBER value from the customers report.

6	Choose Finish to save the data set. Edit Data Set displays the columns specified in the query, and provides options for editing the data set.

7	Create a data set parameter to supply the CUSTOMERNUMBER value in the WHERE clause.

1	Choose Parameters from the left side of the window. Edit Data Set displays a default parameter definition.

2	Choose Edit to modify the parameter definition.

3	On Edit Parameter, specify the following values, as shown in Figure 13‑4:

o	Name: CustID

o	Data Type: Integer

o	Direction: Input

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

o	Linked To Report Parameter: None

Figure 13‑4

Edited parameter definition

4	Choose OK to confirm your edits to the parameter definition. The parameter definition appears in Edit Data Set, as shown in Figure 13‑5.

Figure 13‑5

Parameter definition in the Orders data set

8

Choose Preview Results to confirm that 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 results that appear in Figure 13‑6. These are the data rows that the data set returns for customer number 103.

Figure 13‑6

Data preview for the orders subreport

9	Choose OK to save the changes to the data set.