Dictionary - Standard Functions

  Previous topic Next topic JavaScript is required for the print function Mail us feedback on this topic! Mail us feedback on this topic!  

 

The language used to create Report Builder has some extremely useful functions. Some of these functions are made available to you to use with your reports.

 

A function could be described as a means of converting something to something else. Data is manipulated using a function to generate a desired result. Kind of like a car going through a car wash - it looks one way going in, and another way coming out.

 

Functions are broken into four groups; String, Mathematical, Trigonometric and Date/Time.

 

Standard Functions

 

Here is a more detailed breakdown of the categorized function types:

 

String Functions

 

ALL() - Returns a text string that replicates itself a defined number of times. For example, ALL('A',5) would return the text string 'AAAAA'.

 

CAPITALIZE() - Returns a text string with the first letter in uppercase and the remainder in lowercase.

 

CHR() - Returns the ANSI character from an ASCII code. For example, CHR(122) would return 'z'

 

CLIP() - Returns a string with the trailing spaces removed. Commonly used to properly format names and addresses into a more aesthetic appearing text string; multiple database fields are combined together using the concatenation symbol (&) to form a formatted string. For example, the database field Res:City that contains the city name 'Tucson' could be combined with the Res:State field that contains 'Az'. Because the Res:City database field has 18 spaces allocated to it, and only 6 positions were used for 'Tucson', you would normally have 12 blank spaces:

 

Tucson            Az

 

not exactly appealing! Using the CLIP() function and the concatenation symbol you can convert this to:

 

Tucson, Az

 

EX: CLIP( Res:City ) & ', ' & CLIP( Res:State )

 

Note that there is a blank space after the comma to "push" the state over one position to avoid crowding the comma character.

 

DEFORMAT() - Returns a string that contains the unformatted characters of a numeric string. For example DEFORMAT('999-53-9954') returns 999539954

 

