Table of Contents Report and the PACE_SYSTEM Table

Table of Contents and the PACE_SYSTEM Table

The availability of the Table of Contents (TOC) for Advanced reports is controlled by the PACE_SYSTEM table. The TOC, driven by sys_value for sys_item 142 in PACE_SYSTEM, includes 0 and 1 as possible values:

• When the value is set to 0 (default), you can only view the TOC if it is coded in the Advanced reporting DLL. Coded in the DLL, the TOC works properly when exporting report results from the viewer.

• When the value is set to 1, you can always view the TOC based on report rules, even if it was not coded in the DLL. If the TOC was not coded in the DLL and report results are exported to a .PDF file, the TOC’s bookmarks are either lost or not properly exported.

A warning message appears if you try to export report results with a TOC not coded in the DLL. The message warns that the TOC will not be exported or cannot be exported properly.

Create a Table of Contents Report

Start Visual Basic before creating a Table of Contents report.

To create a Table of Contents report:

1. Create an ActiveX DLL. On the Project menu, click Add Data Dynamics ActiveReports.
   You see the Active Report1 (ActiveReport) window. Drag the corner of this window so that it fits most of the monitor, and then maximize the design window.

2. On the Project window, highlight ActiveReport(x). Then, on the Properties window, highlight ActiveReport(x) and rename it.

3. On the left menu, click DAO to specify how to pass the data from PACE to the Designer. Click and draw a box about the size of the button within the Detail area.
   The button appears highlighted in the Detail section.

4. Click in the cell next to RecordSetType in the bottom-right corner of the Properties window. The RecordSet Type is currently set to 1=Dynaset. Click in the value cell. Then, click the drop‑down list and change it to 2=Snapshot. The Dynaset setting requires going to the database to retrieve the records as they are accessed. The Snapshot setting retrieves the complete record and does not require accessing the database for each record. The Snapshot process is much faster, especially in bulk reports.

5. Right-click the highlighted button and select Properties.
   You see the DAOData Control Properties window.

6. Click Ellipsis to browse by database name.
   You see the Select Database dialog box. This refers to the database name specified when you created the Advanced Report Profile.

7. Access the target folder, highlight the target Access database, and click Open.
   The DatabaseName is populated.

8. The RecordSource section defines how to obtain data from the Table of Contents table. This table contains a row for each Advanced Report Rule in the profile. Each row contains information such as the name and number of pages in the Advanced Report Rule, and the start page of that Advanced Rule in the Advanced Report Profile. In the RecordSource section, type the following:

   select * from pcre_toc order by start_page asc

9. Click Apply and then click OK.

10. Decrease the size of the Detail section so that the DAO button is hidden. To do this, move your cursor so that it touches the PageFooter section. A double arrow appears. Click and drag the section upward. Leave about a half inch of space in the section.

11. Click the icon to display all possible fields as defined by the RecordSource.

12. Click and drag the name and start_page fields to add them to the Detail section.

13. Make the name field wider. Click the field box and resize it. You may need to move the other fields over to the right.

14. Right-click each field and select Properties to format it.

15. Change the output format property of the start_page field to the Number category with no decimal places. Click  to right align the field.

16. Click Apply and then click OK.

17. Click Save or select Save Project on the File menu to save the project.
    You see the Save File As dialog box.

18. Click Save to save the Designer file.

Preview the Table of Contents Report

To preview the Table of Contents report:

1. On the File menu, click Make (ProjectName).dll to preview the report.
   You see the Make Project window. The file name defaults to the Project Name.

2. Click OK.

3. On the Windows desktop, double-click the Advanced Report Tester button.
   You see the Advanced Report Tester window.

4. In the Report File (MDB or ADV) section, click Ellipsis and select Access database. This is the name of the database defined in the Advanced Report Profile.

5. In the Select Report DLL Name section, type the name of the project you created in Visual Basic. You can also select the name from the drop‑down list, since the Advanced Report Rule now contains the Project name.
   The Select Report Name section populates with all of the designers of that project. In the Select Report Name section, select the Designer.

6. Click View.
   The table of contents appears in the individual ActiveReport window.

Set up the Table of Contents Report

To set up the Table of Contents report:

1. Copy the Project DLL (project) to the Auto Registry engine common files location directory on the PACE Server to finish the cycle.
   The file is registered and moves to the destination directory automatically, and the names of the project and designers are added to the PACE_MASTER.DBO.ADV_REPORT_LIST table.

2. Return to PACE and edit your Advanced Report Rule. Select the project and designer name from the Available Reports drop‑down list. Click OK.
   The report appears in the Advanced Reporting folder in Reports Explorer when it is complete.

3. Expand the Advanced Reporting folder and click Compose. Double-click Advanced Reporting.
   You see the Advanced Report Rule window.

4. Complete the following options on the Advanced Report Rules window:
   In the Report Rule name section, type the Rule name.
   In the Available Reports section, select a Project and the Table of Contents Designer Name.
   Leave the Profiles section empty. This allows the report to run as quickly as possible.
   Leave the Table Name column empty.
   In the Options section, select opt_run_last. This insures that the Table of Contents is imaged last and presented in the order defined in the Advanced Report Profile.

5. Click OK.

6. Click the Reports tab. In the Most Recent folder, highlight an existing Advanced Profile that contains multiple PACE Profiles and click Edit.
   You see the Advanced Report Profile - Step 1 of 3 dialog box.
   Complete the following options on this dialog box:
   In the Selected Rules section, right-click and select Add. Select the Table of Contents Advanced Rule. Then, highlight this Advanced Rule in the Selected Reports window and drag it up to the first spot in the Selected Rules list.
   From the Entity Name list, highlight an Entity and click Add.

7. Click Finish.
   When the report is returned, an extra line is added to the table of contents. You can modify this in Visual Basic.

8. Return to Visual Basic, and make sure you are in the Table of Contents Designer.

9. Expand the Detail section, right-click  and select Properties.
   The DAODataControl Properties dialog box appears.

10. In the RecordSource section, just after pcre_toc, add the following code: where name <>‘Advanced Rule Name’.
    The complete code should now be:
    select * from pcre_toc where name <>‘Advanced Rule Name’ order by start_page asc

11. Save your changes.

12. Decrease the size of the Detail section so that the DAO button is hidden. Leave about a half inch of space in the section.

13. Click Save to save your changes.

14. On the File menu, select Make ProjectName to save the DLL.

15. Copy the Project DLL (project) to the Auto Registry engine common files location directory on the PACE Server to finish the cycle.
    The file is registered and moves to the destination directory automatically, and the names of the project and designers are added to the PACE_MASTER.DBO.ADV_REPORT_LIST table.

16. Return to PACE and resubmit the Advanced Report Profile.