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.

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 result\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.

Table 36-1 Example report files

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.

36.1 Prerequisites for this example

To build the example in this chapter, you must have the example files we have provided (see "Example Scenario", above), as well as access to the Sales History sample schema provided with the Oracle Database. If you do not know if you have access to this sample schema, contact your database administrator.

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

Start SQL*Plus.
- On Windows, from the Start menu, choose Programs > Oracle Application Server - oracle_home_name > Application Development > SQL Plus.
- On UNIX, type sqlplus.
Connect to the Sales History schema of the database (for example, use the sh/sh@database_name connect string).

At the SQL prompt, type the following line:

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

Press Enter.

You should see a notification that the table has been created.
Exit SQL*Plus.

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 called multilevel_source.rdf in the source directory.
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.

36.4 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 36.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:

In the Data Model view, click the SQL Query tool in the tool palette then click in an open area of the Data Model view, somewhere to the right of query Q_1, to display the SQL Query Statement dialog box.
In the SQL Query Statement field, enter the following SELECT statement:
```
SELECT * FROM TOC_MULTILEVEL
```
Note:
You can also copy and paste this code from the provided file, called multilevel_code.txt.
Click OK.
In the new query, click and drag the MAIN_TOPIC column to a separate group above the rest of the query.
Click the Summary Column tool in the tool palette, then click in the new group object to create a summary column.
Double-click the new summary column object to display the Property Inspector, and set the following 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 36-3 Data model for the multilevel TOC report

Description of "Figure 36-3 Data model for the multilevel TOC report"
Save your report.

36.5 Create a report block to display the table of contents

The steps in this section will show you how to create a group above report block in the Header section of your report. This report block will display two levels of headings in the table of contents. You will also add a field to your layout to display the page number where each heading is found in report.

To create a group above report block in the Header section:

In the Paper Layout view, click the Header Section button in the toolbar.
Click the Report Block tool in the tool palette, then draw an area about 6 inches (15 centimeters) wide and 1.5 inches (4 centimeters) high. Release the mouse button to display the Report Block Wizard.
In the Report Block Wizard, on the Style page, select Group Above, then click Next.
On the Groups page, click G_MAIN_TOPIC, then click Down to move it to the Displayed Groups list.
In the Available Groups list, click G_1, then click Down to move it to the Displayed Groups list.
Click Next.
On the Fields page, click each of the following fields in the Available Fields list, then click the right arrow (>) to move them to the Displayed Fields list, then click Next.
- MAIN_TOPIC
- SUB_TOPIC
- PAGE
On the Labels page, clear all the labels, then click Next.
On the Template page, click Finish.

Your report block displays in the Paper Layout view:

Figure 36-4 Paper Layout view of the Header Section

Description of "Figure 36-4 Paper Layout view of the Header Section"
Click the Field tool in the tool palette and create a field above the PAGE field, next to the F_MAIN_TOPIC field.
Double-click the new field object to display the Property Inspector, and set the following properties:
- Under Field, set the Source property to Subcategory_Page.
- Under Summary, set the Reset At property to G_1.
Save your report.

36.6 Run your multilevel table of contents report to paper

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

To modify and run your report to paper:

In the Paper Layout view, change the font of the field (F_1) to Arial, Bold.
Change the Fill color to any color (for example, Beige), and the Line Color to No Line Color.

Click the Run Paper Layout button in the toolbar.

Note:

To generate the table of contents (TOC), you must click the Run Paper Layout 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.

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

Figure 36-5 Table of contents page of the report

Description of "Figure 36-5 Table of contents page of the report"

In this example, under the BOYS product department, the SHIRTS sub-category starts at page 22 and the next sub-category, SHOES, starts at page 38. If you move to any page between page 22 and 38 in the report, you will see the SHIRTS sub-category.

Note:

The page numbers may differ depending upon the data used in your report.

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

Description of "Figure 36-6 Results for the BOYS product department and SHIRTS sub-category"

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 have provided, multilevel_toc.rdf. First, compile the PL/SQL by choosing Program > Compile > All, then run the report to paper.

36.7 Summary

Congratulations! You have successfully created a report with a multilevel 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.

For more information on any of the wizards, views, or properties used in this example, refer to the Oracle Reports online Help, which is available in Reports Builder or hosted on the Oracle Technology Network (OTN), as described in Section 3.1.1, "Using the Oracle Reports online Help".