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

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

    Figure 20-6 New Program Unit dialog box

    Description of Figure 20-6 follows
    Description of "Figure 20-6 New Program Unit dialog box"

  4. Click OK.

  5. In the PL/SQL Editor, type the following PL/SQL:

    PACKAGE global IS
      prev_val varchar2(14);
  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 the following properties:

    • Under Advanced Layout, double-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
    If global.prev_val = :department then
        global.prev_val := :department;
      END IF;
  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

    Description of Figure 20-7 follows
    Description of "Figure 20-7 Final report output displayed in the Paper Design view"

  7. Save the report.