24.2 Create the Data Model with Two Linked Queries

The steps in this section will show you how to build a simple data model with two queries in a master/detail relationship.

To create a data model:

  1. After you have updated the REPORTS_PATH with the images directory path, launch Reports Builder.

  2. Choose File > New > Report.

  3. Select Build a new report manually, then click OK.

  4. 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.

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

    SELECT ALL DEPT.DEPTNO, DEPT.DNAME, PICTURES.PICTURE
FROM DEPT, PICTURES
WHERE (DEPT.DEPTNO = PICTURES.DEPTNO)
ORDER BY DEPT.DEPTNO

    Note:

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

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

    Also note that if you have not installed the Pictures table into the sample schema, you will not be able to create this query.

  6. Click OK.

    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 24.1, "Prerequisites for This Example" describes the sample schema requirements for this example.

  7. When the query displays in the Data Model view, right-click the query name (Q_1), then choose Property Inspector from the pop-up menu to display the Property Inspector, and set the following properties:

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

  8. In the Data Model view, double-click the picture column in the Q_PICTURES query to display the Property Inspector, and set the following properties:

    • Under Column, set the Read from File property to Yes, and set the File Format property to Image.

  9. In the Data Model view, follow the steps above to create another query named Q_EMPLOYEES with the following code:

    SELECT ALL EMP.DEPTNO, EMP.ENAME, EMP.JOB, EMP.HIREDATE
FROM EMP
ORDER BY DEPTNO, ENAME

    Note:

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

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

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

  11. Drag a link between DEPTNO in Q_PICTURES and DEPTNO1 in Q_EMPLOYEES. Your data model should now look like this:

    Figure 24-2 Data Model view for the dynamic graphics report

    Description of Figure 24-2 follows
    Description of "Figure 24-2 Data Model view for the dynamic graphics report"

  12. Save your report as dynamicgraphics_your_initials.rdf.