FORMAT() - Returns a formatted string; knowledge of the PICTURE command is mandatory to make best use of this function. For example SSN = FORMAT( Emp:SSN,@P###-##-####P), which would properly format the database field EMP:SSN to a standard social security number.

 

INLIST() - Returns the position of an item in a comma delimited list. For example, INLIST('D','A','B','C','D','E') returns a value of 4

 

LEN() - Returns the length of a string. For example, LEN('Scott Daughtry') would return a value of 14.

 

LOWER() - Returns the lowercase version of a string. For example LOWER('SCOTT Daughtry') would be returned as 'scott daughtry'.

 

NUMERIC() - Returns a true or false. If a string contains only numbers a true is returned. For example NUMERIC('123') would be true, and NUMERIC('1,23') would be false.

 

SUB() - Returns a portion of a string. You define where the starting and stopping positions are in the string. For example, SUB( 'Scott Daughtry', 1, 5 ) would extract positions 1-5 of the string, returning 'Scott'. SUB( 'Scott Daughtry', 7, 3 ) extracts 'Dau' - the starting position is 7, and the next 3 positions are extracted from that starting point. You can also work backwards on a string by assigning a negative starting position. For example, SUB( 'Scott Daughtry', -1, 3 ) would return 'try'.

 

UPPER() - Returns an uppercase version of a text string. For example, UPPER( 'scoTT') is returned as 'SCOTT'.

 

VAL() -  Returns the ASCII character numeric equivalent for an ANSI entry. For example, VAL('A') would return 65.

 

Mathematical Functions

 

ABS() - Returns an absolute value from an expression. The absolute value of a number is always a zero. For example, C = ABS( A-B ). C would contain the absolute value of the difference of B subtracted from A

 

INRANGE() - Compares a numeric expression for an inclusive range of numbers. Returns a value of true if the scope is met, and false otherwise. For example, INRANGE( variablename, 1, 5 ) would check if the variablename is within the numbers 1-5, and return true if so. The starting number is 1, and ending number is 5.

 

INT() - Returns an integer value from a numeric expression. For example, INT( 8.5 ) would return a value of 8.

 

LOGE() - Returns the natural algorithm. The natural logarithm of a value is the power to which e must be raised to equal that value. The value of e used internally for these calculations is 2.71828182846. For example LOGE(2.71828182846) returns 1; LOGE(1) returns 0

 

LOG10() -Returns base 10 logarithm. If the value of the expression is zero or less, the return value will be zero. The base 10 logarithm is undefined for values less than or equal to zero. The LOG10 procedure returns the base 10 logarithm of a numeric expression. The base 10 logarithm of a value is the power to which 10 must be raised to equal that value. For example, LOG10(10) returns 1; LOG10(1) returns 0

 

RANDOM() - Returns random integer.  Returns a random integer between the low and high values, inclusively. The low and high parameters may be any numeric expression, but only their integer portion is used for the inclusive range. For example, RANDOM( 22, 80 ) would return a random number between the numbers 22 and 80.

 

ROUND() - Returns a rounded number. For example, ROUND( 5163, 100 ) would return the rounded number of 5163+100 (which is 5200); ROUND(657.50,1) returns 658; ROUND(51.63594,.01) returns 51.64

 

SQRT() - Returns the square root of a number.

 

Trigonometric Functions

 

SIN() - Returns the trigonometric sine of an angle measured in radians. The sine is the ratio of the length of the angle's opposite side divided by the length of the hypotenuse. For example, returns the trigonometric cosine of an angle measured in radians. The cosine is the ratio of the length of the angle's adjacent side divided by the length of the hypotenuse. For example, sineAngle = SIN( 45 * 3.142498712)

 

COS() - Returns the trigonometric cosine of an angle measured in radians. The cosine is the ratio of the length of the angle's adjacent side divided by the length of the hypotenuse. For example, cosineAngle = COS( 45 * 3.142498712)

 

TAN() - Returns the trigonometric tangent of an angle measured in radians. The tangent is the ratio of the angle's opposite side divided by its adjacent side. For example, TanAngle = TAN( 45 * 3.142498712)

 

ACOS() - Returns the inverse cosine. The inverse of a cosine is the angle that produces the cosine. The return value is the angle in radians. p is a constant which represents the ratio of the circumference and radius of a circle. There are 2p radians (or 360 degrees) in a circle. For example, acos = ACOS( 45 * 3.142498712)

 

ASIN() - Returns the inverse sine. The inverse of a sine is the angle that produces the sine. The return value is the angle in radians. p is a constant which represents the ratio of the circumference and radius of a circle. There are 2p radians (or 360 degrees) in a circle. For example, asine = ASIN( 45 * 3.142498712)

 

ATAN() - Returns the inverse tangent. The inverse of a tangent is the angle that produces the tangent. The return value is the angle in radians. p is a constant which represents the ratio of the circumference and radius of a circle. There are 2p radians (or 360 degrees) in a circle. For example, TanAngle = ATAN( 45 * 3.142498712)

 

Date/Time Functions

 

TODAY() - Returns the operating system date as a standard date. The range of possible dates is from January 1, 1801 (standard date 4) to December 31, 2099 (standard date 109,211). You can use a picture command to change the appearance of the return value. For example, cToday = format( TODAY(), @d01B )

 

CLOCK() - Returns the current time based upon your computer's BIOS time.

 

DATE() - Returns a standard date for a given month, day, and year. The month and day parameters do allow positive out-of-range values (zero or negative values are invalid). A month value of 13 is interpreted as January of the next year. A day value of 32 in January is interpreted as the first of February. Consequently, DATE(12,32,97), DATE(13,1,97), and DATE(1,1,98) all produce the same result. The century for a two-digit year parameter is resolved using the default "Intellidate" logic, which assumes the date falls in the range of the next 20 or previous 80 years from the current operating system date. For example, assuming the current year is 1998, if the year parameter is "15," the date returned is in the year 2015, and if the year parameter is "60," the date returned is in 1960. For example, HireDate = DATE(Hir:Month,Hir:Day,Hir:Year); FirstOfMonth = DATE(MONTH(TODAY()),1,YEAR(TODAY())) would compute the first  day of month

 

DAY() - Returns day of month. Computes the day of the month (1 to 31) for a given standard date. For example, OutDay = DAY(TODAY()) to get the day from today's date; DueDay = DAY(TODAY()+2)  to calculate the return day

 

MONTH() - Returns the month of the year (1 to 12) for a given standard date. For example, PayMonth = MONTH(DueDate) to get the month from the date

 

YEAR() - Returns a four digit number for the year of a standard date (1801 to 9999). For example, YEAR( today() )

 

AGE() - Returns a string containing the time elapsed between two dates. The age return string is in the following format: 1 to 60 days - 'nn DAYS'; 61 days to 24 months - 'nn MOS';  2 years to 999 years - 'nnn YRS'. For example, nAge = AGE( EMP:Birthday, TODAY()) would compare the database field EMP:Birthday against the computer date to return the persons age.