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.
I have CR for ESRI ('CR Developer, Product Type: Full), version 11.0.0.1282.
Does this very interesting tutorial work with this version?
hi Daniel,
although i've never used cr for esri (only cr with the free esri mapping add-on installed later) the above solution should work without issue. the 3 main things that the sample uses should still be in any OEM version of cr; running totals (type = string), the cross-tab Display String function, and the ability to call a variable (whileprintingrecords) in a cross-tab.
however, please do let us know how it works for your version of cr.
cheers,
jamie
when I open the report, the first pop up window states "The saved data will be discarded since one or more formulas failed to compile."
Clicking 'OK', the next pop up states "This report was created with a version of CR for ESRI which is later than the version you are running. Some features used in the report may not be supported.'
Trying to do a 'print preview' the formula workshop opens up with 'Error: the ) is missing" and this formula: "R = " + totext(currentrowindex,0) + " | C = " + totext(currentcolumnindex)
I believe 'currentrowindex' is the problem and not supported in this version.
Darn
hi Daniel, i apologize as i had left in a ToolTip that would cause this issue for earlier versions.
if you right click on the Max of @Index Group summary of the cross-tab > Format > ToolTip and then delete this formula then it should be okay. i didn't see any other extended cross-tab functionality in there.
hopefully you'll be able to view the report after doing so. if not, the report is based on the xtreme sample database which hopefully ships with your version of cr. if not, please follow the instructions on this blog post to get that database.
again, sorry,
jamie
Solved some of my problems - I upgraded to CR 2011.
I realized I forgot the formatting for the string variable in the crosstab -- so I put that in, and I get this error when I preview the report:
On the line "stringvar tG:= groupsSRT [i to i+length (tH0 +6];" I get "A subscript must be between 1 and the length of the string"
Help!
\Thanks very much for your help!
the answer will be in the debugging expert that will show you all of the values for each of the variables mentioned in the Display String formula. for example, it will show you what the stringvar groupsSRT will be. this is the variable that is written to for each of your Group # 2 Headers in the indexBuilder formula.
i would recommend that you compare the sample report to your report and ensure that you've got this formula on your report and you've also got the fGroup1 & fGroup2 formulae with the fields in the same order as the groups on your report.
if you have a look at the sample, and then un-suppress the indexBuilder formula, you'll see that it writes out the values similar to
||ArgentinaBicicletas Buenos Aires|1||ArubaAruba Sport|1|| etc.
||group1value+group2value|pagenumber||group1value+group2value|pagenumber|| etc.
your report should match up with a similar output. the Display String formula checks to see where the Max of indexGroups is in the SRT (string running total) on each cross-tab row and then tries to parse out the page number.
Jamie - thanks for the reply - I found my error - in the Cross-tab expert -> customize style, I had all the options checked in the col. headed which begins with 'Repeat Row Labels'. Comparing that to your, I unclicked all except 'Keep Columns Together' and the index works.
Seems a little obscure to me, but I won't complain about success.
Thank you very much for your help!
great!!!
Can you post a new link to the report? It's not available anymore.
hi John, thanks for the reply. i just tested the link above and it's still valid. here's the address though if you want to copy it directly.
http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/90e7231b-52ae-2f10-c6a1-f1667f6418b1
cheers,
-jamie
Hi Jamie, I have a huge chunk of data so getting the error "A string can be at most 65534 characters long" in the indexBuilder formula. Please assist?
Thanks
hi Bodi,
this is one of the limitations (i.e. 64k formula output) in crystal that is difficult to get around without a lot of extra effort if you're using a set of formulae. what i would recommend is switching out the index formula's group formula for an ID field that corresponds with the group. then you'll need to change the cross-tab's formulas as well.
if you need help on this, please create a new post in the forums...it'll be a bit of work with a medium level of difficulty.
cheers,
-jamie
Are you able to upload the report again, The SCN system can not find it and this looks great
hi Cathy,
thanks for letting me know that the links are busted...this seems to be a big issue for links. i'll let the scn folks know about this.
-jamie
hi Cathy, i have updated the link above to a zip file in dropbox. unfortunately it looks like a lot of links are busted in the new scn.
https://www.dropbox.com/s/gqsi7em6k7ie48d/Index%20Report%20With%20Hyperlinks.zip?dl=0
i hope the report helps you out, -jamie