The Formula Editor in Crystal Reports gives you the ability to write very powerful functions. As you saw in Chapter 8, Crystal Reports lets you write functions to manipulate and analyze variables so that you can report on data specific to your business. In addition to writing your own formulas, Crystal Reports has dozens of built-in functions that decrease the amount of work you have to do. After all, why re-invent the wheel when you don’t have too? This chapter shows you the different functions that come with Basic syntax. The functions are grouped by task so you can quickly find the ones you want. If you prefer to have an alphabetical list, just reference the Index at the back of the book to see the page number of the one you are looking for. This chapter has a section for each of these categories: String Functions, Converting Data Types, Formatting Values for Output, Math Functions, Generating Random Numbers, and Date and Time Functions,
String Functions
The ability to modify and concatenate strings is a powerful feature of many programming languages, and Basic syntax doesn’t disappoint. This section breaks out the different categories of string functions and summarizes how they work. The categories are: Analyzing a String, Parsing Strings, and Manipulating Strings.
Throughout this section, many functions are listed that use one or both of the parameters called compare and start. Rather than repeat a description of those parameters for each function, they are explained here for your reference.
The compare parameter determines when string comparisons are supposed to be case sensitive. If compare is 0, the search is case-sensitive. If it is 1, the search is not case-sensitive. This parameter is optional. If it is left out, the comparison defaults to 0, case sensitive.
The start parameter tells the function to process characters starting at the specified index. Any characters that are prior to that index are ignored. This parameter is optional. If it is left out, the function will be performed for the entire string.
Analyzing a String
Strings are used to store a variety of data that are displayed in a report. They can come from a variety of sources such as database tables, user input or even XML. Most of the time you will want to output the string directly to the report. But there are times when the information you want is stored as part of a larger string and you need to extract that data. To do this, it is necessary to analyze and parse a string’s contents. Basic syntax gives you many functions for doing this. Table 9-1 shows the functions for analyzing a string’s contents. Table 9-3 shows the functions for extracting sub-strings from a string. As a .NET programmer, you are already familiar with this functionality. Descriptions of each function are listed next to its name. Unless otherwise noted, the functions act the same as their .NET equivalents.
Table 9-1. String Analysis Functions
| Function Name | Description |
|---|---|
| AscW(str) | Returns the ASCII value of a character. |
| ChrW(val) | Returns a character equivalent of an ASCII value. |
| Len(str) | Gets the number of characters in the string. |
| IsNumeric(str) | Tells if it can be properly converted to a number. |
| InStr(start, str1, str2, compare) | Determines if str2 is a sub-string of str1. The start and compare parameters are both optional. |
| InStrRev(start, str1, str2, compare) | Same as InStr(), but it starts at the end of the string and searches towards the beginning. |
| StrCmp(str1, str2, compare) | Compares two strings to each other. The compare parameter is optional. |
| Val(str) | Returns the numeric equivalent |
The StrCmp() function returns a value based upon how the two strings compare to each other. Table 9-2 summarizes what these results mean. Just like the Instr() functions, you can pass a compare parameter to set case sensitivity.
Table 9-2. StrCmp(str1, str2) Return Values
| Return Value | Description |
|---|---|
| -1 | str1 < str2 |
| 0 | str1 = str2 |
| 1 | str1 > str2 |
Table 9-3. String Parsing Functions
| Function Name | Description |
|---|---|
| Trim(str) | Trim the spaces from both sides of a string. |
| LTrim(str) | Trim the spaces from the left side of a string. |
| RTrim(str) | Trim the spaces from the right side of a string. |
| Mid(str, start, length) | Return a given number of characters starting at a specified position. The start and length parameters are optional. |
| Left(str, length) | Return a given number of characters starting with the leftmost character. |
| Right(str, length) | Return a given number of characters starting with the rightmost character. |
The Trim() functions will delete all extraneous spaces from either side of the string, depending on which function you call.
The Mid(), Left(), and Right() functions return a partial string whose size is based upon the number of characters you pass to the function. If you don’t pass a length to the Mid() function, it returns all characters starting with the first one you specified.
