How to Create an Index Report (and some info on Report Part Linking)

If you’ve ever wanted to create an index report in Crystal, here’s a blog that will walk you through the steps on creating one. If you’ve ever wanted to create a table of contents in a Crystal Report, perhaps you can use this technique as a substitute.

A lot of times there are forum posts asking how to create a Table of Contents report. This is not a lesson on building a table of contents but may be the next best thing and in a web viewer, linking can make it feel like a table of contents. The main difference between the two being that the table of contents is usually at the beginning while the index is at the end of the document. There is a way to create a table of contents using stored procedures and write-backs to the database but that will not be covered here.

In this ‘how to lesson’ we can walk through a sample report that you can get by clicking here. When you open up the file in the Crystal Reports (CR) designer you will notice that there is a “Go to Index” link on each page that will be active when you publish the report to either BusinessObjects Enterprise or to Crystal Reports Server and open it in the DHTML viewer. This link targets the “Index” text object that is in the Report Footer. If you right click on the “Go to Index” link and choose Format Text and then the Hyperlink tab, you’ll see that it is a Report Part Link. Report Part Linking can be a handy feature and is documented further in your online help in the Crystal Reports Designer. I’ll show you how to create some Report Links further on in the lesson.

If you go to the Design mode of CR there is a formula named “IndexBuilder” which you can copy to your report and place it in the section of your report where you want the index to be based on. In this sample, the index is based on collecting the group names and starting pages at the customer level. The index’s highest level is on country, but the information we require is at the lowest level which is the group on customer name. When you copy the IndexBuilder to your report you will want to change the stringvar for “group1” and stringvar for “group2” to the matching groups on your report. You do not need to change anything else as long as you’ve got two levels of grouping on your report.

whileprintingrecords;
stringvar group1:= {@fGroup1};
stringvar group2:= {@fGroup2};

stringvar groupsSRT;
if onfirstrecord then groupsSRT:= ‘||’;

groupsSRT:= groupsSRT + group1 + group2 + ‘|’ + totext(pagenumber,0) + ‘||’;

The above formula will keep track of your group hierarchy plus the lower level groups and their corresponding starting page numbers.

You will also want to copy the “IndexGroups” formula to your report and also change the groups in that formula to match the groups on your report.

Now on your report Insert a Cross-tab that you will place in your Report Footer. The two Row values will be the two groups that you are using in the above formulae. The Summary value will be the Maximum of the IndexGroups formula. Once you preview the report you can right click on the Maximum Summary and then choose Format Field and then click on the Display String formula icon. Insert the following syntax which will display the Page Number for each of your lower level group values:

whileprintingrecords;
stringvar groupsSRT;
stringvar tH:= currentfieldvalue;

numbervar i:= instr(groupsSRT, tH);
stringvar tG:= groupsSRT[i to i+length(tH)+6];
i:= instr(tG, ‘||’);
tG:= tG[1 to i-1];
tG:= tG[instr(tG,’|’)+1 to length(tG)];

You can also right click on either of the country row or the customer name row and then choose Format Field and select the Hyperlink tab. Note that there are Report Part Links that will take the end user to the appropriate group while in the DHTML viewer. Sorry, but these links don’t work until you publish the report to your server environment.

Here’s a brief outline on how you can put a Report Part Hyperlink into a cross-tab:

1) first you need to publish the report to your server environment (BusinessObjects Enterprise or Crystal Server)

2) right click on your Group 1 Name and choose Copy

3) right click on the Row 1 Name in the cross-tab and then choose Format Field and then Hyperlink

4) you should be able to Paste the Report Part Link into the dialogue

5) ensure that the Data Context is similar to below

“/”+GridRowColumnValue(“your group 1 field”)

This report part link will allow the end user to link from the index to any group on the report.

You can also customize your cross-tab by right clicking on the cross-tab and choosing Cross-tab Expert and then Customize Style. If you wish you can uncheck Show Cell Margins and also go to Format Lines and uncheck Show Grid Lines. This will quickly make the cross-tab smaller and less busy looking.

One final step for the sake of formatting your report is to allow the end user to open up the report and go to the index with one click.

1) Insert a Text Object that reads “Go to Index” and place this field on the Page Header

2) Insert a Text Object that reads “Index” and place this field on the Report Footer right above your index cross-tab

3) right click on the text object that reads “Index” and choose Copy

4) right click on the “Go to Index” text object and choose Format Text and click on the Hyperlink tab

5) you should now be able to Paste the Report Part link into the dialogue

Note that in this case there is no Data Context to pass as you’re only linking from object to object.

If you create a one page Report Header you can also copy the “Go to Index” Text Object to this Report Header and then allow the end user to go directly to the index from the title page, thus (sort of) acting like a table of contents. So instead of paging to the second page for the table of contents, the end user clicks on the link to go to the index. Once in the index pages, the end user can easily select the data that they wish to navigate to.

I hope that you find this tecnhique helpful. If you are looking for any other solutions or workarounds for Crystal Reports, please see my blog here.