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.
In the Object Navigator, click the Program Units node.
Click the Create button in the toolbar. The New Program Unit dialog box displays.
Type global
in the Name field and select Package Spec.
Click OK.
In the PL/SQL Editor, type the following PL/SQL:
PACKAGE global IS prev_val varchar2(14); END;
Click Compile
Click Close.
In the Object Navigator, type F_DEPARTMENT
in the Find field to select it.
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.
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;
Click Compile.
Click Close.
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
Save the report.