Crystal Reports 10: The Complete Reference

Many times, the database will contain string or text data that is insufficient for your reporting needs. For example, you may want to sort a report by ZIP code, but the database contains ZIP codes only as part of a City_State_Zip field. Or, you may want to write a report to print checks, spelling out the dollar amount in words, using a number or currency field in the database. All of these are applications for a formula that either manipulates or creates string data.

Strings can be concatenated , or tacked together, using the plus sign or the & sign (ampersand). Although the plus operator is the same one used to add numbers , the results will be very different depending on the data type. For example, the formula

25 + 7 + 100

returns a numeric result of 132. Because all the elements of the formula are numbers, the plus sign will add the numbers together and return a numeric answer.

Contrast that with this formula:

"25" + "7" + "100"

or

"25" & "7" & "100"

which returns a string result of 257100. When you enclose the numbers in quotation marks, Crystal Reports interprets the values in the formula as strings, not numbers. When you use a plus sign or ampersand with strings, the result is the concatenation of the individual string elements.

This is very useful for many situations you ll encounter when reporting against databases. The following illustration shows the beginning of a form letter that simply uses database fields on the report.

Notice the spacing problems for the contact name , city-state-ZIP line, and salutation. While you may be able to improve the appearance of this report slightly by resizing and moving the individual database fields, you ll never achieve a perfect result. By placing the database fields on the report in fixed locations, the report will never be able to accommodate varying widths of first names, last names , cities, and so on.

The next illustration shows the same report using formulas to concatenate the database fields together.

The results are obvious: No matter how wide or narrow names or cities are, they are placed right next to the other items (with a space in between).

While you can conceivably combine these database fields in text objects to accomplish the same result (refer to Chapter 2 if you forgot about text objects), formulas give you a lot more flexibility in how your report looks. For example, you can display only the first initial of a contact name on the report using a formula. You can t do that with a text object.

Concatenating string fields is as simple as using the ampersand (&) operator, as in the following:

{Customer.Contact Title} & " " & {Customer.Contact First Name} & " " & {Customer.Contact Last Name}

Notice that a space is hard-coded into the formula using a string literal, which is simply a fixed string surrounded by quotation marks. You ll use string literals often in concatenation formulas and If-Then-Else formulas (discussed later in this chapter). So, the literal space in this formula will separate the title from the first name, and the first name from the last name.

You could create a salutation line using several string literals, as follows :

"Dear " & {Customer.Contact Title} & " " & {Customer.Contact Last Name} & ","

Notice that the word Dear and a space precede the title, a space separates the title from the last name, and a comma follows the last name.

There are many situations in which you may want to use only certain parts of strings in a formula, not the whole string. For example, you may want to use only a first initial as part of the contact name on a form letter. By following a string field with a number or range of numbers enclosed in square brackets (Crystal syntax) or parentheses (Basic syntax), you can extract certain characters from the string field. This function is known as the subscript function. For example, look at this formula in Crystal syntax:

{Customer.Contact Title} & " " & {Customer.Contact First Name}[1] & ". " & {Customer.Contact Last Name}

Notice that only the first character from the first name will be included in the formula, and a period has been added to the space literal between the first and last names.

Many older database systems contain date information in string fields, because earlier versions of mainframe systems or older database systems did not include a date data type. To allow dates to sort correctly, the year needs to precede the month and day in these fields as well. Thus, it is very common to find January 10, 1999, coded into a string database field as 19990110.

If you want to display the date in an mm/dd/yyyy format to make the date more readable, you could use string subscript operators to pick out the individual parts of the date, add some string literals, and rearrange the date s appearance. Assuming that the hire date in a legacy database is an eight-character string field in the form yyyymmdd, this formula will redisplay it as mm/dd/yyyy:

{EMP.HIRE_DATE}[5 to 6] & "/" & {EMP.HIRE_DATE}[7 to 8] & "/" & {EMP.HIRE_DATE}[1 to 4]

Notice that the subscript operator can also return a range of characters, not just one.

In addition to the subscript operator, there are many built-in string functions that you can use in your formulas. There are functions to return characters from the left of a string, the middle of a string, and the right of a string. Using these, the preceding date formula could be rewritten in Crystal syntax as follows:

Mid({EMP.HIRE_DATE},5,2) & "/" & Right({EMP.HIRE_DATE},2) & "/" & Left({EMP.HIRE_DATE,4})

In this case, the Mid function takes three arguments: the field or string to use, the position in the string from which to start reading, and the number of characters to return (there s also a two-argument version of Mid). The Right and Left functions take two arguments: the field or string to use and the number of characters to return.

The ToText Function

A crucial built-in function that you will use very often in string formulas is ToText, which is used to convert other data types to a string data type so that you can use them in concatenation or comparison formulas. You can use ToText to convert numbers, dates, times ”virtually any other data type ”to strings. You need this functionality to avoid the type of problem shown here:

This problem occurs because you can t concatenate a number field onto a string literal (or any other combination of mismatched data types). To convert a number or currency field to a string, such that you can concatenate it to another string, use ToText. The following will solve this problem:

