31
Building a Report Using a Pre-Printed Form

Figure 31-1 Printing Reports on Pre-Printed Forms

Text description of the illustration orbrpreprint_finfin.gif

In this chapter, you will learn formatting techniques for printing reports on pre-printed forms when you do not have access to a computer readable version of the forms. Such reports must be designed so that the data prints in exact positions on the form.

The above image shows the example report you will create in this chapter, printed on a sample pre-printed form.

Note: Many of the same concepts are used in the following three example reports This example, where you learn formatting techniques for printing reports on pre-printed forms when you do not have access to a computer readable version of the forms. Such reports must be designed so that the data prints in exact positions on the form. Chapter 30, "Building a Check Printing Report with Spelled-Out Cash Amounts", where you import an image of a check and use it as a guide to position fields in the Paper Layout view. In addition, you learn how to create a PL/SQL function that returns spelled-out numerical values. Chapter 32, "Building an Invoice Report", where you import an image of an invoice and use it as a guide to position fields in the Paper Layout view.

Concepts

• This chapter explains how to use the various tools in the Paper Layout view and the Paper Design view to format a report for printing on pre-printed forms.

Data Relationships

• This report uses two queries linked in a master/detail relationship. You will create a break in the detail group and two summary columns in the master group.

Layout

• This report consists of two partial default layouts, one with a mailing label format and one with a tabular format. You'll move fields to the correct positions on the page and restrict the master repeating frame to appearing once per form.

Example Scenario

This report consists of data formatted such that all values fall precisely within the corresponding fields of a pre-printed sales order.

In our example, the form is on a standard sized 8.5 inches by 11 inches page. The employee name and the customer's name and address must display in the region at the top of the page. The order item details must only display 40 characters. Anything over 40 characters will be placed on a second page of the form. At the end of our example, we add page numbers. This section is optional, as your forms may not require page numbers.

Note: The above measurements are for the form we use in this example. If you do not have access to an online, or computer readable version of your pre-printed form, you will need to take these measurements yourself, then use the steps below to create your own report definition.

To see a sample report that uses pre-printed forms, open the examples folder named preprint, then open the Oracle Reports example named preprint.rdf. For details on how to open it, see "Accessing the example reports" in the Preface.

Table 31-1 Features demonstrated in this example

Feature	Location
Use the tools in the Data Model view to manually create two queries. You will also link the two queries, create a break group, and create two summary columns	Section 31.2, "Manually create the data model for your report"
Use the tools in the Paper Layout view to create the layout for your report. You will also use the Report Block Wizard to create a mailing label portion of your report and a tabular report that displays the order detail information.	Section 31.3, "Create the layout for your report"
Use the tools in the Paper Design view to add format masks and adjust the appearance of your report.	Section 31.4, "Format your report in the Paper Design view"
Use the tools in the Paper Layout view to add page numbering fields to your report.	Section 31.5, "Add page numbers (optional)"

31.1 Prerequisites for this example

To build the example in this chapter, you must have access to the Order Entry portion of the sample schema, which is provided by default with the Oracle9i database. Contact your database administrator for access to this schema.

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

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

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

6. Click OK.

7. If the Connect dialog box displays, type the connection information for the database where the Order Entry portion of the sample schema resides, then click Connect.

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

Figure 31-2 Data Model view of the first query

Text description of the illustration orbrpreprint_dm.gif

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

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

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

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

12. Right-click the query object, then choose Property Inspector from the pop-up menu.

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

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

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

    Your data model should now look like this:

Figure 31-3 Data Model view with two queries

Text description of the illustration orbrpreprint_dm1.gif

Note: In Q_CUSTOMER, the second WHERE clause restricts the values returned to only those customers who have current orders. In Q_ORDERS, we've 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.

16. Save your report as preprint_<your initials>.rdf.

31.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 31-4 Data Model view of the linked queries and break group

