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".
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.
In Reports Builder, open the provided file
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;
You can also copy and paste the code from the provided file,
Once the code has compiled, click Close.
Save your report as
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.
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.
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;
You can also copy and paste this code from the provided file,
Once the code is compiled, click Close, then close the Property Inspector.
Save your report.