12.2 Create Two Queries

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 two queries:

  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 DEPARTMENTS.DEPARTMENT_ID, DEPARTMENTS.DEPARTMENT_NAME, 
DEPARTMENTS.MANAGER_ID, DEPARTMENTS.LOCATION_ID
FROM DEPARTMENTS

    Note:

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

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

    The data model for your new query displays:

    Figure 12-2 Data Model for Query 1

    Description of Figure 12-2 follows
    Description of "Figure 12-2 Data Model for Query 1"

  6. Create another query, this time using the following code:

    SELECT ALL EMPLOYEES.LAST_NAME, EMPLOYEES.FIRST_NAME, EMPLOYEES.DEPARTMENT_ID
FROM EMPLOYEES 
ORDER BY EMPLOYEES.LAST_NAME

    Note:

    You can also copy and paste the code from the text file provided in the acrossbreak folder, called acrossbreak_code.txt.

  7. Click OK.

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

  9. Click and drag your mouse from DEPARTMENT_ID column in Q_1, to DEPARTMENT_ID1 in Q_2 to create a data link between the two queries.

    Your data model should now look like this:

    Figure 12-3 Data Model of the two linked queries

    Description of Figure 12-3 follows
    Description of "Figure 12-3 Data Model of the two linked queries"

    Note:

    You can right-click the data link, then choose Property Inspector from the pop-up menu to ensure that the data link was created properly.