"Thanks for purchasing " + ToText({Customer.Last Year's Sales}) + " in merchandise last year."

That formula will return

Thanks for purchasing $32,421.27 in merchandise last year.

If you look in the Function Tree box under Strings, you ll see several permutations of ToText, with anywhere from one to five arguments.

While the best place to look for all the ToText details is Crystal Reports online Help, here are a few additional ToText examples:

• Determine the number of decimal places:

  "Thanks for purchasing " & ToText({Customer.Last Year's Sales},0) & " in merchandise last year."

  will return

  Thanks for purchasing ,421 in merchandise last year.

  A second number argument to ToText determines how many decimal places Crystal Reports uses when it converts the number or currency field to a text string.

• Determine the thousands separator:

  "Thanks for purchasing " & ToText({Customer.Last Year's Sales},0,"") & " in merchandise last year."

  will return

  Thanks for purchasing 421 in merchandise last year.

  A third string argument to ToText determines what thousands separator Crystal Reports uses when it converts a number or currency field to a text string. In this example, the two quotation marks side by side indicate an empty string, so ToText doesn t use a thousands separator.

• Format date fields:

  "Your order was placed on " + ToText({Orders.Order Date},"dddd, MMMM d, yyyy.")

  will return

  Your order was placed on Monday, January 12, 2004.

  This version of ToText uses a format string as the second argument. The format string (sometimes referred to as a mask ) uses special characters ” pound signs; zeros; decimal points; the letters d, m, y; and so forth ”as placeholders to indicate how data should be formatted when converted to a string. In this example, the dddd characters specify the day of the week to be spelled out fully. MMMM specifies a fully spelled month, d specifies the day of the month without a leading zero, and yyyy specifies a four-digit year. Any characters included in the format string that can t be translated into placeholders (such as the commas and periods) are simply added to the string as literals.

  Tip

  It s important to remember that the case of the placeholder characters is significant. When formatting time fields, for example, lowercase h characters indicate hours with a 12-hour clock, whereas uppercase H characters indicate hours with a 24- hour military clock. Apart from that, if you use placeholder characters of the wrong case, Crystal Reports will just include the characters in the resulting string as literals.

Another built-in function, ToWords, comes in handy when writing checks with Crystal Reports. Consider the following formula:

ToWords({PAYROLL.NET_CHECK_AMT})

When placed on the report, this formula returns the following for an employee record with net pay of $1,231.15:

one thousand two hundred thirty-one and 15/100

Picking Apart Strings

In more complex reports, you may wish to pick out only certain parts of strings (the first initial of a name was mentioned previously as an example). Crystal Reports contains many interesting formula functions to handle even more complex substring requirements.

For example, you may wish to make use of the ToWords function discussed previously, but not to print checks. If you are printing legal contracts, real estate documents, or bank notes, for example, you may need to print both the numeric and text forms of the same number, as in:

This contract expires in thirty (30) days.

For the sake of this example, assume that the data indicating the number of expire days is contained in a numeric database field. Accordingly, you may try creating a formula to print the previous example. The formula might initially look like this:

"This contract expires in " & ToWords({Contracts.Expires}) & " (" & ToText({Contracts.Expires},0) & ") days."

In this example, the ToWords function is used to convert the numeric field to spelled out text, and ToText is used to convert the number to a numeric string, with no decimal places.

However, the result of this formula will be:

This contract expires in thirty and xx / 100 (30) days.

Because ToWords is designed for printing checks, it returns not only the whole number part of the numeric field, but the hundredths part as well. For the contract example shown previously, this won t work. How, then, can you create a formula to return just the whole number portion?

If you look closely at ToWords, you ll notice that the whole number portion is always separated from the hundredths portion with the word and. If you can search through the result of ToWords for the and characters, and return just the portion before these characters, you will have a successful formula.

Look at this example:

"This contract expires in " & Left(ToWords({Contracts.Expires}), InStr(ToWords({Contracts.Expires}), " and ")) & "(" & ToText({Contracts.Expires},0) & ") days."

There are several functions and techniques used in this formula that require further explanation:

• The Left function The Left function returns a certain number of characters from the left side of another string (and, as you might imagine, there is also an available Right function to return characters from the right side of string). In this formula, the desire is to return the left portion of the words returned by ToWords up until the characters and are encountered. So, if ToWords returns thirty and xx / 100 , you wish to extract the first six characters from the left (up until the and is encountered ). But, how do you determine where the characters and are?

• The InStr function There are several different versions of InStr in the formula language. The version shown in the previous example takes two arguments: the string you want to search, and the substring you want to search for within the first string. By supplying the ToWords function as the first argument, and the characters and as the second argument, you are asking InStr to find the numeric position in thirty and xx / 100 where the word and is. Here the word and begins at position 8, which is the numeric result from the InStr function.

So, by using the InStr function within the Left function, you can extract the proper number of characters to get just the whole number portion of the spelled-out number. The only adjustment you must consider is that InStr returns a value of 8, but you want only the first six characters (or seven, if you don t mind having a trailing space). This is why you see the “ 1 subtraction of the value returned by InStr. This will return the leftmost seven characters, including the trailing space, resulting in the characters thirty appearing as the spelled-out text.

There are many other built-in string functions that can perform similar functions. Look in the functions tree under Strings, or in online help, for information on other versions of ToText, InStr, Mid, and many others.