Expressions are properties of report elements just like foreColor, backColor, and textFont. Two expression types are defined in Instant Report: Text Expression and Conditional Expression. They both allow you to define arithmetic and logical functions and calculations whose runtime values will be used to fill text fields (Text Expression) or influence the formatting of other report elements (Conditional Expression).
To build an expression, drag and drop the Text-Field in case of Text expression or any report element in case of Conditional expression. Click anywhere around the element, the property sheet for the element appears on the property inspector window. Click on the Value column cell next to expression in case of Text expression or next to c_expression in case of Conditional expression. In both cases, the Expression builder dialog box appears (See Fig.1 below).
Fig.1: A section of the expression builder dialog box
Text expressions, labeled expression, are properties of Text-Field report elements. The values they evaluate to are at runtime to fill their respective Text-Fields. They can evaluate to numbers, strings, dates, and timestamps.
For example, to create a text expression Page n for page number:
To create another text expression Price + Price*0.05 to represent the price plus tax, assuming we have a data source field named Price:
Conditional expressions, labeled c_expression, are properties of all report elements. They allow you to do conditional formatting on report elements by making the values of affected properties of the report element dependent on whether the conditional expression evaluates to a true or false. Therefore, conditional expressions must always evaluate to true or false.
Instant Report automatically adds some useful fields to do summary calculations in addition to your data source fields. These fields are named based on the calculation type (_COUNT, _MIN, _MAX, _SUM, _AVG, _STDDEVIATION, _VARIANCE) they perform and the reset or re-initialization level (_REPORT, _PAGE, _COLUMN, _groupName). The reset/re-initialization level gives you some hints on which report section to drop a Text-Field containing the field as its expression. For example, it will make sense to drop Text-Field with an expression that contains a field named Freight_MAX_REPORT (holds the maximum freight value for the report) on the report footer section. PAGE_NUMBER, PAGE_COUNT, REPORT_COUNT, and COLUMN_COUNT are system-defined fields that are always present. For example, for every data source field named myfield the following additional report level fields become available:
Field: |
Description: |
myfield_COUNT_REPORT: |
Count of all myfield values in the report. |
myfield_MAX_REPORT: |
Maximum myfield value in the report. |
myfield_MIN_REPORT: |
Minimum myfield value in the report. |
If myfield is a number, the following report level fields also become available: |
|
myfield_SUM_REPORT: |
Sum of myfield all myfield values in the report. |
myfield_AVG_REPORT: |
Average of all myfield values in the report. |
myfield_STDDEVIATION_REPORT: |
Standard deviation of all myfield values in the report. |
myfield_VARIANCE_REPORT: |
Variance of all myfield values in the report. |
The table below summarises all functions available in Instant Report.
Function: |
Description: |
Text Functions: |
|
ToString: |
Converts a number, date, timestamp to string. Parameters: 1. Number or date or timestamp |
Upper: |
Converts & returns a string value in upper case. Parameters: 1. A string value. |
Lower: |
Converts & returns a string value in lower case. Parameters: 1. A string value."; |
Trim: |
Removes white spaces from a string. Parameters: 1. String |
Len: |
Returns the length of a string. Parameters: 1. String |
Left: |
Returns the leftmost characters in a text. Parameters: 1. String, 2. Number of characters from left. |
Right: |
Returns the rightmost characters in a text. Parameters: 1. String, 2. Number of characters from right. |
Concat: |
Returns the result of concatenating (joining) two strings. Parameters: 1. String, 2. String. |
FirstLetter: |
Returns the first letter/initial of a string. Parameters: 1. A string value. |
Number, Math & Currency Functions: |
|
Add: |
Returns the sum of two numbers. Parameters: 1. A number. 2. A number. |
Subtract: |
Returns the result of subtracting two numbers. Parameters: 1. A number. 2. A number. |
Multiply: |
Returns the result of multiplying two numbers. Parameters: 1. Number. 2. Number. |
Divide: |
Returns the result of dividing two numbers. Parameters: 1. Number. 2. Number. |
FormatNumber: |
Formats a number with an input decimal format pattern and returns a string representation. Parameters: 1. A number value to be formatted, 2. A valid decimal pattern string. |
0.00: |
Formats a number to exactly two digits after the decimal separator and returns a string. Parameters: 1. A number to be formatted. |
###,###,###.##: |
Formats a number, rounds to two digits after the decimal point, using a comma for thousand separator. Parameters: 1. A number to be formatted. |
###,###,###.00: |
Formats a number, adds trailing zeros to make exactly two digits after the decimal point, using a comma for thousand separator. Parameters: 1. A number to be formatted. |
###,###,###.##%: |
Formats a number, rounds to two digits after the decimal point, using a comma for thousand separator, and adding a trailing percent symbol. Parameters: 1. A number to be formatted. |
Dollar: |
Formats a number to the Dollar currency format, adds the Dollar character and returns a string. Parameters: 1. A number to be formatted. |
Euro: |
Formats a number to the Euro currency format, adds the Euro character and returns a string. Parameters: 1. A number to be formatted. |
Yen: |
Formats a number to the Yen currency format, adds the Yen character and returns a string. Parameters: 1. A number to be formatted. |
Pound: |
Formats a number to the Pound currency format, adds the Pound (Sterling) character and returns a string. Parameters: 1. A number to be formatted. |
ToInt: |
Converts a decimal number to integer (whole number). Parameters: 1. Number |
Abs: |
Returns the absolute value of a number. Parameters: 1. Number. |
Round: |
Returns the closest number to the argument. Parameters: 1. Number. |
Sqrt: |
Returns the correctly rounded positive square root of a number. Parameters: 1. A double value. |
Sin: |
Returns the trigonometric sine of an angle. Parameters: 1. An angle, in radians. |
Cos: |
Returns the trigonometric cosine of an angle. Parameters: 1. An angle, in radians. |
Tan: |
Returns the trigonometric tangent of an angle. Parameters: 1. An angle, in radians. |
ToDegrees: |
Converts an angle measured in radians to the equivalent angle measured in degrees. Parameters: 1. An angle, in radians. |
ToRadians: |
Converts an angle measured in degrees to the equivalent angle measured in radians. Parameters: 1. An angle, in degrees. |
Log: |
Returns the natural logarithm (base e) of a number. Parameters: 1. A double value. |
Max: |
Returns the greater of two number. Parameters: 1. A double value, 2. A double value. |
Min |
Returns the smaller of two number. Parameters: 1. A double value, 2. A double value. |
Date Functions: |
|
Now: |
Returns the current date/time. |
FormatDate: |
Formats a date/time with an input pattern and returns a string representation. Parameters: 1. A date/time value to be formatted, 2. A valid date/time pattern string. |
dd MMMMM yyyy: |
Formats a date/time with the pattern "dd MMMMM yyyy" and returns a string representation. Parameters: 1. A date/time value to be formatted. |
dd.MM.yy: |
Formats a date/time with the pattern "dd.MM.yy" and returns a string representation. Parameters: 1. A date/time value to be formatted. |
MM/dd/yy: |
Formats a date/time with the pattern "MM/dd/yy" and returns a string representation. Parameters: 1. A date/time value to be formatted" |
yyyy.MM.dd G 'at' hh:mm:ss z: |
Formats a date/time with the pattern "yyyy.MM.dd G 'at' hh:mm:ss z" and returns a string representation. Parameters: 1. A date/time value to be formatted. |
EEE, MMM d, ''yy: |
Formats a date/time with the pattern "EEE, MMM d, ''yy" and returns a string representation. Parameters: 1. A date/time value to be formatted. |
h:mm a: |
Formats a date/time with the pattern "h:mm a" and returns a string representation. Parameters: 1. A date/time value to be formatted. |
H:mm:ss:SSS: |
Formats a date/time with the pattern "H:mm:ss:SSS" and returns a string representation. Parameters: 1. A date/time value to be formatted. |
K:mm a,z: |
Formats a date/time with the pattern "K:mm a,z" and returns a string representation. Parameters: 1. A date/time value to be formatted. |
yyyy.MMMMM.dd GGG hh:mm aaa: |
Formats a date/time with the pattern "yyyy.MMMMM.dd GGG hh:mm aaa" and returns a string representation. Parameters: 1. A date/time value to be formatted. |
Comparison Functions: |
|
EQ: |
Returns true if the first number is equal to the second number, and false otherwise. Parameters: 1) Number, 2) Number. |
LT: |
Returns true if the first number is less than the second number, and false otherwise. Parameters: 1. Number. 2. Number. |
GT: |
Returns true if the first number is greater than the second number, and false otherwise. Parameters: 1. Number. 2. Number. |
LTE: |
Returns true if the first number is less than or equal to the second number, and false otherwise. Parameters: 1. Number. 2. Number. |
GTE: |
Returns true if the first number is greater than or equal to the second number, and false otherwise. Parameters: 1. Number. 2. Number. |
NEQ: |
Returns true if the first number is not equal to the second number, and false otherwise. Parameters: 1. Number. 2. Number. |
StrEQ: |
Returns true if the first string is equal to the second string, and false otherwise. Parameters: 1) String, 2) String. |
Contains: |
Returns true if the first string contains the second string, and false otherwise. Parameters: 1) String, 2) String. |
Before: |
Returns true if the first date occurs before the second date, and false otherwise. Parameters: 1) Date, 2) Date. |
BeforeTS: |
Returns true if the first timestamp occurs before the second timestamp, and false otherwise. Parameters: 1) Timestamp, 2) Timestamp. |
After: |
Returns true if the first date occurs after the second date, and false otherwise. Parameters: 1) Date, 2) Date. |
AfterTS: |
Returns true if the first timestamp occurs after the second timestamp, and false otherwise. Parameters: 1) Timestamp, 2) Timestamp. |