36 Building a Paper Report with a Multilevel Table of Contents

In this chapter, you will learn about reports that include a multilevel table of contents.

By following the steps in this chapter, you can generate the report output shown in Figure 36-1 and Figure 36-2.

Figure 36-1 Multilevel table of contents

Description of "Figure 36-1 Multilevel table of contents"

Figure 36-2 Main category with sub-category sample page in the report

Description of "Figure 36-2 Main category with sub-category sample page in the report"

Concepts

• This chapter assumes that you know how to create a basic data model and layout for your report, and focuses on the building of the table of contents. If you are not sure how to create a data model and paper layout, refer to the Oracle Reports online Help or, for specific steps, refer to any other chapter in this manual.

• When you create a large paper report, you can create a table of contents (TOC) to make it easier for users to navigate through the pages of information. In Chapter 35, you learned how to create a basic table of contents that categorized customer information by country, then listed the country and page ranges in the table of contents. You also learned how to index this information.

• In this chapter, you will learn how to create a multilevel table of contents that will enable you to create a hierarchy. This hierarchy will further enable you to categorize your information, so that users can find a specific piece of information.

• As in Chapter 35, you will use the SRW.SET_FORMAT_ORDER built-in procedure in an After Parameter Form trigger to generate the Main section of your report before the Header and Trailer sections. Doing so enables you to generate the page numbers for the report in the Main section, then display the page numbers in the table of contents in the Header section.

  Note:

  Although you can use SRW.SET_FORMAT_ORDER to change the order in which the report is formatted, the report will still display the Header section first, then the Main section, and finally the Trailer section. You can use SRW.SET_FORMAT_ORDER to simply change the formatting order, but not the display order of the report sections.

• You will also create a format trigger based on a sub-category in your report that will use the SRW.GET_PAGE_NUM built-in package to retrieve the page number for each record and temporarily place it in a database table you create. This format trigger will also get the page numbers for each sub-category that you specify. Finally, you will use a SQL query to display the desired information in a multilevel table of contents that displays at the beginning of your report.

For more information on the SRW built-in package in Oracle Reports, refer to the Oracle Reports online Help.

Example Scenario

In this example, you have a large paper catalog that lists all the clothing products a company sells. This catalog simply lists all the items in a simple report. Since the product line has become much larger, the catalog report now exceeds 700 pages. You will create a multilevel table of contents that sorts the clothing by department (for example, Boys, Girls), then lists each item under each category.

As you build this example report, you will:

• Create a table in the database to hold the TOC data (the first and second tier categories and page numbers) using SQL*Plus.

• Create an After Parameter Form Trigger and a Format Trigger using the PL/SQL Editor to change the order in which the report sections are executed and fetch the page numbers for each category, sub-category, and record.

• Create a Second Query in the Data Model to retrieve the information stored in the table you created.

• Create a Report Block to Display the Table of Contents using the Report Block Wizard to create a group above report block in the Header section to display two levels of headings. You will also add a field to your layout to display the page number where each heading is found in report.

• Run Your Multilevel Table of Contents Report to Paper.

To see a sample report with a multilevel table of contents, open the examples folder named multileveltoc, then open multilevel_toc.rdf. For details on how to open it, see "Accessing the Example Reports" in the Preface. The example files used in this chapter are listed and described in Table 36-1.