34
Building a Paper Report with a Simple Table of Contents and Index

Figure 34-1 Simple table of contents for a large report

Text description of the illustration fotoc_toc.gif

Figure 34-2 Simple index for a large report

Text description of the illustration fotoc_idxa.gif

When you have large paper report, it's sometimes easier for your users to navigate through the report if you include a table of contents. Using Reports Builder, you can generate a table of contents that displays at the beginning of your paper report.

Normally, Reports Builder formats the report starting with the Header section, then the Main section, followed by the Trailer section. Now, Reports Builder can format any section first to create information that is only known at the time of formatting, such as page numbers, then use that information in the formatting of a previous section.

In this example, you will use the built-in procedure called SRW.SET_FORMAT_ORDER to generate the Main section of the report first, then 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 use the After Parameter Form trigger and a format trigger to place the page numbers temporarily in a table you create in the database. You will then create a simple tabular report that displays at the beginning of your report, that lists the category and its page number. Finally, you will create an index that simply lists the record and the page number in the Trailer section of the report.

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

Example Scenario

Suppose you want to generate a list of all the e-mail addresses for your customer database. Your customer database contains thousands of records. First, you want to categorize the customers (in this case, by country), then generate a table of contents so you can easily find the e-mail address for the desired customer.

Table 34-1 Features demonstrated in this example

Feature	Location
Use SQL*Plus to create a table in your database to hold the table of contents (TOC) information.	Section 34.2.1, "Create a table in the database to hold the information for the TOC"
Use the Report Wizard to create a simple group above report.	Section 34.2.2, "Create a group above report"
Use the Data Model view to create a second query.	Section 34.2.4, "Create a second query in the data model"
Use the Report Block Wizard to create a tabular report to display the country name and page number.	Section 34.2.5, "Create a report block to display the table of contents"
Create a format trigger to generate a page number for every customer record.	Section 34.3.2, "Create a format trigger"
Use the Data Model view to create another query that retrieves the necessary information for the index.	Section 34.3.3, "Add a query to the data model"
Use the Report Block Wizard to display the index.	Section 34.3.4, "Create a report block to display the index in the Trailer section"

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

34.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 34-2 Files necessary for building a report with a simple table of contents and an index

   File	Description
   Examples\result\formatorder.rdf	The final RDF version of the paper report.
   Examples\result\formatorder_index.rdf	The final RDF version of the paper report with an index.
   Examples\scripts\formatorder_code.txt	The various SQL and PL/SQL code 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

34.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 by using the user ID and password "sh/sh", then enter the name of the database.

34.2 Create a simple table of contents for a large report

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 34-3 Simple table of contents

Text description of the illustration fotoc_toc.gif

34.2.1 Create a table in the database to hold the information for the TOC

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:

1. Start SQL*Plus.
   • In 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 portion of the database (e.g., use the sh/sh@database name connect string).

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

   create table toc_example (topic varchar2(100), page number);
   
   

4. Press Enter.

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

5. Exit SQL*Plus.

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

1. In Reports Builder, choose File > New > Report, then choose to create the report manually.

2. In the Data Model view that displays, right-click on the canvas, then choose Report Wizard from the pop-up menu.

3. On the Report Type page, select Create Paper Layout only, then click Next.

4. On the Style page, select Group Above, then click Next.

5. On the Data Source page, click SQL Query, then click Next.

6. On the Data page, enter the following SELECT statement in the SQL Query Statement field:

   SELECT ALL COUNTRIES.COUNTRY_NAME,
              CUSTOMERS.CUST_LAST_NAME,
              CUSTOMERS.CUST_FIRST_NAME,
              CUSTOMERS.CUST_e-mail
   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 formatorder_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 Next. Ensure that you connect to a database that has the appropriate schema for this example. Section 34.1, "Prerequisites for this example" describes the sample schema requirements for this example.

8. On the Groups page, click COUNTRY_NAME, then click > to move the field to the Group Fields list.

9. Click Next.

10. On the Fields page, click >> to move all fields to the Displayed Fields list, then click Next.

11. On the Totals page, click Next.

12. On the Labels page, click Next.

13. On the Template page, click Finish.

14. In the toolbar, click the Data Model view button. The data model should look like this:

Figure 34-4 Data model for the group above report

Text description of the illustration fotoc_dm.gif

15. In the toolbar, click the Paper Layout view button. The Paper Layout view should look like this:

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

Text description of the illustration fotoc_lay.gif

16. Save your report as formatorder_<your initials>.rdf. You have now created the data model and the basic layout for your report.

34.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 34.2.1, "Create a table in the database to hold the information for the TOC".

Normally, Reports Builder formats a report with the Header section first, followed by the Main and Trailer sections. By changing the order so that the Main section is formatted first, you can generate the page numbers for the records, place this data into a database table, then generate the table of contents based on that data. In later steps, you will create the table of contents in the Header section of the report.

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

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

1. In the Object Navigator, under your report name (FORMATORDER_<your initials>), expand the Report Triggers node.

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

3. 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, formatorder_code.txt.

4. Click Compile.

5. Once the code has compiled, click Close.

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

1. 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 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, 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, formatorder_code.txt.

5. Click Compile.

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

7. Save your report.

34.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 34.2.1, "Create a table in the database to hold the information for the TOC". 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:

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

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

3. Click OK. Your data model should now look like this:

Figure 34-6 Data model with second query

Text description of the illustration fotoc_dm2.gif

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

5. While the formula column is selected, press F4 to display its Property Inspector.

6. Under General Information, set the Name property to TOC_pages, and the Datatype property to Character.

