36.3 Create an After Parameter Form Trigger and a Format Trigger

The steps in this section will show you how to create two triggers. The first trigger will use the SRW.SET_FORMAT_ORDER built-in procedure to format the Main section of the report first, then the Header and Trailer sections. The second trigger will fetch the page number for each category, sub-category, and record, and place that information into the table you created in Section 36.2, "Create a table in the database to hold the TOC data".

36.3.1 Create an After Parameter Form trigger

An After Parameter Form trigger is a function that executes after the Parameter Form is executed. In this section, you will set the trigger to change the order of the report execution, so that the Main section formats first.

To create an After Parameter Form trigger:

In Reports Builder, open the provided file multilevel_source.rdf.
In the Object Navigator, under your report name (MULTILEVEL_SOURCE), expand the Report Triggers node.
Right-click AFTER PARAMETER FORM, then choose PL/SQL Editor from the pop-up menu.
In the PL/SQL Editor, use the template to enter the following code:
```
function AfterPForm return boolean is
begin
  srw.set_format_order(srw.main_section, srw.header_section, srw.trailer_section);
  return (TRUE);
end;
```
Note:
You can also copy and paste the code from the provided file, multilevel_code.txt.
Click Compile.
Once the code has compiled, click Close.
Save your report as multilevel_your_initials.rdf.

36.3.2 Create a format trigger

In this section, you will create a format trigger based on the field F_PROD_SUBCATEGORY. This field displays the sub-category, which is the product name. This sub-category falls under the main category, which is the product department. This format trigger will fetch the page number for each product name, so that the table of contents will enable the user to navigate to various parts of the report based on the department name, then the product name.

To create a format trigger:

In the Object Navigator, under the Paper Layout node for your report, navigate to Main Section > Body > M_G_PROD_CATEGORY_GRPFR > R_G_PROD_CATEGORY > M_G_PROD_SUBCATEGORY_GRPFR > R_G_PROD_SUBCATEGORY_GPRFR, then click F_PROD_SUBCATEGORY.

Tip:
If you cannot find this field, use the Find field at the top of the Object Navigator.
Choose Tools > Property Inspector to display the Property Inspector for this field.
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 following code:

function F_PROD_SUBCATEGORYFormatTrigge return boolean is
  pageNum number;
  myCount number;
begin
  -- get page number
  srw.get_page_num(pageNum);
  -- check table for duplicates
  select count(*)
  into myCount
  from toc_multilevel
  where main_topic = :PROD_CATEGORY and
  sub_topic = :PROD_SUBCATEGORY;
  -- if no duplicates, insert row
  if myCount = 0 then
    insert into toc_multilevel
    values (:PROD_CATEGORY, :PROD_SUBCATEGORY, pageNum);
  end if;
  return (TRUE);
end;

Note:

You can also copy and paste this code from the provided file, multilevel_code.txt.

Click Compile.
Once the code is compiled, click Close, then close the Property Inspector.
Save your report.