Crystal Reports: Sort Multiple Parameter Values Before Display

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,", ")

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.

Surnames

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.