11.3 Create a Data Model with a Data Link

When you create a report with multiple queries, it is typically easier to create all of the queries with the Data Wizard first and then create the layouts with the Report Wizard.

To create the queries:

  1. In the Data Model view, choose Insert > Query to display the Data Wizard.

  2. If the Welcome page displays, click Next.

  3. On the Query page, type Q_Salesrep for the Query name and click Next.

  4. On the Data Source page, click SQL Query, then click Next.

  5. On the Data page, enter the following SELECT statement in the Data Source definition field:

    SELECT ENAME, EMPNO
    FROM EMP
    WHERE JOB = 'SALESMAN'
    

    Note:

    You can enter this query in any of the following ways:
    • Copy and paste the code from the provided text file called grp_above_code.txt into the Data Source definition field.

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

    • Type the code in the Data Source definition field.

  6. Click Next.

    Note:

    If you are not already connected to a database, you will be prompted to connect to the database when you click Query Builder or Next. Ensure that you connect to a database that has the appropriate schema for this example. Section 11.1, "Prerequisites for This Example" describes the sample schema requirements for this example.
  7. On the Groups page, click Next.

  8. Click Finish to display the data model for your report in the Data Model view.

  9. Repeat the steps above for a second query, but this time name your query

    Q_Product and use the following SELECT statement:

    SELECT REPID, PRODNAME, AMOUNT, CUSTNAME
    FROM SALES
    ORDER BY REPID, CUSTNAME
    

Figure 11-2 Two-query data model without a link

Description of Figure 11-2 follows
Description of "Figure 11-2 Two-query data model without a link"

To add the data link

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

  2. Click and drag from the EMPNO column in the G_ENAME group to the REPID column in the G_REPID group. Notice that a line is drawn from the bottom of the G_ENAME group to the Q_Product query. Labels for EMPNO and REPID are created at each end of the line to indicate they are the columns linking G_ENAME to Q_Product.

    Figure 11-3 Two-query data model with a data link

    Description of Figure 11-3 follows
    Description of "Figure 11-3 Two-query data model with a data link"

  3. Double-click the new data link line to display the Property Inspector and examine the property settings:

    • G_ENAME is identified as the parent, while Q_Product is listed as the child. In terms of the data, the sales rep's name and employee number make up the master record and should print once for the associated product information retrieved by the Q_Product query.

    • Notice that WHERE already appears in the SQL Clause property. WHERE is the default clause used in master/detail relationships. You can replace WHERE with other SQL clauses such as HAVING and START WITH, but for this report the default is correct.

    • The other point to notice is that an equal sign (=) appears in the Condition property. An equality (that is, table1.columnname = table2.columnname) is the default condition for master/detail relationships defined through a data link. You can replace the equal sign with any other supported conditional operator (to see what is supported, click the field), but for this report the default is the proper condition.

    Linking the group G_ENAME and the query Q_Product through the EMPNO and REPID columns is analogous to writing both queries as the single-query shown below:

    SELECT ENAME, EMPNO, REPID,
    PRODNAME, AMOUNT, CUSTNAME
    FROM EMP, SALES
    WHERE JOB = 'SALESMAN'
    AND EMPNO = REPID (+)
    ORDER BY REPID, CUSTNAME