Skip Headers
Oracle® Reports Building Reports
10g Release 2 (10.1.2)
B13895-01
  Go To Documentation Library
Library
Go To Product List
Product
Go To Table Of Contents
Contents
Go To Index
Index

Previous
Previous
Next
Next
 

7 Building a Form Letter Report

In this chapter, you will learn about form letter reports, one of the standard report styles. By following the steps in this chapter, you can generate the report output shown in Figure 7-1.

Figure 7-1 Form letter report output

Description of Figure 7-1  follows
Description of "Figure 7-1 Form letter report output"

Concepts

Data Relationships

There are no special restrictions on data relationships for a form letter report.

Layout

Example Scenario

As you build this example report, you will:

To see a sample form letter report, open the examples folder called formletter, then open the Oracle Reports example report called formletter.rdf. For details on how to open it, see "Accessing the Example Reports" in the Preface.

7.1 Prerequisites for this example

To build the example in this chapter, you must have access to the Human Resources sample schema (HR) provided with the Oracle Database. If you do not know if you have access to this sample schema, contact your database administrator.

7.2 Use the Report Wizard to create a form letter report

When you create a report, you can either use the Report Wizard to assist you or create the report yourself. To build the form letter report in this example, you can use the Report Wizard. Using the wizard enables you to define the layout for the report, as well as set the data definition.

In the Report Wizard, on the Text page, you will be able to set up your form letter report exactly the way you want it to appear. On this page of the wizard, you can set up your boilerplate text (for example, the body of the letter), and use the fields from your data tables to fill in the variable data (for example, the addressee's name).

To create a form letter report:

  1. Launch Reports Builder (or, if already open, choose File > New > Report)

  2. In the Welcome or New Report dialog box, select Use the Report Wizard, then click OK.

  3. If the Welcome page displays, click Next.

  4. On the Report Type page, select Create Paper Layout Only, then click Next.

  5. On the Style page, type a Title for your report, select Form Letter, then click Next.

  6. On the Data Source page, click SQL Query, then click Next.

  7. On the Data page, enter the following SELECT statement in the Data Source definition field:

    SELECT ALL EMPLOYEES.LAST_NAME, EMPLOYEES.FIRST_NAME, JOBS.JOB_ID, EMPLOYEES.EMPLOYEE_ID, JOBS.JOB_TITLE
    FROM EMPLOYEES, JOBS
    WHERE (EMPLOYEES.JOB_ID = JOBS.JOB_ID)
    ORDER BY EMPLOYEES.EMPLOYEE_ID 
    

    Note:

    You can enter this query in any of the following ways:
    • Copy and paste the code from the provided text file called formletter_code.txt into the Data Source definition field.

    • Click Query Builder to build the query without entering any code manually.

    • Type the code in the Data Source definition field.


  8. Click Next.


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

  9. On the Text page, format the letter the way you want it to appear. The steps that follow will show you how to make your form letter report look like this:

    Employee: &<FIRST_NAME> &<LAST_NAME>

    Emp. #: &<EMPLOYEE_ID>

    Dear &<FIRST_NAME> &<LAST_NAME>:

    The Human Resources department is updating its records of the company's employees. Currently, our records show your employee number as &<EMPLOYEE_ID>, and that you hold the position of &<JOB_TITLE>. If any of this information is incorrect, please contact the Human Resources department.

    Thank you,

    Human Resources

  10. In the Form Letter Text box, type Employee:.

  11. Click Space four times to enter four spaces.

  12. In the Available Fields list, click FIRST_NAME, then click the right arrow (>) to move this field to the Form Letter Text field.

  13. In the Available Fields list, click LAST_NAME, then click the right arrow (>).

  14. Click New Line.

  15. In the Form Letter Text field, type Emp. #:.

  16. Click Space four times to enter four spaces.

  17. In the Available Fields list, click EMPLOYEE_ID, then click the right arrow (>).

  18. Click New Line twice.

  19. In the Form Letter Text field, type Dear.

  20. Click Space.

  21. In the Available Fields list, click FIRST_NAME, then click the right arrow (>).

  22. In the Available Fields list, click LAST_NAME, then click the right arrow (>).

  23. In the Form Letter Text field, type a colon (:) next to LAST_NAME, then click New Line twice.

  24. Type the body of the letter. For the field names, use the Available Fields list to select the appropriate name, then click the right arrow (>) to insert it into the Form Letter Text field. The result should look like this:

    The Human Resources department is updating its records of the company's employees. Currently, our records show your employee number as &<EMPLOYEE_ID>, and that you hold the position of &<JOB_TITLE>. If any of this information is incorrect, please contact the Human Resources department.

    Thank you,

    Human Resources

  25. Click Next.

  26. On the Template page, select Predefined Template and click Beige, then click Finish to display your report output in the Paper Design view. It should look something like this:

    Figure 7-2 Paper Design view for the form letter report

    Description of Figure 7-2  follows
    Description of "Figure 7-2 Paper Design view for the form letter report"

  27. Save the report as formletterreport_your_initials.rdf.

7.3 Summary

Congratulations! You have successfully created a form letter paper report. You now know how to:

For more information on any of the wizards, views, or properties used in this example, refer to the Oracle Reports online Help, which is available in Reports Builder or hosted on the Oracle Technology Network (OTN), as described in Section 3.1.1, "Using the Oracle Reports online Help".