35
Building a Paper Report with a Multilevel Table of Contents

Figure 35-1 Multilevel table of contents

Text description of the illustration multoc_toc.gif

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

Text description of the illustration multoc_boy.gif

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 the previous chapter, 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. For information on creating a simple table of contents, refer to Chapter 34, "Building a Paper Report with a Simple Table of Contents and Index".

As in the previous chapter, 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 packages in Oracle Reports, refer to the Reports Builder Online Help.

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 Reports Builder Online Help or, for specific steps, refer to any other chapter in this manual.

Example Scenario

Suppose 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 (e.g., Boys, Girls), then lists each item under each category.

.

Table 35-1 Features Demonstrated in this example

Feature	Location
Use SQL*Plus to create a table in the database to hold the first and second tier categories and page numbers.	Section 35.2, "Create a table in the database to hold the TOC data"
Use the PL/SQL Editor to create two triggers that change the order in which the report sections are executed and fetch the page numbers for each category, sub-category, and record.	Section 35.3, "Create an After Parameter Form trigger and format trigger"
Use the Report Block Wizard to create a simple layout in the Header section of the report layout.	Section 35.4, "Create a layout for the table of contents"

35.1 Prerequisites for this example

To build the examples in this manual, you must have the example files we've provided, as well as access to the sample schema that is shipped with the Oracle9i database.

35.1.1 Example files

