Crystal Reports: Strings longer than 254 characters

See also: Split a String

We can use a formula or "For" loop to build up a string to display in our report, but in Crystal Reports it has a physical limit of 254 characters. How can we display a longer string?

Carol Kesen of Phoenix, Arizona, has sent us this suggestion and solved the challenge.

Carol’s Crystal Report builds a list of a series of field values and displays the list in a report. A formula in the group header (suppress printing):

whileprintingrecords;

Global StringVar Lots1 := "" ;

Global StringVar Lots2 := "" ;

Global StringVar Lots3 := ""

Formula in details section (suppress printing):

whileprintingrecords;

Global StringVar Lots1 ;

Global StringVar Lots2 ;

Global StringVar Lots3 ;

if length(Lots1)<220 then

Lots1 := Lots1 + {table.Lot}+", "

else

if length(Lots2)<220 then

Lots2 := Lots2 + {table.Lot}+", "

else

if length(Lots3)<220 then

Lots3 := Lots3 + {table.Lot}+", "

The formulas in each of Group footer a,b,c for each of Lots1, Lots 2 and Lots 3

whileprintingrecords ;

Global StringVar Lots1 ;

left(Lots1,length(Lots1)-2) // to remove the trailing comma and space

And the final thing is set the section formatting for group footer a,b,c to "Suppress if blank"

This solution trebles the available length of a string to around 660 characters. If you need a longer list, just use more variables until you have the capacity to hold the maximum list you need.

An even better solution to the long string problem is to rethink the report. If your report is displaying long strings, then probably your report design focus has been on presenting data rather than meeting the information needs of the report user.

Unfortunately, most people don't really read anymore. If they see a long data string, they'll visually scan past it. It's just meaningless clutter on the page, for them.

You may think your highly-paid executives are mulling over critical business information, carefully digesting what they read so they can make solid business decisions. But that is seldom the case in any organization. Executives lead fragmented work days, and the higher up the chain they are the more this tends to be true. It does work against their effectiveness, but that issue doesn't cause them to change their behavior. They react, instead of respond, to the many demands placed on them.

You can help these executives by crystallizing the information they need. Crystal Reports isn't meant to just reformat data, but to crystallize information. Make that fact your driving principle behind your report design.

So rather than toss that long string out there, ask "What does that long string represent?" It's the representation you want the report to convey to the reader. Not the actual data.

Figuring all this out may take a long time. You may need to do considerable research to determine what the string really needs to convey. So an instant fix via report redesign may not be possible. In the interim, consider using another interesting technique for the "data mode" kind of report design you currently have. You can split a string. But how?

Room No. from "4" to "6".

Room No. from "56" to "Store"

The request was to extract the values from between the quotes. After several approaches the simplest

solution was to use this formula to extract the first value.

split({@yourfield},'"')[2]

And the second value was extracted with

split({@yourfield},'"')[4]

The way this works is to split the field into a string array using the quote delimiter. Then the first member of the array is the value prior to the first quote, and can be ignored. The first formula then returns the second value in the array.

The third value in the array is the value between the two quoted values and the fourth value in the array is the second quoted value and is returned by the second formula.

Thanks CoSpringsGuy for an interesting solution to a complicated problem.

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.