35.2 Create a Simple Table of Contents

The steps in this section will show you how to create a basic table of contents for a large, multipage report. The table of contents will look like the following:

Figure 35-3 Simple table of contents

Description of "Figure 35-3 Simple table of contents"

35.2.1 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 records you want to list in the table of contents. If you are not sure whether you have the privileges to 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_example (topic varchar2(100), page number);

Press Enter.

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

35.2.2 Create a group above report

The steps in this section will show you how to build a simple group above report in Reports Builder. This group above report will display the country name, then the customers and customer e-mail addresses under each country name. The table of contents you create will then be based on the country name in this report.

To create a simple group above report:

In Reports Builder, choose File > New > Report, then choose to create the report manually.
In the Data Model view that displays, right-click on the canvas, then choose Report Wizard from the pop-up menu.
On the Report Type page, select Create Paper Layout only, then click Next.
On the Style page, select Group Above, then click Next.
On the Data Source page, click SQL Query, then click Next.
On the Data page, enter the following SELECT statement in the Data Source definition field:
```
SELECT ALL COUNTRIES.COUNTRY_NAME,
           CUSTOMERS.CUST_LAST_NAME,
           CUSTOMERS.CUST_FIRST_NAME,
           CUSTOMERS.CUST_email
FROM CUSTOMERS, COUNTRIES
WHERE (CUSTOMERS.COUNTRY_ID = COUNTRIES.COUNTRY_ID) 
```
Note:

You can enter this query in any of the following ways:
- Copy and paste the code from the provided text file called toc_index_code.txt into the Data Source definition field.
- Click Query Builder to build the query without entering any code manually.
- Type the code in the Data Source definition field.
Click Next.

Note:

If you are not already connected to a database, you will be prompted to connect to the database when you click Query Builder or Next. Ensure that you connect to a database that has the appropriate schema for this example. Section 35.1, "Prerequisites for This Example" describes the sample schema requirements for this example.
On the Groups page, click COUNTRY_NAME, then click > to move the field to the Group Fields list.
Click Next.
On the Fields page, click >> to move all fields to the Displayed Fields list, then click Next.
On the Totals page, click Next.
On the Labels page, click Next.
On the Template page, click Finish.
In the toolbar, click the Data Model view button. The data model should look like this:

Figure 35-4 Data model for the group above report

Description of "Figure 35-4 Data model for the group above report"
In the toolbar, click the Paper Layout view button. The Paper Layout view should look like this:

Figure 35-5 Paper Layout view of the group above report

Description of "Figure 35-5 Paper Layout view of the group above report"
Save your report as toc_your_initials.rdf. You have now created the data model and the basic layout for your report.

35.2.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 number of the page on which each country name displays, and place that information into the table you created in Section 35.2.1, "Create a table in the database to hold the TOC data".

35.2.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 runs first.

To create an After Parameter Form trigger:

In the Object Navigator, under your report name (TOC_your_initials), 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 PL/SQL 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, toc_index_code.txt.
Click Compile.
Once the code has compiled, click Close.

35.2.3.2 Create a format trigger

In this section, you will create a format trigger based on the field F_COUNTRY_NAME. This field displays the country name. This format trigger will fetch the page number for each country name, so that the table of contents will enable the user to navigate to various parts of the report based on the name of the country.

To create a format trigger:

In the Object Navigator, under the Paper Layout node for your report, navigate to Main Section > Body > M_G_1_GRPFR > R_G_1, then click F_COUNTRY_NAME.

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, type the following code:

function F_COUNTRY_NAMEFormatTrigger return boolean is
pageNum number;
begin
  -- get current page number
  srw.get_page_num(pageNum);
  -- insert row into table
  insert into toc_example
  values (:country_name, pageNum);  
  return (TRUE);
end;

Note:

You can also copy and paste this code from the provided file, toc_index_code.txt.

Click Compile.
Once the code is compiled, click Close, then close the Property Inspector.
Save your report.

35.2.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 with a formula column that calculates the page range for the data under each country name. This query will fetch the information from the database table you created in Section 35.2.1, "Create a table in the database to hold the TOC data". You will later create a tabular layout to display this information in your table of contents.

To create a second query with a formula column:

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 
    TOPIC, 
    MIN(PAGE) FROMPAGE,
    MAX(PAGE) TOPAGE
FROM TOC_EXAMPLE
GROUP BY TOPIC
```
Note:

You can enter this query in any of the following ways:
- Copy and paste the code from the provided text file called toc_index_code.txt into the SQL Query Statement field.
- Click Query Builder to build the query without entering any code manually.
- Type the code in the SQL Query Statement field.
Click OK. Your data model should now look like this:

Figure 35-6 Data model with second query

Description of "Figure 35-6 Data model with second query"
In the Data Model view, click the Formula Column tool in the tool palette, then click in the group (G_TOPIC) for the second query.
While the formula column is selected, choose Tools > Property Inspector to display its Property Inspector.
Under General Information, set the Name property to TOC_pages, and the Datatype property to Character.
Under Placeholder/Formula, click the PL/SQL Formula property field to display the PL/SQL Editor.

In the PL/SQL Editor, use the template to enter the following code:

function TOC_pagesFormula return Char is
begin
  if :fromPage = :toPage then
  return (:fromPage);
  else
  return (:fromPage ||'-'|| :toPage);
  end if;
end;

Note:

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

Click Compile.
Once the code is compiled, click Close.
Save the report.

35.2.5 Create a report block to display the table of contents

The steps in this section will show you how to create a tabular report block in the Header section of your report. This report block will display the headings in the table of contents (in this example, the country name), and the page range where the information can be found in the report.

To create a tabular 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 about 5 inches (12.5 centimeters) wide and 1.5 inches (4 centimeters) high in the Paper Layout view. When you release the mouse button, the Report Block Wizard displays.
On the Style page, select Tabular, then click Next.
On the Groups page, click G_TOPIC and click Down to move it to the Displayed Groups list, then 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:
- TOPIC
- TOC_pages
On the Labels page, click Next.
On the Template page, click Finish.

Your report block displays in the Paper Layout view:

Figure 35-7 Paper Layout view of the Header Section

Description of "Figure 35-7 Paper Layout view of the Header Section"
Save your report.

35.2.6 Run your simple table of contents report to paper

To run your report to paper:

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 35-8 Table of contents page of the report

Description of "Figure 35-8 Table of contents page of the report"

You can navigate to various pages in your report to view the data for each country. For example, when you navigate to page 1, you will see the e-mail addresses for the customers in Argentina:

Figure 35-9 Results for customers in Argentina

Description of "Figure 35-9 Results for customers in Argentina"

Note:

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

For information on creating a multilevel table of contents, see Chapter 36, "Building a Paper Report with a Multilevel Table of Contents".