Task 5:  Build a data set for the payments subreport

In this procedure, you build a data set to indicate what data to extract from the Payments table. The payments 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	In New Data Set, type the following text for the data set’s name:

Payments

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

4	In Query, in Available Items, expand CLASSICMODELS, then expand the Payments table to display the columns in the table.

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

SELECT Payments.paymentDate,

Payments.amount

FROM Payments

WHERE Payments.customerNumber = ?

This statement selects the PAYMENTDATE and AMOUNT columns from the Payments table. The WHERE clause has a parameter marker for the value of CUSTOMERNUMBER. When the report runs, the payments subreport gets the current CUSTOMERNUMBER value from the customers report.

6	Choose Finish to save the data set.

7	In Edit Data Set, create a data set parameter to supply the CUSTOMERNUMBER value for the WHERE clause.

1	Choose Parameters. Edit Data Set displays a default parameter.

2	Choose Edit to modify the parameter definition.

3	In Edit Parameter, specify the following values:

Name: CustID

Data Type: Integer

Direction: Input

Default value: 103

Linked To Report Parameter: None

4	Choose OK to confirm your edits to the parameter definition.

8	Choose Preview Results to confirm that the query returns the correct data. Figure 13-6 shows the data rows that the data set returns for customer number 103.

Figure 13-6  Data preview for the payments subreport

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