32.2 Manually Create the Data Model for Your Report

The steps in this section will show you how to manually create the data model for your report. Your data model will consist of two linked queries. The first query will retrieve all the customers that have orders in the database. The second query will retrieve all of the order information for each of the customers, and will contain a break group based on the date of each order.

32.2.1 Create the queries

The steps in this section will show you how to manually create two queries in the Data Model view.

To create the 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 FIRST_NAME||' '||LAST_NAME EMPLOYEE_NAME
      , CUST_FIRST_NAME||' '||CUST_LAST_NAME CUSTOMER_NAME
      , CUST_ADDRESS
      , CUSTOMER_ID 
    FROM EMPLOYEES, CUSTOMERS 
    WHERE EMPLOYEE_ID = ACCOUNT_MGR_ID 
    AND CUSTOMER_ID IN (SELECT UNIQUE(CUSTOMER_ID) FROM ORDERS)
    ORDER BY LAST_NAME, 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 preprint_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.

    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 32.1, "Prerequisites for This Example" describes the sample schema requirements for this example.
  6. If the Connect dialog box displays, type the connection information for the database where the Order Entry sample schema resides, then click Connect.

    The query displays in the Data Model view, and should look something like this:

    Figure 32-2 Data Model view of the first query

    Description of Figure 32-2 follows
    Description of "Figure 32-2 Data Model view of the first query"

  7. Right-click the query (Q_1), then choose Property Inspector from the pop-up menu.

  8. In the Property Inspector, under General Information, set the Name property to Q_CUSTOMER.

  9. Double-click the group (G_EMPLOYEE_NAME) to display the Property Inspector, and set the Name property to G_CUSTOMER.

  10. In the Data Model view, click the SQL Query tool in the tool palette to create a second query with the following SELECT statement:

    SELECT to_char(ORDER_DATE, 'DD-Mon-YY') ORDER_DATE
      , I.PRODUCT_ID
      , PRODUCT_DESCRIPTION
      , QUANTITY
      , UNIT_PRICE
      , QUANTITY * UNIT_PRICE ITEM_TOTAL
      , CUSTOMER_ID
    FROM ORDERS O, ORDER_ITEMS I, PRODUCTS P 
    WHERE O.ORDER_ID = I.ORDER_ID 
    AND I.PRODUCT_ID = P.PRODUCT_ID 
    ORDER BY ORDER_DATE, I.PRODUCT_ID 
    
  11. Right-click the query object, then choose Property Inspector from the pop-up menu.

  12. In the Property Inspector, set the Name property to Q_ORDERS.

  13. Double-click the new group object to display the Property Inspector.

  14. In the Property Inspector, set the Name property to G_ORDER_ITEMS.

    Your data model should now look like this:

    Figure 32-3 Data Model view with two queries

    Description of Figure 32-3 follows
    Description of "Figure 32-3 Data Model view with two queries"

    Note:

    In Q_CUSTOMER, the second WHERE clause restricts the values returned to only those customers who have current orders. In Q_ORDERS, we have used "to_char" to account for a new datatype, "Datetime." Since this new datatype is not recognized by Oracle Reports, you cannot use format masks on this particular column yet.
  15. Save your report as preprint_your_initials.rdf.

32.2.2 Modify your data model

The steps in this section will show you how to modify your data model to link the two queries and to cause the data retrieved to break on a particular column.You will also create several summary columns to calculate information for each order.

To modify your data model:

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

  2. Click the CUSTOMER_ID column in the G_Customer group.

  3. While holding down your mouse button, drag your cursor to the CUSTOMER_ID1 column in the G_ORDER_ITEMS group and release your mouse button to create a link between the two queries.

  4. Now, create a break group by clicking the ORDER_DATE column in the G_ORDER_ITEMS group and dragging it above the group.

    Tip:

    You can select the group or the query title and use the resize handles to modify the look of your data model.

    Your data model should now look like this:

    Figure 32-4 Data Model view of the linked queries and break group

    Description of Figure 32-4 follows
    Description of "Figure 32-4 Data Model view of the linked queries and break group"

  5. Click the Summary Column tool in the tool palette, then click in the G_CUSTOMER group to create a summary column. You may want to resize the group so that you can see your summary columns.

  6. Double-click the new summary column object (CS_1) to display the Property Inspector, and set the following properties:

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

    • Under Summary, set the Function property to Sum, set the Source property to ITEM_TOTAL, and set the Reset At property to Page.

  7. Create another summary column in the G_CUSTOMER group, and set its properties as follows:

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

    • Under Summary, set the Function property to Sum, set the Source property to ITEM_TOTAL, and set the Reset At property to G_CUSTOMER.

      Note:

      These two summary columns calculate the total items per page and the total items in the order for the report.

    Your data model is now complete, and should look like this:

    Figure 32-5 Complete Data Model view of the preprinted forms example

    Description of Figure 32-5 follows
    Description of "Figure 32-5 Complete Data Model view of the preprinted forms example"

  8. Save your report.