SAP Knowledge Base Article - Public

1215100 - Is it possible to create a table of contents in Crystal Reports?

Symptom

Customer assurance was able to get this proposed solution to work with Microsoft SQL Server databases only. This proposed solution has not been verified to work with any other databases. 
 
In Crystal Reports (CR) 9 and later, is it possible to create a table of contents at the beginning of a report? A table of contents would be in the Report Header, showing group names next to the page numbers the groups start on.
 
WARNING
The following resolution is intended for advanced users of Crystal Reports. This solution is offered as a tip to our customers but is not supported by Business Objects Customer Assurance. Ensure you read all notes and warnings throughout this article.

 

Resolution

WARNING
Crystal Reports is not recommended as a tool to write to, update or delete from a database. Database clients or applications designed specifically to edit databases should be used instead. However, now that CR has full SQL functionality it is possible to write to, update and delete from databases. This article is provided as a tip.
 
Before attempting to implement the steps in this article, consult your Database Administrator (DBA). Your DBA needs to grant specific rights to your database. Your DBA may also be able to guide you in creating an appropriate SQL statement for your database. The syntax provided in this article is specific to Microsoft SQL Server.
 
You can make a table of contents using a subreport with a command object. Using a command object it is possible to write to a database from CR. The following example will use a subreport to write group names and page numbers to a new table in your database.
 
CAUTION
The following resolution involves editing the registry. Using the Registry Editor incorrectly can cause serious problems that may require you to reinstall the Microsoft Windows operating system. Use the Registry Editor at your own risk. It is strongly recommended that you make a backup copy of the registry files before you edit the registry. For information on how to edit the registry key, view the 'Changing Keys And Values' online Help topic in the Registry Editor (Regedit.exe).
 
 

Create a Table of Contents

1. Create a new table in your database called TableOfContents. Create three fields in this table:

" Grouper (String data type)(Ensure this field is at least as large as your longest group field value)

" Page (Number data type)

" DateTime (DateTime data type)

2. Assign rights to write to, delete, and update this table to any users of this report. The TableOfContents table will be updated as the report changes.

CAUTION
Use INSERT INTO, DELETE or UPDATE statements at your own risk. Once a command object containing an INSERT INTO, DELETE or UPDATE statement has run, the database has changed and the changes may not be reversible.

3. On a grouped report (for this example the report is grouped on the ProductName field), in the 'Insert' menu, click 'Subreport'. Name this subreport 'Table Of Contents'. Connect this subreport to the new TableOfContents table and insert the TableOfContents.Grouper and TableOfContents.Page fields in the Details section. Sort this subreport by the TableOfContents.Page field.

4. Insert the 'Table of Contents' subreport in the Report Header section.

5. Right-click the gray area to the left of the Report Footer, and then click 'Insert Section Below'. There will now be 'Report Footer a' (RFa) and 'Report Footer b' (RFb).

6. In the Main report, on the 'Insert' menu, click 'Subreport'. Name this subreport 'UpdateTOC'. In the 'Database Expert' dialog box, browse to your Table of Contents data source, then double-click 'Add Command'. The 'Add Command to Report' dialog box will appear.

7. Click the 'Create' button to create a parameter. Name the parameter 'LinkReceiver' and click 'String' from the 'Value Type' drop-down box. Create another parameter named 'DateTime' with a 'DateTime' value type.

8. In the 'Add Command to Report' dialog box enter a SQL Query similar to the following:

{?LinkReceiver}

DELETE FROM "TableOfContents"

WHERE "TableOfContents"."DateTime" <> {?DateTime};

SELECT * FROM "TableOfContents"

 

This command object will add new GroupName and PageNumber data to the TableOfContents table and delete any existing data based on the ?DateTime parameter.

 

IMPORTANT
This SQL Query (and any other SQL syntax in this article) is written for a Microsoft SQL Server database. Consult the database manual or Database Administrator for the correct syntax for your data source. The command object syntax must be in the databases accepted format or it will generate errors in Crystal Reports.
 
It is strongly recommended that you populate the TableOfContents with some data. When that table is empy, you may experience syntax errors when running the report. When the TableOfContents table does not contains any details then Crystal Reports cannot give a valid answer when comparing the DateTime to CurrentDateTime.
 
For assistance with creating the SQL query for your command object, refer to your DBA. Business Objects Customer Technical Support does not provide support for writing SQL queries, because the syntax in use is database specific.

9. Insert the Grouper and the Page fields in the subreport Details section. Suppress all of the subreport sections.

10. Insert the 'UpdateTOC' subreport in the Report Footer B (RFb) section. (This is to ensure that the formula used to link the subreport is processed before the subreport is processed.)

11. Right-click the 'UpdateTOC' subreport, and then click 'Format Subreport'. Click the 'Border' tab, and then click 'None' from the 'Left', 'Right', 'Top' and 'Bottom' drop-down boxes. Click 'OK' to close the 'Format Editor' dialog box.

12. In the main report, create a formula named 'DateTime' with the following syntax:

CurrentDateTime

13. Insert this formula in the main report's Report Header section.

14. Create another formula named 'LinkMaker' with the following syntax and place it in the Group Header section of the main report:

WhilePrintingRecords;

StringVar link;

If Not InRepeatedGroupHeader Then

link := link &

"INSERT INTO TableOfContents VALUES ('" &

GroupName ({Product.Product Name}) & "'," &

ToText(PageNumber,0) & ",'" &

ToText({@DateTime}, "yyyy-MM-dd HH:mm:ss") &

"')" & chr(10);

link
 
The output of this formula will look similar to this:

INSERT INTO TableOfContents VALUES

('Bikes',1,'2003-04-04 12:32:01')

INSERT INTO TableOfContents VALUES

('Locks',2,'2003-04-04 12:32:01')

15. Create another formula named 'LinkPasser' with the following syntax and place it in the Report Footer A (RFa) Section of the main report:

WhilePrintingRecords;

StringVar link;

link
 
16. In the main report, in the 'Edit' menu, click 'Subreport Links'. In the 'Subreport Links' dialog box click the 'UpdateTOC' subreport from the 'For subreport' drop-down box.

17. Click the '@DateTime' Formula field from the 'Available Fields' box and move it to the 'Field(s) to link to' box. In the 'Subreport parameter field to use' drop-down box, click the '?DateTime' parameter.

18. Repeat step 15, connecting the '@LinkPasser' Formula field to the '?LinkReceiver' parameter. Click 'OK' to close the 'Subreport Links' dialog box.

19. In the 'Field Explorer' of the main report, click 'Special Field'. Insert the 'PageNofM' field in the Page Footer.

20. Preview the report to write the Table of Contents data to the database.

21. Refresh the report again and the Table of Contents will display the group and page number values based on your report.

NOTE

If changes are made to the report that affect the page numbering, refresh the report twice to update the TableOfContents table. If the number of pages used by the Table of Contents changes, the report must be refreshed a third time to update the Table of Contents subreport.

 

Keywords

TABLE OF CONTENTS C2019321 ADD COMMAND PAGE NUMBERS CATALOGUE DIRECTORY Crystal Reports Table of Contents Database write-back , c2011950 , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

Product

SAP Crystal Reports 9.0