Text description of the illustration orbrpreprint_dm2.gif

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 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 31-5 Complete Data Model view of the pre-printed forms example

   

   Text description of the illustration orbrpreprint_dmfin.gif

   8. Save your report.

   31.3 Create the layout for your report

   The steps in this section will show you how to create a layout that displays the objects in the desired locations on your pre-printed form. You will start by adjusting the margin of your report, then creating the mailing label portion that will print at the top of each page that shows the address information for the customer. You will then create the body of the report, which contains the order information that display the date, order number, description, number, price per item, and total price.

   31.3.1 Set up your report layout

   Before you can start adding layout objects, you will need to adjust the margin of your layout.

   To set up the layout:

   1. Click the Paper Layout button in the toolbar to display the Paper Layout view of your report.

   2. To make it easier for you to manipulate your layout objects, expand the size of the Paper Layout window.

   3. Click the Edit Margin button in the toolbar to view the margin portion of the layout.

   4. Using the resize handles, adjust the size of the margin (the solid black bar) to the dimensions of your form. In our example, our form is 8.5 inches by 11 inches, so we will expand the margin to 8.5 inches across, and 11 inches down.

      Tip: You can use the corner resize handles to adjust the size of your margin.

   5. Click the Edit Margin button in the toolbar again to return to the body of the layout.

      Now, you are ready to create your report layout.

   31.3.2 Create an address label for your form

   In this section, you will use the Report Block Wizard to create a mailing label for the address section of your pre-printed form.

   To create an address label:

   1. In the Paper Layout view, click the Report Block tool in the tool palette.

   2. Using the rulers along the edges of the Paper Layout view, drag a rectangle starting at 1 inch from the left and 0.25 inches from the top (1, 0.25) to 6 inches from the left and 1.25 inches from the top (6, 1.25). Release your mouse button to display the Report Block Wizard.

   3. On the Style page of the Report Block Wizard, select Mailing Label, then click Next.

   4. On the Groups page, click G_CUSTOMER and click Down in the Available Groups list to specify the Print Direction and move this group to the Displayed Groups list, then click Next.

   5. In the Mailing Label Text field, type the columns (or click the columns in the Available Fields list) so that the field looks like this:

   Figure 31-6 Mailing label text field

   

   Text description of the illustration orbrpreprint_ml.gif

   Note: For more information on building a mailing label report, see Chapter 6, "Building a Mailing Label Report".

   6. Once you've verified your text against the above, click Next.

   7. On the Template page, select No template, then click Finish to display your report layout in the Paper Layout view. It should look something like this:

   Figure 31-7 Paper Layout view of the initial layout

   

   Text description of the illustration orbrpreprint_lay.gif

   8. The region that you just created is a boilerplate object called B_TBP. While this object is selected, view the Object Navigator and notice the new boilerplate object:

   Figure 31-8 Report Block boilerplate object in the Object Navigator

   

   Text description of the illustration orbrpreprint_on.gif

   9. While the object is selected, press F4 on your keyboard to display the Property Inspector (or right-click the object and choose Property Inspector).

   10. In the Property Inspector, under General Layout, set the Horizontal Elasticity property to Variable. This ensures that appropriate space is available for the mailing label text.

   11. Save your report.

   31.3.3 Add the order item details to your report

   In this section, you will add a report block that displays the order items and details that correspond to the customer identified in each mailing label. These items and details are required for your pre-printed form.

   To add order item details to your report:

   1. In the Paper Layout view, click the repeating frame that surrounds the boilerplate object (B_TBP) you created in Section 31.3.2, "Create an address label for your form".

   2. Use the corner resize handles to adjust the size of the repeating frame to the size of your pre-printed form (in our example, 8.5 inches across and 11 inches down). Make sure the frame does not extend beyond the single page boundary.

   3. Click the Report Block tool in the tool palette.

   4. Using the rulers as guides, drag a region 0.25 inches from the left and 1.5 inches from the top (0.25, 1.5) to 8.25 inches from left and 2 inches from the top (8.25, 10). Release your mouse button to display the Report Block Wizard.

   5. On the Style page of the Report Block Wizard, select Group Left, then click Next.

   6. On the Groups page, click the following groups and click Down in the Available Groups list to specify the Print Direction and move them to the Displayed Groups list, then click Next:
      • G_ORDER_DATE

      • G_ORDER_ITEMS

   7. On the Fields page, click the following fields in the Available Fields list and click the right arrow (>) to move them to the Displayed Fields list, then click Next:
      • ORDER_DATE

      • PRODUCT_ID

      • PRODUCT_DESCRIPTION

      • QUANTITY

      • UNIT_PRICE

      • ITEM_TOTAL

   8. On the Labels page, remove the label names since they are not required for the pre-printed form, and adjust the widths as follows, then click Next:

      Fields and Totals	Labels	Width
      ORDER_DATE	<none>	9
      PRODUCT_ID	<none>	8
      PRODUCT_DESCRIPTION	<none>	40
      QUANTITY	<none>	4
      UNIT_PRICE	<none>	8
      ITEM_TOTAL	<none>	10

      Note: The Width values are based on the space available on the pre-printed form.

   9. On the Template page, select No Template, then click Finish to display your report layout in the Paper Layout view. It should look something like this:

   Figure 31-9 Paper Layout view of the pre-printed form example with mailing label and order item details

   

   Text description of the illustration orbrpreprint_layod.gif

   10. Save your report.

   31.3.4 Adjust the layout and add summaries

   Now that you've added the elements you want displayed on your report, you need to set up the layout so that only records required for the pre-printed form display on each page, and that the correct amount of information displays in the available amount of space on the form. Also, the form requires that a summary of each page displays in the same location on the page.

   To adjust the layout and add page summaries:

   1. In the Object Navigator, click M_G_ORDER_ITEMS_GRPFR, as shown in the following image:

   Figure 31-10 Selected repeating frame in the Object Navigator

   

   Text description of the illustration orbrpreprint_rgon.gif

   2. In the Paper Layout view, you should see the same object selected, as shown in the following image:

   Figure 31-11 Selected repeating frame in the Paper Layout view

   

   Text description of the illustration orbrpreprint_rglay.gif

   3. In the Object Navigator, double-click the properties icon next to M_G_ORDER_ITEMS_GRPFR to display the Property Inspector, and set properties:
      • Under General Layout, set the Vertical Elasticity property to Fixed to ensure that only this area is available for the order items to display.

   4. In the Object Navigator, double-click the properties icon next to R_G_CUSTOMER to display the Property Inspector, and set properties:
      • Under Repeating Frame, set the Maximum Records per Page property to 1, to ensure that only one customer's information prints per page.

   5. In the Object Navigator, double-click the view icon next to the Paper Layout node to display the Paper Layout view.

   6. In the Paper Layout view, click the Field tool in the tool palette.

   7. Draw a region 7 inches from the left and 10.25 inches from the top (7, 10.25) to 8 inches from the left and 10.5 inches from the top (8, 10.5).

      Note: This field, as well as the second field you will create, should not be located within the repeating frame called M_G_ORDER_DATE_GRPFR. If you cannot fit these fields on the page, resize the repeating frame so that you can place the fields below the repeating frame, but still on the same page.

   8. Double-click the new field object (F_1) to display the Property Inspector, and set properties:

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

   • Under Field, set the Source property to CS_PAGE_TOTAL (the summary column you created in Section 31.2, "Manually create the data model for your report").

