Crystal Reports: Displaying a Check Mark for a Boolean Value

Crystal Reports gives you a few options of how to display a boolean value including Yes and No, Y and N, True and False, and 0 and 1. But, frequently I want to show a check mark for true values and the empty string for false values. This isn’t a difficult task, I just never remember the correct character codes.

To do this, either create a new formula field or enter the following formula in the Display String formula of the field and set the font property to Windings.

if {Table.Field} then
    Chr(252)
else
    ""

The above is illustrated in the Void column in the image below. When the check has been voided, a check mark appears in the Void column.

If you want to display a check box with a check mark for true and empty for false, use the following code.

if {Table.Field} then 
    Chr(254) 
else
    Chr(168)

Crystal Reports: Add a Line Break in a Formula Field

If you need to add a line break in a formula field just use the ChrW function which “returns the single character text string associated with the Unicode value passed in” with the value of 13. The Unicode value associated with 13 is the carriage return.

// Formula Field Code
"This formula field " + ChrW(13) + " contains a line break!"

// Output
// ------
// This formula field
// contains a line break!

Crystal Reports: Sorting String Fields Numerically

Generally speaking, if you need to sort by a field in a numerical fashion, it is obviously best to have the value of the field be a number. But, I have had a few situations where I needed to store the value of the field as a string but sort it in a numerical fashion (for example, fields where letters are allowed but generally are all numbers). If you do a generic string sort on a set of numbers, it does not come out in numerical order due to the fact that lengths are not taken into account. Anything that starts with a 0 comes first, then with a 1 next, etc. So, the following strings ‘200’, ’10’, ‘3020’, ‘420’, ’11’, ‘8’ get sorted as follows:

10
11
200
3020
420
8

Obviously, not the desired result. On the other hand, if we prepend zeros to each of the strings like so, ‘0200’, ‘0010’, ‘3020’, ‘0420’, ‘0011’, ‘0008’, then the strings will be sorted in numeric order.

0008
0010
0011
0200
0420
3020

So, all you need to do is pad your string field with zeros so that each string is the same length. To do this in Crystal Reports, create a forumla field with the following code.

Right("000000000000000" & {Field},15)

The length of the string of zeros should be as long as the maximum length of the field and the second parameter of the Right function is as well the maximum length of the field. Then sort by this forumal field as opposed to the original field.

A simple solution that for some reason wasn’t completely apparent to me at first.