7. Under Placeholder/Formula, click the PL/SQL Formula property field to display the PL/SQL Editor.

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

   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 formatorder_code.txt.

9. Click Compile.

10. Once the code is compiled, click Close.

11. Save the report.

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

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 5 inches long and 1.5" inches long in the Paper Layout view. When you release the mouse button, the Report Block Wizard displays.

3. On the Style page, select Tabular, then click Next.

4. On the Groups page, click G_TOPIC and click Down to move it to the Displayed Groups list, then click Next.

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

6. On the Labels page, click Next.

7. On the Template page, click Finish.

Your report block displays in the Paper Layout view:

Figure 34-7 Paper Layout view of the Header Section

Text description of the illustration fotoc_hdlay.gif

8. Save your report.

34.2.6 Run your simple table of contents report to paper

Click the Run to Paper button in the toolbar. 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 34-8 Table of contents page of the report

Text description of the illustration fotoc_toc.gif

When you navigate to, for example, page 1, you'll see the e-mail addresses for the customers in Argentina:

Figure 34-9 Results for customers in Argentina

Text description of the illustration fotoc_arg.gif

You can navigate to various pages in your report to view the data for each country.

Note: At this point, you can compare your report against the example file we've provided, formatorder.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, seeChapter 35, "Building a Paper Report with a Multilevel Table of Contents".

34.3 Create an index for your report

In this section, you will create an index that displays at the end of your report. This index will simply display the customer's name and the page number on which the name appears. This indexing technique is useful when users want to find a specific customer's name, but are not sure what category (e.g., country) to reference.

Figure 34-10 Sample index for the paper report

Text description of the illustration fotoc_idx.gif

34.3.1 Create a table in the database to hold the information for the index

The steps in this section will show you how to create a table in the database that will hold the page numbers for the items you want to list in the index. 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. Follow the steps in Section 34.2.1, "Create a table in the database to hold the information for the TOC"to create a new table in the database, using the following code:

   create table index_example (term varchar2(100), page number);
   

2. Press Enter.

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

3. Exit SQL*Plus.

34.3.2 Create a format trigger

The steps in this section will show you how to create a format trigger based on the customer's last name. You will use the SRW.GET_PAGE_NUM built-in function to find the page number for the current customer, and insert that page number into the table you created in the previous section.

To create a format trigger:

1. In Reports Builder, open the report, formatorder_<your initials>.rdf.

2. Click the Paper Layout button in the toolbar to display the Paper Layout view.

3. In the Paper Layout view, right-click the field F_CUST_LAST_NAME, then choose Property Inspector from the pop-up menu.

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

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

   function F_CUST_LAST_NAMEFormatTrigger return boolean is
     PageNum number;
   begin
     -- get pagenumber
     srw.get_page_num(pageNum);
     -- insert into table
     insert into index_example
     values (:Cust_last_name||', '||:Cust_first_name, PageNum);
     return (TRUE);
   end;
   

   Note: You can enter this code by copying and pasting it from the provided text file called formatorder_code.txt.

6. Click Compile.

7. If there are compilation errors, match your code to the code we've provided (either in the example RDF file or in this chapter), then compile it again.

8. Once there are no compilation errors, click Close.

9. Save your report as formatorder_index_<your initials>.rdf.

34.3.3 Add a query to the data model

The steps in this section will show you how to add a query to your data model that will retrieve the individual customer names and the page numbers for your index.

To add the 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 SUBSTR(TERM,1,1) INITIAL_LETTER, TERM, PAGE FROM INDEX_EXAMPLE 
   ORDER BY TERM
   
   

   Note: You can enter this query in any of the following ways: Copy and paste the code from the provided text file called formatorder_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.

3. Click OK.

4. In the new query that displays in the Data Model view, click and drag the INITIAL_LETTER column above the rest of the query, so that the data model now looks like this:

Figure 34-11 Data model view of the index

Text description of the illustration fotoc_dmi.gif

5. Save your report.

34.3.4 Create a report block to display the index in the Trailer section

The steps in this section will show you how to display the index information in the Trailer section of your report.

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

2. Choose Insert > Report Block.

3. On the Style page, select the Group Above radio button, then click Next.

4. On the Groups page, click G_2 (the name of the new group that contains the INITIAL_LETTER column), then click Down to specify the print direction.

5. In the Available Groups list, click INITIAL_LETTER, then click Down.

6. Click Next.

7. On the Fields page, click INITIAL_LETTER, then click > to move it to the Displayed Fields list.

8. Move TERM and PAGE to the Displayed Fields list.

9. Click Next, then Next again, then click Finish.

10. Save your report.

34.3.5 Run your report to paper

Click the Run to Paper button in the toolbar. The index displays on the last pages of the report. We chose page 2402, which (in our example) 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 34-12 Index page of the report

Text description of the illustration fotoc_idx.gif

Note: The data on page 2402 in your report may not appear the same as ours, depending on the amount and type of data you have in the schema.

34.4 Summary

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

• create a table of contents (TOC) and index for a large paper report.

• use the SRW.SET_FORMAT_ORDER built-in procedure to change the order in which the sections of a report are executed.

• use the SRW.GET_PAGE_NUM built-in package to retrieve the page number for a specific record.

• create a new table in your database to hold the necessary page numbers.

• create a simple group above report using the Report Wizard.

• manually a simple data model to retrieve the information for your report.

• create a format trigger to generate the necessary page numbers for an index.

• use the Report Block Wizard to create a group report that displays the index in the trailer of a report.

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.