9. Following the steps above, create another field directly below F_PAGE_TOTAL in the Paper Layout view, then set its Name property to F_ORDER_TOTAL and Source property to CS_ORDER_TOTAL.

10. The new layout objects should now look like this:

    Figure 31-12 F_PAGE_TOTAL and F_ORDER_TOTAL fields in the Paper Layout view

    

    Text description of the illustration orbrpreprint_flds.gif

    11. Save your report.

    31.4 Format your report in the Paper Design view

    The steps in this section will show you how to preview your report in the Paper Design view, and make a few last-minute tweaks using the tools in the Paper Design view. It's sometimes easier to use the Paper Design view to finish your report, since you can see actual data displayed.

    To format your report in the Paper Design view:

    1. Click the Paper Design button in the toolbar to display the Paper Design view.

    2. In the Paper Design view, click the field F_PRODUCT_DESCRIPTION.

       Tip: If you cannot find this field, you can use the Object Navigator to find the field name and click it. When you select an item in the Object Navigator, the corresponding object is selected in the Paper Design view.

    3. Increase the size of this field as much as possible, given the size of the area on your form.

    4. Shift-click to select the four numbered fields, then click the Align Right button in the toolbar.

    5. At the bottom of the page, Shift-click the two fields (F_PAGE_TOTAL and F_ORDER_TOTAL), then click the Line Color tool in the tool palette and choose No Line.

    6. Shift-click the three currency number fields on the right.

    7. Click the Currency button in the toolbar to add a dollar sign to the amounts.

    8. Click the Commas button to add a comma to values over 999.

    9. Click the Add Decimal Place button to add a decimal to the values.

    10. In the Object Navigator, Shift-click the following three objects:
        • B_TBP

        • F_ORDER_TOTAL

        • F_PAGE_TOTAL

    11. Press F4 on your keyboard to display the Property Inspector, and set properties:
        • Under Advanced Layout, set the Print Object On property to All Pages, and set the Base Printing On property to Enclosing Object.

    12. In the Paper Design view, adjust the sizes of the fields according to the measurements on your pre-printed form.

        Your report should now look like this:

    Figure 31-13 Final preview of your pre-printed form report

    

    Text description of the illustration orbrpreprint_fin.gif

    Note: To see a more complete view of the report, you can open the sample report we've provided, called preprint.rdf. To view the report, open the report and click the Run Paper Layout button in the toolbar.

    13. When you print your report on to the form, it might look something like this:

    Figure 31-14 Final report printed on a sample pre-printed form

    

    Text description of the illustration orbrpreprint_finfin.gif

    Note: The Paper Design view of your report will not look like the image above. We printed our report on a sample pre-printed form. The above image is a snapshot of that form.

    14. Save your report.

    31.5 Add page numbers (optional)

    Some pre-printed forms may require you to print page numbers. The steps in this section will show you how to add page numbers to your output that will display when the number of order items extends beyond a single page.

    To add page numbers to your report:

    1. Click the Paper Layout button in the toolbar to display the Paper Layout view.

    2. Click the Field tool in the tool palette.

    3. Draw a small region on your layout 6 inches from the left and 0.25 inches from the top (6, 0.25) to 7 inches from the left and 0.5 inches from the top (7, 0.5).

    4. Click the new field object, F_1, then press F4 on your keyboard to display the Property Inspector, and set properties:
       • Under General Information, set the Name property to F_PAGE_NUMBER.

       • Under General Layout, set the Horizontal Elasticity property to Variable.

       • Under Field, set the Source property to Page Number, and set the Visible property to No. Click the Page Numbering property field to display the Page Numbering dialog box.

    5. In the Page Numbering dialog box:
       • Clear the Header Section and Trailer Section check boxes, and make sure that the Main Section check box is selected.

       • In the Reset At list, click R_G_Customer.

       • Click OK.

    6. In the Paper Layout view, following the steps above to create another field from (7.25, 0.25) to (8.25, 0.5).

    7. Double-click the new field object to display the Property Inspector, and set properties:
       • Under General Information, set the Name property to F_TOTAL_PAGES.

       • Under General Layout, set the Horizontal Elasticity property to Variable.

       • Under Field, set the Source property to Total Pages, and set the Visible property to No. Click the Page Numbering property field to display the Page Numbering dialog box.

    8. In the Page Numbering dialog box:
       • Clear the Header Section and Trailer Section check boxes, and make sure that the Main Section check box is selected.

       • In the Reset At list, click R_G_Customer.

       • Click OK.

       Now that you've created the page number fields, you need to make them visible.

    9. In the Paper Layout view, click the Text tool in the tool palette.

    10. Draw a region on your layout 6 inches from the left and 1 inch from the top (6, 1) to 8 inches from the left and 1.25 inches from the top (8, 1.25).

    11. Click inside the rectangle and type "&<F_PAGE_NUMBER> of &<F_TOTAL_PAGES>".

        Note: The "&<F_PAGE_NUMBER>" text will replace that text with the current value of the summary field you just created, which determines the page number of the current page. The "&<F_TOTAL_PAGES>" text will replace that text with the current total number of pages of the report, based on the value of the summary field of the same name.

    12. With the text object selected, press F4 on your keyboard to display the Property Inspector, and set properties:
        • Under General Information, set the Name property to B_PAGE_NUMBERS.

        • Under Advanced Layout, set the Print Object On property to All Pages, and set the Base Printing On property to Enclosing Object.

    13. In the Paper Design view, click the Run Paper Layout button in the toolbar to run your report and display it in the Paper Design view.

        Your report should now look like the following image. Notice how Reports Builder calculates that this particular report is one page, thus displays "1 of 1" above the order details.

    Figure 31-15 Paper Design view of the pre-printed form report with page numbers

    

    Text description of the illustration orbrpreprint_finpn.gif

    14. Save your report.

    31.6 Summary

    Congratulations! You have successfully built a report that you can print on a pre-printed form. You now know how to:

    • manually create two queries and link them.

    • create a summary column.

    • create a break group in your data model.

    • use the tools in the Paper Layout view to create your layout.

    • use the Report Block Wizard to create a mailing label report.

    • use the Report Block Wizard to create a tabular report.

    • add page numbering using fields in the Paper Layout view.

    For more information on any of the wizards, views, or properties used in this example, refer to the Reports Builder Online Help, which you can access in two ways:

    • From the Oracle Technology Network (http://otn.oracle.com/products/reports/), click Documentation and navigate to the Reports Builder Online Help for the most recent, hosted online help.

    • From Reports Builder, choose Help > Help Contents.

      Note: You can replace your Reports Builder online help with the most recent update by downloading the latest online help set available on the Oracle Technology Network (http://otn.oracle.com/docs/products/reports/). Instructions for replacing your help file are included in the readme.txt in the download file.