35.3 Create an Index

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 (for example, country) to reference.

Figure 35-10 Sample index for the paper report

Description of Figure 35-10 follows
Description of "Figure 35-10 Sample index for the paper report"

35.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 35.2.1, "Create a table in the database to hold the TOC data" 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.

35.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, toc_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, double-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 toc_index_code.txt.
  6. Click Compile.

  7. If there are compilation errors, match your code to the code we have 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 toc_index_your_initials.rdf.

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

  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 35-11 Data model view of the index

    Description of Figure 35-11 follows
    Description of "Figure 35-11 Data model view of the index"

  5. Save your report.

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

To create a report block in the Trailer section:

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

35.3.5 Run your report to paper

Click the Run Paper Layout 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:

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.

Figure 35-12 Index page of the report

Description of Figure 35-12 follows
Description of "Figure 35-12 Index page of the report"

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.