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
 

17 Building a Header with Database Values Report

In this chapter, you will learn about reports with headers that include values queried from the database. By following the steps in this chapter, you can generate the report output shown in Figure 17-1.

Figure 17-1 Group left report output with database values in header

Description of Figure 17-1  follows
Description of "Figure 17-1 Group left report output with database values in header"

Concepts

In this example report, both the first and last department numbers found on each page are displayed in the page header.

Layout

This report uses one query and two groups. You'll also create two summary columns to provide the values for the header fields. To ensure unique field values for each page, you'll compute the values using the First and Last functions. The First function will return the first database value selected for a group, page, or report, and the Last function will return the last database value selected for a group, page, or report.

Layout

This report uses the default group left format with no modifications.

Example Scenario

In this example, you will create a report that displays and summarizes employee data by department. To make the report more readable, you decide to add a header to each page that indicates which departments appear on the page.

As you build this example report, you will:

To see a sample header with database values report, open the examples folder named headingdb, then open the Oracle Reports example called headingdb.rdf. For details on how to access it, see "Accessing the Example Reports" in the Preface.

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

17.2 Create a data model and a group left layout

When you are creating a single-query report, such as this one, you can use the Report Wizard to create the data model and layout simultaneously.

To create a data model and layout:

  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 Group Left, 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 DEPARTMENT_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY 
    FROM EMPLOYEES 
    ORDER BY DEPARTMENT_ID, 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 heading_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 17.1, "Prerequisites for this example" describes the sample schema requirements for this example.

  9. On the Groups page, click DEPARTMENT_ID and click the right arrow (>) to move this field to the Group Fields list, then click Next.

  10. On the Fields page, click the double right arrows (>>) to move all of the fields to the Displayed Fields list, then click Next.

  11. On the Totals page, click Next.

  12. On the Labels page, change the labels as follows, then click Next:

    Fields Labels
    DEPARTMENT_ID Department
    JOB_ID Job

  13. On the Template page, click Finish to display your report output in the Paper Design view.

  14. In the Paper Design view, click the first number value underneath the Salary label.


    Tip:

    If you are familiar with format mask syntax, you could now right-click the field values, choose Property Inspector, and choose or manually enter a value for the Format Mask property.

  15. Click the Currency button in the toolbar. A currency symbol immediately appears next to all of the values.

  16. Click the Add Decimal Place button twice. Two decimal places are added to the right of the decimal point.

  17. Resize the field by clicking and dragging the rightmost handle of the field approximately 0.5 inches (1.5 centimeters) to the left.

  18. Shift-click the Salary label itself.

  19. Click the Align Right button in the toolbar. All of the values and the Salary label are immediately right aligned.

  20. Click the first department number value under the Department label to select it.

  21. Click the Align Center button in the toolbar.

    Your report should look something like this:

    Figure 17-2 Group left report output with values formatted

    Description of Figure 17-2  follows
    Description of "Figure 17-2 Group left report output with values formatted"

  22. Save your report as headingdb_your_initials.rdf.

17.3 Add summary columns for the header data

For the header that you want to create (that is, departments found on this page), you need to create two summary columns that compute the values you need in order to populate the header.

  1. In the Report Editor, click the Data Model button in the toolbar to display the Data Model view.

  2. Click the Summary Column tool in the tool palette, then click in an open area of the Data Model view to create a summary column.

  3. 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 FirstDeptRec.

    • Under Summary, set the Function property to First, set the Source property to DEPARTMENT_ID, set the Reset At property to Page.

  4. Create a second summary column, and set its properties as follows:

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

    • Under Summary, set the Function property to Last, set the Source property to DEPARTMENT_ID, set the Reset At property to Page.

    Your data model should now look like this:

Figure 17-3 Data Model with Summary Columns for Header

Description of Figure 17-3  follows
Description of "Figure 17-3 Data Model with Summary Columns for Header"

17.4 Add a page heading

Now that you have the data for the header, you need to add the corresponding layout objects to the margin area of the report.

  1. In the Paper Design view, from the font lists in the toolbar, choose Arial (Western), point size 10.

  2. In the Paper Layout view, click the Edit Margin button in the toolbar.

  3. Click the Text tool in the tool palette.

  4. Draw an area somewhere to the right of the logo image and type the following text:

    Departments found on this page: &FirstDeptRec through &LastDeptRec
    
  5. Move to an open area of the Paper Layout view and click the mouse button to exit text mode. Notice that the text object you just created is still selected, you can now adjust its positioning with the arrow keys. If you click in an open area a second time, the object is deselected.

  6. Choose Program > Run Paper Layout to run your report.

    Your report should now look like this:

    Figure 17-4 Group left report output with database values in the header

    Description of Figure 17-4  follows
    Description of "Figure 17-4 Group left report output with database values in the header"

  7. Save your report.

17.5 Summary

Congratulations! You have successfully created a header with database values 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".