Oracle Reports Building Reports 10g (9.0.4) Part Number B10602-01 |
|
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 the previous chapter, 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. For information on creating a simple table of contents, refer to Chapter 34, "Building a Paper Report with a Simple Table of Contents and Index".
As in the previous chapter, 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.
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 packages in Oracle Reports, refer to the Reports Builder Online Help.
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 Reports Builder Online Help or, for specific steps, refer to any other chapter in this manual.
Suppose 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 (e.g., Boys, Girls), then lists each item under each category.
.Feature | Location |
---|---|
Use SQL*Plus to create a table in the database to hold the first and second tier categories and page numbers. |
Section 35.2, "Create a table in the database to hold the TOC data" |
Use the PL/SQL Editor to create two triggers that change the order in which the report sections are executed and fetch the page numbers for each category, sub-category, and record. |
Section 35.3, "Create an After Parameter Form trigger and format trigger" |
Use the Report Block Wizard to create a simple layout in the Header section of the report layout. |
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 with 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 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.
create table toc_multilevel (main_topic varchar2(100), sub_topic varchar2(100), page number);
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 35.2, "Create a table in the database to hold the TOC data".
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.
multilevel_source.rdf
in the source
directory.
function AfterPForm return boolean is begin srw.set_format_order(srw.main_section, srw.header_section, srw.trailer_section); return (TRUE); end;
multilevel_<your initials>.rdf
.
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.
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;
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 35.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.
SELECT * FROM TOC_MULTILEVEL
Subcategory_Page
Your data model should now look like this:
To display the TOC in your report, you will need to create a layout in the Header section of the report. In doing so, the multilevel TOC displays at the beginning of your report. You will also add a field to your layout to display the page number for each category.
Your report block displays in the Paper Layout view:
The steps in this section will show you how to run your report and make a few modifications in the Paper Design view.
The table of contents displays on the first page of the report, and looks like the following:
When you move to, for example, page 30
(which is between the page range of 22 and 38), you'll see the SHIRTS sub-category under the BOYS product department:
You can navigate to various pages in your report to view the data for each department and sub-category.
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. |
|