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.
The steps in this section will show you how to manually create two queries in the Data Model view.
Launch Reports Builder (or, if already open, choose File > New > Report).
In the Welcome or New Report dialog box, select Build a new report manually, then click OK.
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.
In the SQL Query Statement field, enter the following
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.
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.
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:
Right-click the query (Q_1), then choose Property Inspector from the pop-up menu.
In the Property Inspector, under General Information, set the Name property to Q_CUSTOMER.
Double-click the group (G_EMPLOYEE_NAME) to display the Property Inspector, and set the Name property to G_CUSTOMER.
In the Data Model view, click the SQL Query tool in the tool palette to create a second query with the following
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
Right-click the query object, then choose Property Inspector from the pop-up menu.
In the Property Inspector, set the Name property to Q_ORDERS.
Double-click the new group object to display the Property Inspector.
In the Property Inspector, set the Name property to G_ORDER_ITEMS.
Your data model should now look like this:
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.
Save your report as
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.
In the Data Model view, click the Data Link tool in the tool palette.
Click the CUSTOMER_ID column in the G_Customer group.
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.
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:
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.
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.
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:
Save your report.