Oracle Reports Building Reports 10g (9.0.4) Part Number B10602-01 |
|
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.
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".
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.
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. |
|
Use the Data Model view to create a second query. |
|
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. |
|
Use the Data Model view to create another query that retrieves the necessary information for the index. |
|
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" |
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.
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.
http://otn.oracle.com/products/reports/
).
formatorder.zip
into a temporary directory on your machine (e.g., d:\temp
).
d:\orawin90\examples
).
This zip file contains the following files:
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.
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:
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.
sh/sh@
database name connect string).
create table toc_example (topic varchar2(100), page number);
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.
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)
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.
Note:
formatorder_<your initials>.rdf
. You have now created the data model and the basic layout for your report.
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.
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.
function AfterPForm return boolean is begin
srw.set_format_order(srw.main_section, srw.header_section, srw.trailer_section); return (TRUE);
end;
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.
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;
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.
SELECT TOPIC, MIN(PAGE) FROMPAGE, MAX(PAGE) TOPAGE FROM TOC_EXAMPLE GROUP BY TOPIC
TOC_pages
, and the Datatype property to Character.
begin if :fromPage = :toPage then return (:fromPage); else return (:fromPage ||'-'|| :toPage); end if; end;
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.
Your report block displays in the Paper Layout view:
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:
When you navigate to, for example, page 1, you'll see the e-mail addresses for the customers in Argentina:
You can navigate to various pages in your report to view the data for each country.
For information on creating a multilevel table of contents, seeChapter 35, "Building a Paper Report with a Multilevel Table of Contents".
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.
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.
create table index_example (term varchar2(100), page number);
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.
formatorder_<your initials>.rdf
.
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;
formatorder_index_<your initials>.rdf
.
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.
SELECT SUBSTR(TERM,1,1) INITIAL_LETTER, TERM, PAGE FROM INDEX_EXAMPLE ORDER BY TERM
The steps in this section will show you how to display the index information in the Trailer section of your report.
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:
Congratulations! You have successfully created a report with a table of contents. You now know how to:
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:
http://otn.oracle.com/products/reports/
), click Documentation and navigate to the Reports Builder Online Help for the most recent, hosted online help.
|
Copyright © 2002, 2003 Oracle Corporation. All Rights Reserved. |
|