How to use Format Triggers in Reports (Doc ID 31364.1)

Using Format Triggers in Oracle Reports
---------------------------------------


Purpose:
--------

This document describes some ways that format triggers can be utilized.  It
gives examples on how to suppress data, change the value of a field, and so
forth.  Format triggers are PL/SQL functions and return a Boolean value
(TRUE or FALSE).  Oracle Reports' packaged functions, procedures and exceptions
can be referenced in the triggers.  Format triggers are executed each time
that Oracle Reports attempts to format the layout object.

To suppress a record or change a value of a column based on a condition,
create a format trigger on the item.  If you are basing the condition in the
trigger based upon a value of an object in the report, make sure that you
reference it at the correct frequency.

Case 1.
-------

To display zeroes in a number column (ex. comm) when the column is null,
create a format trigger on that column with the syntax:

        FUNCTION my_formattrig RETURN BOOLEAN IS
        BEGIN
          IF :comm IS NULL THEN
             SRW.SET_FIELD_NUM(0,'0');
          END IF;
          RETURN TRUE;
        END;

Or, to perform a calculation on this field and display the new value,
the trigger would look like:

      FUNCTION my_formattrig RETURN BOOLEAN IS
        tmp NUMBER
      BEGIN
        IF :comm IS NOT NULL THEN
           tmp := :comm * 1.5;
           SRW.SET_FIELD_NUM (0,tmp);
        END IF;
        RETURN TRUE;
      END;

The same type of triggers can be performed on date and char fields using
SRW.SET_FIELD_CHAR and SRW.SET_FIELD_DATE.


Case 2.
-------

In a master/detail report, suppose no detail records are retrieved for a
master record.  The master record still needs to be displayed, but the headers
of the detail records need to be suppressed.

    STEP 1:  Create a summary column called "count_col" that counts the records
             in the detail group (set reset at the master group).
    STEP 2:  Create a format trigger on the M_GRPFR of the detail group that
             looks like the following:

                FUNCTION my_formattrig RETURN BOOLEAN IS
                BEGIN
                  IF :count_col = 0 THEN
                     RETURN (FALSE);
                  ELSE
                     RETURN (TRUE);
                  END IF;
                END;

Case 3.
-------

Suppose you are building a report and you only want to see certain records
based on a condition.  Normally, you would place the condition in your query
and simply not retrieve the records.  However, if you want the records
retrieved, but not displayed (that is, you may want to do a summary of all
records retrieved, just not display them all), then create the following format
trigger on the repeating frame.  In this example, we will print all employees
whose commission is greater than 3000.

            FUNCTION my_formattrig RETURN BOOLEAN IS
                BEGIN
                  IF :comm > 3000 THEN
                     RETURN (TRUE);
                  ELSE
                     RETURN (FALSE);
                  END IF;
                END;

Case 4.
-------

Suppose you want to suppress records/columns and change column values, column
headers, report titles/headers, and so forth, based on input from the end user.
Simply create the parameter that would be used to get the input from the user,
and create a format trigger on the object.

                FUNCTION my_formattrig RETURN BOOLEAN IS
                BEGIN
                  IF :p1 = 'condition1' THEN
                     SRW.SET_FIELD_CHAR (0,'This is my new value.');
                  ELSIF :p1 = 'condition2' THEN
                     SRW.SET_FIELD_CHAR (0,'This is my new value 2.');
                  END IF;
                  RETURN (TRUE);
                END;

Case 5.
-------

To display an object on a spcific page, in the Object Navigator, expand the
Layout Model node.  Next expand the node that contains the object that you
want to display.  
        
Right click on the item that you would like to display or not display based on
your condition in a trigger.  Select the 'PL/SQL Editor' option.

In the PL/SQL Editor, define the PL/SQL for the format trigger.
        
Format trigger example:
       
  function <your_field or frame>FormatTrigger return boolean is
  pageno number;
  begin
    srw.get_page_num(pageno);
      if pageno = 1
          then
         return (true);
      else
         return (false);
   end if;
  end;
        
The srw.get_page_num function will return the current page number.  This is
useful when you want to include or exclude item based on the page number.
       
Syntax example for srw.get_page_num:
        
  SRW.GET_PAGE_NUM (page_num);
        
Parameters
        
page_num        Defines the variable in which you want to place the current
                page number. 

Format triggers can be very useful and very powerful when formatting your
report.  They can be used to highlight, suppress, and alter columns and
headings or to print objects on a specific page.  Summarized below are the
basic rules to using format triggers:

Format Triggers Rules:

1.  Use PL/SQL delimiters when inserting comments.

2.  You can read columns and parameters of the correct frequency, but you
    cannot directly set their values (such as,  :count1 = 10).

3.  You cannot reference columns or variables that have a different frequency
    than the object being formatted. (For example, in a master/detail report, a
    format trigger on the master cannot reference items in the detail, because
    there are multiple details for the master, and Oracle Reports cannot
    determine which child record to use.

4.  If the format trigger returns false and the object does not format, this
    can cause other objects not to print.  (For example, if a format trigger on a
    group frame does not format, then anything it encloses will also not
    format.)

5.  Repeating Frames - the format trigger is executed for each record returned
    in that repeating frame.

6.  The format trigger must return consistent results for the same object.
    (For example, if the object spans two pages, then the format trigger will
    execute twice, once for each page.)

7.  If the format trigger on a repeating frame in a matrix report returns
    false, the entire row or column of the matrix will not format.

8.  To conditionally change the cell of a matrix, put a frame around
    the field inside the cell and put the format trigger on that frame
    Make sure that you place this frame on the correct level.

_______________________________________________________________________________