	Resources
	Best sellers: cView Report Analyzer cViewSERVER ReCrystallize

	Articles: Administration Advanced Basic Crystal eNL Database Financial Problems Solved Books: CR Books Database Books Developer Books
	Tools: Analyzers Bestsellers CR Schedulers CR UFLs CR Viewers DataBase Tools Graphics International Mail UFLs ReCrystallizePro
	Add'l: About us Contact Us cViewSUITE Ppt Support
	CrystalReports on Steroids

Crystal Reports: Sort Fields

It is important to sort your data, but sometimes you need to sort the report by fields that are not currently in your Crystal Report.

Barry Gray of Auckland University reminded us that if the field isn’t in your Crystal Report, it isn’t on the list of available sort fields. To add the field to the list, just put the field somewhere on the report--then you can sort on that field.

The interesting point is that you can now remove the field from your report and the sort is still valid. Version 8 users will have discovered that they can sort on any field or formula. The choose field form also lists fields that are not yet in the report. Users of Version 7 and earlier will need to use Barry’s original suggestion.

To keep track of what fields you have, use Report Analyzer. This powerful tool will document your Crystal Reports and provide other functionality that will save you time, reduce your costs, and make you look good. Check it out.

Ripples

And if you don't have this particular problem you may wish to use another tool. Sort a string of values, using a ripple sort.

A question on Tek-Tips asked how to take a string with a list of values (“A/J/W/X/C”) and to sort the values into alphabetic order. The solution used the traditional ripple sort, which we would like to share with you here.

StringVar EmpNames:="Smith, J/Jones B/Brown M";
stringvar array sort;
numbervar loop;
numbervar loop2;
stringvar temp;
sort:=split(EmpNames, "/");
for loop2:=1 to ubound(sort)-1 do(
for loop:=1 to ubound(sort)-loop2 do(
if sort[loop] > sort[loop+1] then
(temp := sort[loop];
sort[loop] := sort[loop+1];
sort[loop+1] := temp)));
Join(sort,"/")

The “Split” function extracts the values from the string into an array. The two loops cycle through the array and shuffle the largest value to the end of the array. The inner loop is done fewer times as each value is shuffled along the array.

The “if” statement swaps the values if needed. And the “join” at the end puts the array back into a string.

Surnames

Sometimes, we have to do clever things in our Crystal Reports to solve a limitation in the database design or to compensate for the fact our users have entered data in an unusual way.

A database should store the first name and surname of an individual in separate fields. But sometimes it doesn’t. If your database has a name field stored as "Firstname Surname" you can still sort the data by the surname by using the following formula.

Numbervar i:=instr({table.field}," "); // There is a single space between the quotes

If i=0 then {table.field} else

mid({table.field},i+1,length({table.field}-i) +", " + left({table.field},i);

We’ve used a variable to save the location of the first space in the name field. It makes the formula shorter and easier to maintain, but it’s also important--in case the surname isn’t there.

This formula will have problems with people who have two first names. That’s a "quality of data" issue we can’t solve with a report. Or could you write a formula to handle this situation, as well? (You can).

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.