To use system-defined functions in expressions, select a function from the drop-down list and click Add. Then, from the Fields tab, select the field you wish to use with the function and click Add. The field will appear in the expression, enclosed in parentheses.
You can’t apply one system-defined function to another, or “nest” one function within another. For example, if you’re using the CUR_STR function to convert a currency field to a string, you can’t use the STRIP function to remove extra spaces from the same field. For example, the following is not permitted:
STRIP (CUR_STR ( Beginning Balance ) )
If the field you wish to create requires using two system-defined functions, first create a calculated field using the first function, then create another calculated field that applies a second function to the first calculated field, as in the following example:
![[spacer]](1p.gif){kind=small}
|
Calculated field 1 |
CUR_STR ( Beginning Balance ) |
|
Calculated field 2 |
STRIP ( Calculated field 1 ) |
Each system-defined function takes only one parameter. The following briefly explains each of these functions:
Converts a field with a currency storage type to a string type.
Takes a date value and returns the day portion of the date. The integer value returned will range from 1 to the maximum number of days in the month.
Takes a date value and returns a string containing the name the day of the week for the date value.
Takes a date value and returns an integer representing the day of the week the specified date falls on. The following table lists the integer values and the corresponding day:
|
Integer value |
Day |
|---|---|
|
0 |
Sunday |
|
1 |
Monday |
|
2 |
Tuesday |
|
3 |
Wednesday |
|
4 |
Thursday |
|
5 |
Friday |
|
6 |
Saturday |
Converts a field with an integer storage type to a string type.
Prints only the data that lies to the left of the caret (^) in a field with a string storage type. If there is more than one caret in the field, the function will print only the characters to the left of the leftmost caret. The caret must be part of the field, not part of a format applied to the field’s data type. If there is no caret in the string, the entire string will be printed, provided enough space is allocated in the layout area.
Converts a field with an integer or long integer storage type to a string type.
Takes a date value and returns the month portion of the date. The integer value returned will range from 1 to 12.
Takes a date value and returns a string containing the name of the month for the date value.
Multiplies 10 to the power specified as this function’s parameter. For example, the value of POWER_10(2) is 10 to the power 2, or 100. This function is useful for dealing with currency fields and decimal placement.
Prints only the data that lies to the right of the caret (^) in a field with a string storage type. If there is more than one caret in the field, the function will print all the characters to the right of the leftmost caret. The caret must be part of the field, not part of a format applied to the field’s data type. If there is no caret in the string, no data will be printed.
Converts a field with a string storage type to a date. The string of characters you convert to a date must be in MM/DD/YY format. If the year listed is 35 or less, the year will be preceded by “20,” so that the year will be 2035, for example, instead of 1935.
Converts a field with a string storage type to a currency type. The current control panel settings for the currency symbol, thousands separator, decimal symbol, and negative currency format will be used as the basis for analyzing the string. If the string cannot be properly interpreted based on the current control panel settings, it will be interpreted using the following rules:
Counts the number of characters, excluding blanks at the end of the string of characters, in a field with a string storage type. Blanks within a string are counted.
Converts a field with a string storage type to a long integer. This function can be used to compare a string field to a long integer field. Since Report Writer can’t compare two fields with different storage types, using the STR_LNG function to convert the string field to a long integer field allows you to compare it to other long integer fields.
Converts a field with a string storage type to a variable currency type. The string will be interpreted using the following rules:
Removes trailing blank spaces from fields with string storage types. The STRIP function can be used with the CAT operator, but can’t be applied to an expression containing the CAT operator.
For example, the following expression is valid:
STRIP (Cust_Name) # " has this address: " # STRIP (Address)
The following expression is invalid:
STRIP (Customer Name # Address)
Searches fields of the string storage type for a match of the specified string pattern, regardless of whether that match occurs at the beginning of or within the string field. Searches are case-sensitive.
Three special characters can be used in the search pattern: the asterisk (*), the question mark (?) and the backslash (\), as indicated in the following table.
|
Character |
Matching search results |
|---|---|
|
* |
Any character Any group of characters No characters |
|
? |
Any single character |
|
\ |
That the following special character is to be treated as part of the search pattern |
The backslash is used in front of special characters that have specific meaning in Report Writer, such as quotation marks that indicate the beginning or end of a field. If you want to search for fields containing a quotation mark, you’ll need to place a backslash in front of it to indicate that the quotation mark is simply a character, and not the sign for the end of the field. Most symbols, such as the pound sign (#) or the at sign (@), are special characters and should be preceded by backslashes if you want them to be evaluated literally in an expression.
The difference between this function and the WILDCARD function is that this function implicitly adds an asterisk to the beginning and end of the pattern you specify. For example, the SUBSTRING function would find a match for the pattern P*L in the word APPLE, while the WILDCARD function wouldn’t.
You must use the SUBSTRING and WILDCARD functions with the equality (=) and inequality (<>) operators in conditional expressions, to ascertain whether the data in the field matches or doesn’t match the pattern.
In an expression, the SUBSTRING and WILDCARD functions should always precede the name of the field you want to match to the pattern. For example, the following example would check whether the values for the Name field contains the string Ann. Each of the following names would be considered matches: Anne Jones, MaryAnn Magaldi and Jose Annez.
SUBSTRING ("Ann") = Name
The following example checks the First_Name field for all names containing the letter “t” separated from the letter “n” by one other character. Names that would match this search include “Astin”, “Barton” and “Antonette”.
SUBSTRING ("t?n") = First_Name
Prints the alphabetic characters in the specified string field in uppercase. Numeric characters won’t be affected.
Searches fields of the string storage type for an exact match of the specified string pattern. Searches are case-sensitive.
You can use the *, ? or \ special characters to indicate the type of match you’re searching for. These special characters have the same effect as they do with the SUBSTRING function.
Unlike the SUBSTRING function, this function does not implicitly add an asterisk to the beginning and end of the pattern you specify. Therefore, this function is useful when you want to search for strings that begin with a certain character or set of characters.
The following example checks the First_Name field for all names beginning with the letters “To”. Names that would match this search include “Tony”, “Tonya” and “Tomas”.
WILDCARD ("To*") = First_Name
The following example checks the Local_Businesses field for all stores beginning with the string “Tommy’s”. Note that this search uses both the \ and * special characters. Business names that would match this search include “Tommy’s Pizzeria”, and “Tommy’s Bait and Tackle Shop”.
WILDCARD ("Tommy\'s*") = Local_Businesses
Takes a date value and returns the year portion of the date. The integer value returned will be a four-digit year, such as 2001.