36.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 that retrieves the information stored in the table you created in Section 36.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.

To create a 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 * FROM TOC_MULTILEVEL
   

   Note:

   You can enter this query in any of the following ways:

   • Copy and paste the code from the provided text file called multilevel_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.

3. Click OK.

4. In the new query, click and drag the MAIN_TOPIC column to a separate group above the rest of the query.

5. Click the Summary Column tool in the tool palette, then click in the new group object to create a summary column.

6. Double-click the new summary column object to display the Property Inspector, and set the following properties:

   • Under General Information, set the Name property to Subcategory_Page

   • Under Summary, set the Function property to Minimum and set the Source property to PAGE

   Your data model should now look like this:

   Figure 36-3 Data model for the multilevel TOC report

   
   Description of ''Figure 36-3 Data model for the multilevel TOC report''
   
   

7. Save your report.