19.3 Add a Second Query

In the Paper Design view, the data is grouped by the Sales Representative with customer data listed below each Sales Representative.

Figure 19-4 Report layout in the Paper Design view

Description of Figure 19-4 follows
Description of "Figure 19-4 Report layout in the Paper Design view"

All the data will display on one page and the individual sales transactions for each customer is not yet available. You will need an additional query in the Data Model to accomplish this.

To add a new query to the report:

  1. Click the Data Model button in the toolbar.

  2. In the Data Model view, click the SQL Query tool in the tool palette then click in an open area of the Data Model view, somewhere to the right of query Q_1, to display the SQL Query Statement dialog box.

  3. In the SQL Query Statement field, enter the following SELECT statement:

    SELECT CUSTID,PRODNAME,AMOUNT
FROM SALES
ORDER BY CUSTID

    Note:

    You can enter this query in any of the following ways:

    • Copy and paste the code from the provided text file called pagenum_code.txt into the SQL Query Statement field.

    • Click Query Builder to build the query without entering any code manually.

    • Type the code in the SQL Query Statement field.

  4. Click OK to display the new query in the Data Model view.

  5. In the Data Model view, click the Data Link tool in the tool palette.

  6. Using the cross hairs of the cursor, click CUSTID in the G_NAME group and drag the cursor to custid1 in the G_custid1 group, then release the mouse button to link the CUSTID and custid1 fields between the two groups.

  7. Position the Report Editor window to display it alongside the Object Navigator so that you can view both windows simultaneously.

  8. In the Object Navigator, under the Data Model node, expand the Groups node. Then, under the Paper Layout node, expand the Main Section node. Compare these two structures and note that even though additional data is made available in the Data Model by adding query Q_2, the Paper Layout will not use this additional data unless modified to do so. We will do this next.