Crystal Reports: Ripple Sort

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.

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.

Multiple Parameters

You can also sort your multiple value parameter values before you display in a heading.

In a training course, we were showing how you could use the join function in a formula to show the list of parameter values. We address this in a separate article. It's a handy technique. It is interesting how variations of these techniques keep appearing.

A student asked, "What if the user had entered a long list of values? Could it be sorted prior to printing in the heading?"

Challenge given, we thought about how to do this. The easiest way would be to develop a User Function Library and use one the internal Visual Basic sort methods.

But this approach wouldn’t be available to everyone out there.

So you would need to sort the array inside your formula. One of the first algorithms any programmer learns is the old “ripple sort,” so we could use that approach with Crystal arrays and variables. The following formula works nicely:

local stringvar array myList:={?Country}; //load the array
Local stringvar temp;
local numbervar i;
local numbervar j;
for i:= 1 to count(myList)-1 do //First loop of the ripple sort
for j:= 1 to count(MyList)-i-1 do //second loop
if MyList[j] > MyList [j+1] then ( //Out of order - swap them
temp := MyList[j];
MyList [j] := MyList [j + 1];
MyList [j+1] := temp;);
join(mylist,", ")

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.