Adding an index to a report 1

[SmpTraining]

Has anyone tried adding an index to a report.

Basically I have a list of schools and their addresses spanning about 60 pages, I want to add an index to the schools such that the page number appears alongside the school name at the front of the report.

Any neat ideas? Steve Phillips, Crystal Consultant

 

[mmaz]

I found a way you could do it, but it has its limitations. You could create a formula with the following code:

StringVar myPageNums;

if OnFirstRecord then
myPageNums:= ToText(PageNumber,0)

else
myPageNums:= myPageNums+ "," + ToText(PageNumber,0);

myPageNums

Drop this formula in your group header, and it will return the page numbers of each group in the following format: 1, 1, 3, 4...
where the 1st and 2nd group are on page 1, the 3 group is on page 3 and so on.

You could then pass the value of myPageNums to a sub report and create your table of contents in that sub report. I'm not sure but I think the sub report would have to be at the end of the report...I did this a while ago, so I don't remember..!

Now, the limitation in this is that myPageNums is a string, and a string cannot contain more than 254 characters.

If you know it will never get greater than that, then this will work for you. Otherwise, I really don't know how you could do this.

 

[SmpTraining]

The index needs to appear on one page showing all the groups something like this:


School A Page 8
School B Page 1
School C Page 15
School D Page 3

You get the idea. I don't think your solution will produce these results. Unless anyone has a better idea, I'm trying to achieve this by maintaining a series of arrays that get added to each time I come across a new school, then I'm going to print the array contents at the end of the report.

Thanks anyway mmaz Steve Phillips, Crystal Consultant

 

[mmaz]

Yes, I also tried that with an array, but I had problems when it came to printing the array values on the report. But I'm sure you'll find a solution.

Good luck!

 

[dgillz]

Steve-

Please post your ultimate solution on this one, I am very interested.

Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[IdoMillet]

Crystal Decisions has a document called "Advanced Reporting Techniques Using Arrays" that describes in full detail how to create a report index.

Cheers,
- Ido ixm7@psu.edu

 

[IdoMillet]

Here is the link to the document explaining the technique:

http://support.crystaldecisions.com/communityCS/TechnicalPapers/cr_arrays.zip.asp

Cheers,
- Ido ixm7@psu.edu

 

[Ngolem]

I THINK MMAZ's solution is terrific!

Wish I had thought of that before....I did it very clumsy with arrays and the idea of using a subreport never occured to me.

you are correct that 254 chars is the limit but that should not stop you....just create a shared string array...you initialize all elements to null

I am sure that the field containing the School name is less than 254 chars.....Say the field is 100 characters..so the Page number will start on the 101th character.

create a formula to pad extra spaces for short named schools

trim({table.schoolname}) + space(100 -
length({table.schoolname}))+ totext(pagenumber,0)

your only limitation is there must be less than 1000 schools, although even that can be dealt with too.(just add a second array to fill and loop through if nothing is found in the first array)

Now create a subreport that simply lists the schoolnames and create a formula that searches through the shared array for the schoolname and prints the page number

shared stringVar array schoolarray;
stringvar pageRef := "";
for i := 1 to 1000 do
( if trim({table.schoolname}) = trim(left(
schoolarray,100)) then
( pageRef := "Page " + mid(schoolarray,101);
exit for;
);
);

pageRef;


Now use the "Format multiple columns" option in the section expert and you have it


Thanks for the idea MMaz...Jim

 

[SmpTraining]

Thanks everyone, as I was limited by time I did the best I could with arrays.

After looking at the solution proposed by mmaz in more detail, I should have used that - Thanks.

The solution is exlained well in the Crystal link posted by Ido too - Thanks for that.

Adding indexes is a nice trick to learn, perhaps I'll use it more often now.

Some limitations though:

1. In CR8 you can only have 1000 elements in an array
2. If you want to sort your finished array into a different order using a bubble sort (like I tried to do) there is a limitation of 30,000 loops in a FOR..NEXT loop. That equates to about 150 array elements max that can be sorted.

Now you know!
Steve Phillips, Crystal Consultant