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. |
Format triggers do not affect the data retrieved by the report. For example, if a format trigger returns FALSE for a field, the data for the field is retrieved even if the field does not appear in the output.
If a format trigger suppresses report output on the last page of the report, the last page will still be formatted and sent to the appropriate output and the page will be included in the total number of pages.
Because you cannot be sure how many times a format trigger will fire for a particular object, you should not perform calculations or use DML in a format trigger.
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.
If a format trigger returns false for an object on the last page of your report and no other objects are formatted on the last page, the last page be a blank page.
Comments inserted directly into the PL/SQL code must use the PL/SQL comment delimiters.
In a format trigger, you can read the values of Reports Builder columns and parameters of the correct frequency (look at the rule below), but you cannot directly set their values. For example, you can use the value of a parameter called COUNT1 in a condition (for example, IF :COUNT1 = 10), but you cannot directly set its value in an assignment statement (for example, :COUNT1 = 10). (This restriction also applies to user exits called from the format trigger.) Note also that the use of PL/SQL global variables to indirectly set the values of columns or parameters is not supported. If you do this, you may get unpredictable results.
You cannot reference columns or variables in the format trigger of an object that have a different frequency than the object. For example, if you create a master/detail report, the parent group's repeating frame cannot have a format trigger that relies on a value in the child group. For each parent, there may be multiple children. Therefore, at the parent record level, Reports Builder cannot determine which of the child records to use. You also cannot reference any page-dependent columns (that is, Reset At of Page) or columns that rely on page-dependent columns in a format trigger. The reason for this is that it would result in a circular dependency. That is, the value of a page-dependent column cannot be computed until the report is formatted, but the report cannot be formatted until the format trigger is executed.
If a format trigger returns false and the object does not format, this can cause other objects not to print. For example, if a repeating frame does not format, any objects (fields, boilerplate, frames, or other repeating frames) it encloses would not format either.
For repeating frames, the format trigger is executed for each instance of the repeating frame. To create a format trigger that acts upon all instances of a repeating frame at once, create a frame around the repeating frame and enter a format trigger for the frame. If the format trigger returns FALSE for every instance of a repeating frame on a logical page, the repeating frame will occupy no space on the logical page and anchors to other objects will collapse (if specified).
The PL/SQL in a format trigger must return consistent results for the same object. For example, say you have a frame whose format trigger returns FALSE when a certain condition is met. If the frame spans two pages, the format trigger actually fires twice (once for each page on which the frame formats). The condition in your PL/SQL must return the same result both times the format trigger fires. Otherwise, only part of the frame will be formatted (for example, the part of the frame on the first page formats, but the part on the second page does not).
If the format trigger on a repeating frame in a matrix report returns FALSE, an entire row or column of the matrix will not format. For example, if an instance of the across dimension repeating frame does not format, the entire column will not format in the matrix.
If you want to conditionally change the cell of a matrix, you should put a frame around the field inside the matrix and use the format trigger for the frame.
The following example show how you can use format triggers in your reports:
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;
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;
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;
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;
This example creates a comma-separated list of employee names using the following steps:
Create a field object inside a repeating frame with a Print Direction of Across.
Next to the field, create a boilerplate text object that contains a comma followed by a space.
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;
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;
Creating or editing a format trigger
Copyright © 1984, 2005, Oracle. All rights reserved.