Dynamic Crystal Reports Table of Contents / Index

So I spent the last couple days searching the net for how to build a table of contents into a Crystal Report that would let me know what pages my group headers appeared on. There were only a couple subtle references out on the net but mostly people posing the question with either no answer or an answer directing them to a kb article by Crystal that has since been removed with Crystal being now owned by SAP.

I was finally able to figure out how to build an index page. Basically a table of contents that appears at the end of the report instead of the beginning.

I made two formulas:

The first one is called IndexBuilder. In it I placed the category header. Each time the header changed the function would get called and populate the array variables with the category and current page number:

Code:

---

//this function builds the indexes based on categories in the report
WhilePrintingRecords;
StringVar Array catArry;
NumberVar Array pageArry;
IF NOT (GroupName({Table.Field}) IN catArry) THEN (
Redim Preserve pageArry[UBound(pageArry) + 1];
pageArry[UBound(pageArry)] := PageNumber;
Redim Preserve catArry[UBound(catArry) + 1];
catArry[UBound(catArry)] := GroupName({CategorySummary.CatalogTitle});
);
""; //the formula has to print something even if it's an empty string to run at all

---

The second formula IndexPrinter takes the array that was build while the report was being generated and prints the line items. It is placed in a report footer with a new page call in the section expert making it a seperate page.

Code:

---

//this function prints the arrays built throughout the creation of the report, functionally a TOC/Index of the report catagories
WhilePrintingRecords;
NumberVar i;
NumberVar j;
StringVar Array catArry;
NumberVar Array pageArry;
StringVar Array Output;
Redim Output[UBound(catArry)];
FOR i := 1 to UBound(catArry) do (
  Output[i] := "Page " + Left(CStr(ToText(pageArry[i],0))+":    ",5) + catArry[i];
);
Join(Output,Chr(13))

---

I would still prefer to see this exact same thing appear at the beginning of the report as a Table of Contents so if anyone has any brilliant tricks please chime in.

Scuz

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.

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.

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

NOTE:
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')

NOTE:
If changes are made to the report that affect the page numbering, refresh the report twice to update the TableOfContents table.

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.

Plenty here to look into, thanks for posting.

Hi There
I was also Trying to print the Index page
I used tecknique mentioned in "Advanced Reporting Techniques using Arrays.pdf"

I'm not able to pass the array from main report to sub report
please read the following post for details of my problem

http://www.vbcity.com/forums/topic.asp?tid=163538

Thanks in advance
amit saraf

Hi,
I have a Problem with this Formula:

Quote:

---

Originally Posted by jggtz

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

NOTE:
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')

---

The Problem is that the Formula 'LinkMaker' writes only the first Value to the Database.

The output of the formula looks similar to this:
INSERT INTO TableOfContents VALUES ('Bikes',1,'2003-04-04 12:32:01')

But I have more Values that should be added to the TableOfContent Database.

Can anyone help me?

Post your formula code and say in wich section you placed it

Hi,
This is my 'LinkMaker' Formula:

WhilePrintingRecords;
StringVar link;
If Not InRepeatedGroupHeader Then
link := link & "INSERT INTO TOC1 VALUES ('" &
GroupName ({Product.Product Name}) & "'," &
ToText(PageNumber,0) & ",'" &
ToText({@DateTime}, "dd-MM-yyyy HH:mm:ss") &
"')" & chr(10);
link

And I post this Formula in the 'Main Report Header'.

I post the 'LinkPasser' Formula in the 'Main Report Footer a' with the code:
WhilePrintingRecords;
StringVar link;
link

And the 'DateTime' Formula I also Post in the Main Report Header.
Code:
CurrentDateTime

Hi,
I solve the problem.
Thank you.

Hi,
This is great solution for Dynamic Crystal Reports Table of Contents, but has one disadvantage:
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.

Someone know how to automatically refresh this report ??? most of users which print the report with TOC simply don't know that the table of contents they see is not actual until they refresh the report.

I followed all your steps, but this below Insert is not happening nor it is throwing any error. I am on Crystal Reports 2008. Does it have anything to do with crystal report user id permission issue on CMC? The DB user id I am using have write permissions on db, so I am not sure why it is not inserting any rows.
Any pointers for the same would be great.

Thanks

[QUOTE=jggtz;3373999]
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

NOTE:
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')

NOTE:
If changes are made to the report that affect the page numbering, refresh the report twice to update the TableOfContents table.