A script-enabled browser is required for this page to function properly.

Format trigger

Format triggers are PL/SQL functions executed before the object is displayed. The trigger can be used to dynamically change the formatting attributes of the object. The function must return a Boolean value (TRUE or FALSE). Depending on whether the function returns TRUE or FALSE, the current instance of the object is included or excluded from the report output. You can access format triggers from the Object Navigator, the Property Inspector, or the PL/SQL Editor.

Note:   The PL/SQL in a format trigger is executed for each instance of its object. The lower the frequency of the object, the fewer times the PL/SQL will be executed and the faster the report will run. For example, PL/SQL in the format trigger of a frame instead of a field typically makes the report run faster.

Definition Level

Layout object

On Failure

Excludes the current instance of the object from the output.

Usage notes

Restrictions

Caution: The PL/SQL in a format trigger is executed each time that Report Builder attempts to format the layout object. As a result, format triggers should only contain PL/SQL program units that set formatting attributes (for example, color and highlighting). You should not perform other actions, such as inserting data in a table, because you cannot predict when or how many times the trigger will fire. For example, if you have Page Protect set for an object, the object might not be formatted on the logical page where the trigger is fired. In addition, the trigger may be executed more than once.

Examples

The following example show how you can use format triggers in your reports:

Example 1:   Highlighting a value

Suppose that you are building a banking report and would like it to indicate if a customer is overdrawn. To do so, you give the repeating frame around the customer information a format trigger that causes it to have a border only if a customer's account balance is less than 0 (or the required minimum balance).


function my_formtrig return BOOLEAN is
begin
  if :bal < 0 then
    srw.set_border_width := 1;
  end if;
  return (true);
end;

Example 2: Highlighting a row

The following code applies conditional formatting to a repeating frame. If the condition (compensation exceeds four thousand dollars a month and the employee is not a manager) is met, the entire row will have a red background color.


function R_G_EMPNOFormatTrigger return boolean is varcomm number; 

begin 
  if :comm is null then 
    varcomm := 0;
  else
    varcomm := :comm;
  end if;
  
  if (:sal * 2 + varcomm > 4000) and :job != 'MANAGER' then
    srw.set_background_fill_color('red');
  end if;
  return (TRUE);
end;

Example 3: Suppressing labels

Suppose that you are building a master/detail report and, if no detail records are retrieved for a master record, you do not want the boilerplate labels to appear. To do this, you first create a summary column called MYCOUNT with a Function of Count in the source group of the master repeating frame. In the format trigger for the group frame that surrounds the detail repeating frame and its labels, you enter the following:


function my_formtrig return BOOLEAN is
begin 
  if :mycount = 0 then
    return (false);
  else
    return (true);
  end if;
end;

Example 4: Suppressing values

Suppose that you are building a salary report and you only want to see salaries above $2500. In the report summary of all salaries, however, you want all salaries to be included. To do this, you create a data model for the report without restricting the records retrieved. Then, you enter the following format trigger for the repeating frame. The report output will only show salaries greater than $2500, but the summary that calculates the sum of all salaries will include all the salaries retrieved from the database.

function my_formtrig return BOOLEAN is
begin
  if :sal > 2500 then
    return (true);
  else
    return (false);
  end if;
end;

Example 5: Inserting commas between fields

This example creates a comma-separated list of employee names using the following steps:

  1. Create a field object inside a repeating frame with a Print Direction of Across.

  2.   Next to the field, create a boilerplate text object that contains a comma followed by a space.

  3. To ensure that the comma does not appear after the last name in the list, enter the following format trigger for the boilerplate object. LASTNAME is a summary column with a Source of ENAME, a Function of Last, and Reset At of Report.


    function my_formtrig return BOOLEAN is
    begin
      if :ename <> :lastname then
        return (true);
      else
        return (false);
      end if;
    end;

Example 6:   Adding blank lines between groups of rows

The following inserts blank space between groups of rows (displays a boilerplate rectangle when the row count divided by the value of SPACE leaves no remainder).


function spacing return BOOLEAN is
begin
  if :CountENAMEPerReport MOD :SPACE = 0 then
    return (true);
  else 
    return (false);
  end if;
end;

See also

About triggers

About format triggers

Creating or editing a format trigger

Highlighting a row