20
Building an Intermixed Fields Report

Figure 20-1 Group with master record in the middle report output

Text description of the illustration intermx_fin.gif

Normally, a group (break) field appears to the left of (in group left report) or above (in group above report) its related fields. In this example, the group field appears between its related fields.

Concepts

• To create this type of report you need to build a data model with two groups.

• A formula column in the detail group returns the value of the group column. The formula column is moved to the middle position of its group.

• The Report Wizard is used to create a group above layout. In the Paper Layout view, the break column is deleted from its defaulted position.

• A format trigger is created to suppress redundant printing of the formula column group value in the detail group.

Example Scenario

Suppose that you want to create a report that displays salary data by employee and department. Your users have indicated to you that they prefer the group value (i.e., department name) to appear in the middle of the layout with employee names to the left and employee salaries to the right.

To see a sample report that intermixes fields, open the examples folder named intermix, then open the Oracle Reports example named intermixb.rdf. For details on how to access it, see "Accessing the example reports" in the Preface.

Table 20-1 Features demonstrated in this example

Feature	Location
Create a data model and layout in the Report Wizard	Section 20.2, "Create a data model and a layout"
Add a formula column to the detail group	Section 20.3, "Add a formula column"
Add a field in the Report Wizard	Section 20.4, "Add a field"
Remove a redundant field in the Paper Design view	Section 20.5, "Remove a redundant field"
Suppress values in a Format Trigger	Section 20.6, "Suppress redundant values"

20.1 Prerequisites for this example

To build the example in this chapter, you must have access to the EMP and DEPT schema, which is provided by default with the Oracle database. The userid and password for accessing this schema is scott/tiger.

20.2 Create a data model and a 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 group above 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 Above, 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 DEPT.DNAME, EMP.ENAME, EMP.SAL
   FROM DEPT, EMP
   WHERE (EMP.DEPTNO = DEPT.DEPTNO)
   

   Note: You can enter this query in any of the following ways: Copy and paste the code from the provided text file called intermxb_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.

Click Next.

Note: If you are not already connected to a database, you will be prompted to connect to the database when you click Next. Ensure that you connect to a database that has the appropriate schema for this example. Section 20.1, "Prerequisites for this example" describes the sample schema requirements for this example.

9. On the Groups page, click DNAME in the Available Fields list and click the right arrow (>) to move this field to the Group Fields list.

Figure 20-2 Group page of Report Wizard

Text description of the illustration intermx_grp.gif

10. Click Next.

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

12. On the Totals page, click Next.

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

    Fields	Labels
    DNAME	Dept.
    ENAME	Name
    SAL	Salary

14. On the Template page, click Finish to display your report output in the Paper Design view. It should look something like this:

Figure 20-3 Paper Design view for intermixing fields report

Text description of the illustration intermx_out1.gif

20.3 Add a formula column

In order to have the department names appear in the center of this group report, it is simplest to have a column in the detail group for the department names. To achieve this result, you must create a formula column in the detail group that will display the department names.

Add a formula column

1. Click the Data Model tool in the toolbar to display the Data Model view.

2. In the Data Model view, resize the G_ENAME group to be large enough to contain a third column.

3. Click the Formula Column tool in the tool palette.

4. Click in the G_ENAME group to create a formula column.

5. Double-click the new formula column object (CF_1) to display the Property Inspector, and set properties:
   • Under General Information, set the Name property to DEPARTMENT.

   • Under Column, set the Datatype property to Character, and set the Width property to 14.

   • Under Placeholder/Formula, double-click the PL/SQL Formula property field to display the PL/SQL Editor.

6. In the PL/SQL Editor, at the flashing cursor (after the word begin), type the following:

   return(:dname);
   

7. Click Compile.

8. Click Close.

9. Click the title bar of the Report Editor to make it the active window again.

20.4 Add a field

Now that you have added the formula column to the data model, you need to add a corresponding field to display it. You can easily do this by invoking the re-entrant Report Wizard.

To add a field:

1. Choose Tools > Report Wizard.

2. On the Report Type page, select Create Paper Layout Only.

3. On the Fields page:
   • Click DEPARTMENT in the Available Fields list and click the right arrow (>) to move it to the Displayed Fields list.

   • Click and drag DEPARTMENT in the Displayed Fields list until it is located between ENAME and SAL.

4. Click Finish to preview your report output in the Paper Design view. It should look something like this:

Figure 20-4 Paper Design view of the intermixing fields report

Text description of the illustration intermx_out2.gif

20.5 Remove a redundant field

After you have added a field for the formula column, you will notice that the department values appear in two places. To eliminate this redundancy, you need to remove the master field and its heading.

To remove a field and its label:

1. In the Paper Design view, Shift-click on the first instance of the label Dept. and the value next to it.

2. Choose Edit > Delete.

Figure 20-5 Output displayed in Paper Design view

Text description of the illustration intermx_out3.gif

20.6 Suppress redundant values

Notice in your output that the department values are properly positioned, but they repeat for every record in the department. What you really want is for the department values to appear once for each department. To accomplish this task, you will first create a global variable to be used in comparing the current department value to the previous one. You will then write a Format Trigger to determine which values to suppress based upon the comparison within each department's records.

To create a global variable:

1. In the Object Navigator, click the Program Units node.

2. Click the Create button in the toolbar. The New Program Unit dialog box appears.

3. Type global in the Name field and select Package Spec.

Figure 20-6 New Program Unit dialog box

Text description of the illustration intermx_npu.gif

Click OK.

In the PL/SQ Editor, type the following PL/SQL:

PACKAGE global IS
  prev_val varchar2(14);
END;

6. Click Compile

7. Click Close.

To add the format trigger:

1. In the Object Navigator, type F_DEPARTMENT in the Find field to select it.

2. Double-click the properties icon to the left of F_DEPARTMENT to display the Property Inspector, and set properties:
   • Under Advanced Layout, click the Format Trigger property field to display the PL/SQL Editor.

3. In the PL/SQL Editor, use the template to enter the following PL/SQL code:

   function F_DEPARTMENTFormatTrigger return boolean is
   begin
   If global.prev_val = :department then
     return(false);
     Else
       global.prev_val := :department;
       return(true);
     END IF;
   end;
   
   
   

4. Click Compile.

5. Click Close.

6. Click the title bar of the Report Editor to make it the active window. Return to the Paper Design view if you are not already there. Notice the change in your report output.

Figure 20-7 Final report output displayed in the Paper Design view

Text description of the illustration intermx_fin.gif

20.7 Summary

Congratulations! You have successfully created an intermixed fields report. You now know how to:

• create a data model and layout in the Report Wizard.

• add a formula column to the detail group.

• add a field in the Report Wizard.

• remove a redundant field in the Paper Design view.

• suppress values in a Format Trigger.

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.