Create a table of contents for your reports

A simple TOC can help users navigate your reports

In this article, we'll show you how to create a table of contents (TOC) based on the grouping in a Crystal report. For example, if you have a report grouped on country, the TOC would list all the countries in the report, along with the page number that each country begins on. This could be a useful navigation aid for people working with printed copies of the report. Figure 1 shows how it might look.

Figure 1: A table of contents, showing group names and page numbers

The technique that we will describe has the advantage of being very simple - only three short formulae are needed. But it also has two important limitations. First, the TOC must always appear at the end of the report, rather than at the start where most people would expect to see it. That's because group names and page numbers are not available until all the groups have been printed.

Secondly, the technique is only suitable for relatively short TOCs - typically those containing up to 20 to 40 entries. If the total length of the entries exceeds 254 characters (the maximum length of a string in CR), the surplus characters will not appear.

If those limitations are too onerous for you, there is an alternative approach you could take, although this has drawbacks of its own. More about this later in the article.

First build the TOC ...

To create the TOC, you first need a formula which collects the group names and their corresponding page numbers and stores them in a pair of global string variables. The formula, which is placed in the group header band, looks something like this:

global stringvar strTOC1; 
global stringvar strTOC2; 
local stringvar strTemp;

if not InRepeatedGroupHeader then
  ( strTemp := {customer.country} + chr(10) ;
  if len(strTemp) + len(strToc1) <= 254 then 
    ( strToc1 := StrToc1 + strTemp; 
    strToc2 := StrToc2 + 
      totext(PageNumber,0) + chr(10) ))

The formula declares two global strings variables. The first of these, strToc1, holds the group names (the country names in this example) separated by line-feed characters. The line-feeds ensure that each name will appear on a separate line within the table of contents. Before adding each new group name to this string, we check to see if it has reached the limit of 254 characters. If it has, no further names are added.

The second string, strToc2, holds the page numbers, converted to text. These too are separated by line-feed characters.

You might be wondering why we didn't choose to build a single string containing both the group names and the page numbers. This is a matter of alignment. Given that the group names vary in length, there would have been no way of getting the names and the numbers into two neatly aligned columns if they had been in the same string. Creating two separate strings solves that problem.

After you have added the above formula to the group header section, be sure to suppress it to avoid spurious output appearing in the group header.

... Then display it

To display the TOC, we need two further formulae, both of which are extremely simple. The first simply returns the contents of the group name string, like so:

whileprintingrecords;
  global stringvar strTOC1; 
  strToc1

The other formula, which is almost identical to the first, returns the contents of the page number string:

whileprintingrecords;
  global stringvar strTOC2; 
  strToc2

Place these two formulae in the report footer section. Position them side by side, and line them up along their top edges. Be sure to enable the Can Grow option for both formulae (do this from the Common page of the format editor). To improve the alignment of the page numbers, right-justify the second of the two formulae. Finally, add a suitable text object (such as 'Table of Contents') to label the TOC.

And that's all there is to it. When you preview the report, you should see the finished table of contents, complete with group names and page numbers, all neatly lined up on the last page of the report.

An alternative approach

Earlier, we mentioned two drawbacks that might rule this technique out for your reports. There is an alternative method of creating a table of contents that doesn't have these disadvantages, although it is much more complicated than the technique we have described. It involves maintaining a special table in the database to hold the TOC, and updating this by means of SQL code within the report.

However, this method should be approached with caution. In general, it is not a good idea to write data to the database from a report, because a mistake could lead to invalid data and corrupted tables. You would also need to have the necessary permissions to create and update tables, permissions which database administrators might be reluctant to grant. Also, the ability to write SQL code to update a database was only added in Crystal Reports 9.0, so the technique would not work with earlier versions.

If, despite these difficulties, you would like to know more about this table-based approach, you can find full details at http://support.businessobjects.com/library/kbase/articles/c2011950.asp.

Summing up

If you can live with the drawbacks mentioned earlier, you should find the technique which we have described in this article to be a simple and effective way of creating a table of contents for your reports. We have often found it useful in our own reporting projects, and we hope you will find a use for it in yours.

Mike Lewis Consultants Ltd. January 2004.