If you haven't already done so, you can download the files you'll need to complete this example from the Oracle Technology network (http://otn.oracle.com/products/reports/) and install them on your machine.

To download and install the example files:

1. Go to the Oracle Technology Network Web site (http://otn.oracle.com/products/reports/).

2. Click Getting Started with Oracle Reports.

3. Click Index, then browse through the list of examples and find the "Building a Report with a Table of Contents and Index" example.

4. Download the file formatorder.zip into a temporary directory on your machine (e.g., d:\temp).

5. Unzip the contents of the file, maintaining the directory structure, into an examples directory on your machine (e.g., d:\orawin90\examples).

   This zip file contains the following files:

   Table 35-2 Files necessary for building a report with a table of contents

   File	Description
   Examples\MultiLevelTOC\source\multilevel_source.rdf	The source file that contains a basic paper layout and data model for your report.
   Examples\MultiLevelTOC\result\multilevel_toc.rdf	The final RDF version of the report with a multilevel table of contents.
   Examples\MultiLevelTOC\scripts\multilevel_code.txt	The various SQL statements you will use in this report.

   Note: The index.html file and assets directory are used as part of the Getting Started with Oracle Reports Web site. Please do not delete or move these files

35.1.2 Access to the sample schema

If you don't know if you have access to the sample schema provided with the Oracle9i database, contact your database administrator. You should have access to the "Sales History" portion of the schema to complete this example. Typically, you can log into this schema with the user ID and password "sh/sh", then enter the name of the database.

35.2 Create a table in the database to hold the TOC data

The steps in this section will show you how to create a table in the database that will hold the page numbers for the main and sub-categories in the report. If you are not sure if you can create a table in the database, contact the database administrator.

To create a table in the database:

1. Create a new table in the Sales History database schema, using the following code:

   create table toc_multilevel (main_topic varchar2(100), sub_topic 
   varchar2(100), page number);
   
   

   Tip: To create a table, use SQL*Plus and connect to the sample schema provided with the Oracle9i database called "Sales History." Typically, you connect to this database using the user ID and password "sh/sh." If you do not have access to this schema, contact your database administrator.

2. Press Enter.

3. You should see a notification that the table has been created.

4. Exit SQL*Plus.

35.3 Create an After Parameter Form trigger and 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 35.2, "Create a table in the database to hold the TOC data".

35.3.0.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:

1. In Reports Builder, open the provided file called multilevel_source.rdf in the source directory.

2. In the Object Navigator, under your report name (MULTILEVEL_SOURCE), expand the Report Triggers node.

3. Right-click AFTER PARAMETER FORM, then choose PL/SQL Editor from the pop-up menu.

4. 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.

5. Click Compile.

6. Once the code has compiled, click Close.

7. Save your report as multilevel_<your initials>.rdf.

35.3.0.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:

1. 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 can't find this field, use the Find field at the top of the Object Navigator.

2. Press F4 to display the Property Inspector for this field.

3. Under Advanced Layout, click the Format Trigger property field to display the PL/SQL Editor.

4. 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.

5. Click Compile.

6. Once the code is compiled, click Close, then close the Property Inspector.

7. Save your report.

35.3.1 Create a second query in the data model

The steps in this section will show you how to create a second query in the data model that retrieves the information stored in the table you created in Section 35.2, "Create a table in the database to hold the TOC data". This query will fetch the data necessary for generating the table of contents in the Header section of your report.

To create a query:

1. In the Data Model view, click the SQL Query tool in the tool palette, then click in the main area (canvas region) of the window to the right of query Q_1.

2. In the SQL Query Statement dialog box, type the SELECT statement for the query:

   SELECT * FROM TOC_MULTILEVEL
   
   

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

3. Click OK.

4. In the new query, click and drag the MAIN_TOPIC column to a separate group above the rest of the query.

5. Double-click the new summary column object to display the Property Inspector, and set properties:
   • Under General Information, set the Name property to Subcategory_Page

   • Under Summary, set the Function property to Minimum and set the Source property to PAGE

   Your data model should now look like this:

Figure 35-3 Data model for the multilevel TOC report

Text description of the illustration multoc_dm.gif

6. Save your report.

35.4 Create a layout for the table of contents

To display the TOC in your report, you will need to create a layout in the Header section of the report. In doing so, the multilevel TOC displays at the beginning of your report. You will also add a field to your layout to display the page number for each category.

To create a layout for the TOC:

1. In the Paper Layout view, click the Header Section button in the toolbar.

2. Click the Report Block tool in the tool palette, then draw an area about 6 inches long and 1.5" inches long. Release the mouse button to display the Report Block Wizard.

3. In the Report Block Wizard, on the Style page, select Group Above, then click Next.

4. On the Groups page, click G_MAIN_TOPIC, then click Down to move it to the Displayed Groups list.

5. In the Available Groups list, click G_1, then click Down to move it to the Displayed Groups list.

6. Click Next.

7. On the Fields page, use > to move the MAIN_TOPIC, SUB_TOPIC, and PAGE fields to the Displayed Fields list, then click Next.

8. On the Labels page, click Next.

9. On the Template page, click Finish.

Your report block displays in the Paper Layout view:

Figure 35-4 Paper Layout view of the Header Section

Text description of the illustration multoc_lay.gif

10. Click the Field tool in the tool palette and create a field above the PAGE field, next to the F_MAIN_TOPIC field.

11. Double-click the new field object to display the Property Inspector, and set properties:
    • Under Field, set the Source property to Subcategory_Page, then click the Page Numbering property field to display the Page Numbering dialog box:

    • In the Reset at list, click G_1, and verify that the Start at and Increment by values are both 1.

12. Save your report.

35.4.1 Run your multilevel table of contents report to paper

The steps in this section will show you how to run your report and make a few modifications in the Paper Design view.

1. In the Paper Layout view, click the Run to Paper button in the toolbar.

2. In the Paper Design view, change the font of the field (F_1) to Arial, Bold.

3. Change the Fill color to Beige, and the Line Color to No Line Color.

   The table of contents displays on the first page of the report, and looks like the following:

   Note: The report may take a few minutes to run. Also note that to generate the table of contents (TOC), you must click the Run to Paper button. If you click the Paper Design view button, the change of format order will not take effect, thus the TOC will not be generated.

Figure 35-5 Table of contents page of the report

Text description of the illustration multoc_toc.gif

When you move to, for example, page 30 (which is between the page range of 22 and 38), you'll see the SHIRTS sub-category under the BOYS product department:

Figure 35-6 Results for the BOYS product department and SHIRTS sub-category

Text description of the illustration multoc_boy.gif

You can navigate to various pages in your report to view the data for each department and sub-category.

Note: At this point, you can compare your report against the example file we've provided, multilevel_toc.rdf. First, compile the PL/SQL by choosing Program > Compile > All, then run the report to paper.

35.5 Summary

Congratulations! You have successfully created a report with a table of contents You now know how to:

• create a multilevel table of contents for a large paper report.

• Use the SRW.SET_FORMAT_ORDER built-in procedure to change the order in which the report sections are formatted

• create a format trigger that fetches the page numbers for categories and subcategories that you specify.

• use the Report Block Wizard to create a simple group above report layout to display your table of contents.

To learn how to add hyperlinks to your table of contents, refer to the examples in the Getting Started with Oracle Reports Web site, located on the Oracle Technology Network (http://otn.oracle.com/products/reports/).

For more information on any of the wizards, views, or properties used in this example, refer to the Reports Builder Online Help, which you can access in two ways:

• From the Oracle Technology Network (http://otn.oracle.com/products/reports/), click Documentation and navigate to the Reports Builder Online Help for the most recent, hosted online help.

• From Reports Builder, choose Help > Help Contents.

  Note: You can replace your Reports Builder online help with the most recent update by downloading the latest online help set available on the Oracle Technology Network (http://otn.oracle.com/docs/products/reports/). Instructions for replacing your help file are included in the readme.txt in the download file.