15.2 Create a Query in the Data Model View

When you create a report, you can either use the Report Wizard to assist you or create the report yourself. In this example, you will use the Data Model view to create your two queries, then use the tool palette to create a data link between the two queries to relate the data tables.

To create the query:

  1. Launch Reports Builder (or, if already open, choose File > New > Report).

  2. In the Welcome or New Report dialog box, select Build a new report manually, then click OK.

  3. In the Data Model view that displays, click the SQL Query tool in the tool palette then click in an open area of the Data Model view to display the SQL Query Statement dialog box.

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

    SELECT ALL CUSTOMERS_A1.CUST_FIRST_NAME, 
  CUSTOMERS_A1.CUST_LAST_NAME, ORDERS.ORDER_ID, ORDERS.ORDER_TOTAL
FROM CUSTOMERS CUSTOMERS_A1, ORDERS
WHERE (ORDERS.CUSTOMER_ID = CUSTOMERS_A1.CUSTOMER_ID) 
ORDER BY CUSTOMERS_A1.CUST_LAST_NAME

    Note:

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

    • Copy and paste the code from the provided text file called wrappedbreak_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.

  5. Click OK to display the data model for your new query in the Data Model view. It should look like this:

    Figure 15-2 Data Model for the query

    Description of Figure 15-2 follows
    Description of "Figure 15-2 Data Model for the query"

  6. In the G_CUST_FIRST_NAME group, click CUST_FIRST_NAME and drag it above the group to create another group.

  7. Click CUST_LAST_NAME, then drag it into the new group, so that the data model now looks like this:

    Figure 15-3 Data model with groups

    Description of Figure 15-3 follows
    Description of "Figure 15-3 Data model with groups"

  8. Double-click the new group (G_1) to display the Property Inspector, and set the following properties:

    • Under General Information, set the Name property to G_Name.

    Your data model should now look like this:

    Figure 15-4 Data model with new G_Name group

    Description of Figure 15-4 follows
    Description of "Figure 15-4 Data model with new G_Name group"

  9. Save your report as wrappedbreak_your_initials.rdf.