Skip Headers
Oracle® Reports Building Reports
10g Release 2 (10.1.2)
B13895-01
  Go To Documentation Library
Library
Go To Product List
Product
Go To Table Of Contents
Contents
Go To Index
Index

Previous
Previous
Next
Next
 

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  follows
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  follows
Description of "Figure 36-2 Main category with sub-category sample page in the report"

Concepts

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:

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:

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

  2. Connect to the Sales History schema of the database (for example, use the sh/sh@database_name connect string).

  3. At the SQL prompt, type the following line:

    create table toc_multilevel (main_topic varchar2(100), sub_topic varchar2(100), page number);
    
    
  4. Press Enter.

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

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

  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.

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:

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

  2. Choose Tools > Property Inspector to display the Property Inspector for this field.

  3. Under Advanced Layout, double-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.

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:

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

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

  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. Click the Summary Column tool in the tool palette, then click in the new group object to create a summary column.

  6. 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  follows
    Description of "Figure 36-3 Data model for the multilevel TOC report"

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

  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 (15 centimeters) wide and 1.5 inches (4 centimeters) high. 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, 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

  8. On the Labels page, clear all the labels, then click Next.

  9. 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  follows
    Description of "Figure 36-4 Paper Layout view of the Header Section"

  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 the following properties:

    • Under Field, set the Source property to Subcategory_Page.

    • Under Summary, set the Reset At property to G_1.

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

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

  2. Change the Fill color to any color (for example, Beige), and the Line Color to No Line Color.

  3. 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  follows
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  follows
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:

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