Report Expressions

Introduction

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).

Expression builder dialog box
Fig.1: A section of the expression builder dialog box

Text Expressions

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:

  1. Select the Function: radio button (See Fig. 1 above) and select the ToString function from the functions drop down list. The ToString function takes one parameter ToString_Param_1. To set this parameter to PAGE_NUMBER, choose PAGE_NUMBER from the fields drop down list and click on the corresponding F-Param button. Then click on the Update button (not shown in Fig.1). The list of Sub-Expressions now gets a new expression named Sub-Exp001, which holds the value ToString($V{PAGE_NUMBER}). We can use sub expressions to build other complex expressions, by making themparameters to other functions.
  2. Next, we use another function Concat to create the full expression. Select the Function: radio button (See Fig. 1 above) and select the Concat function from the functions drop down list. The Concat function takes two parameters Concat_Param_1 and Concat_Param_2. Select Concat_Param_1 from the parameters drop down list. Enter Page ("Page" and space) into the Constant: text field (make sure you choose String from the Constant Type drop down list) and click on the corresponding C-Param button. Then select Concat_Param_2 from the parameters drop down list. Select Sub-Exp001 we created in step 1 and click on the corresponding E-Param button. Click on the Update button. We now have the the full expression we wanted Concat(Page ,ToString($V{PAGE_NUMBER})). We could further use this expression as a sub-expression to create other complex expression.
  3. Finally, click on the Apply button (not shown in Fig.1) to update the Text-Field element with this expression, and click on the OK button to quit.

To create another text expression Price + Price*0.05 to represent the price plus tax, assuming we have a data source field named Price:

  1. Select the Function: radio button and select the Multiply function from the functions drop down list. The Multiply function takes two parameter Multiply_Param_1 and Multiply_Param_2. To set Multiply_Param_1 to Price, choose the Price field from the fields drop down list and click on the corresponding F-Param button. To set Multiply_Param_2 to 0.05, first select Multiply_Param_2 from the parameters drop down list, then enter 0.05 into the Constant: text field (make sure you choose Number (integer & decimal) from the Constant Type drop down list) and click on the corresponding C-Param button. Then click on the Update button. The list of Sub-Expressions now gets a new expression named Sub-Exp001, which holds the value $F{Price} * 0.05.
  2. Next, select the Function: radio button and select the Add function from the functions drop down list. The Add function also takes two parameter Add_Param_1 and Add_Param_2. To set Add_Param_1 to Price, choose the Price field from the fields drop down list and click on the corresponding F-Param button. Then we set Add_Param_2 to Price*0.05. First select Add_Param_2 from the parameters drop down list. Then select Sub-Exp001 we created in step 1 and click on the corresponding E-Param button. Then click on the Update button. We now have the the full expression we wanted Add($F{Price},$F{Price} * 0.05).
  3. Finally, click on the Apply button to update the Text-Field element with this expression, and click on the OK button to quit.
See Functions Reference for a list of all available functions.

Conditional Expressions

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.

    For example, assuming you have two number fields total_cost and revenue, you want to create a conditional expression that will evaluate to true if total_cost is greater than revenue so that the containing Text-Field should display in red or black if false:
  1. Select the Function: radio button and select the GT (greater than) function from the functions drop down list. The GT function takes two parameter GT_Param_1 and GT_Param_2. To set GT_Param_1 to total_cost, choose the total_cost field from the fields drop down list and click on the corresponding F-Param button. To set GT_Param_2 to revenue, choose the revenue field from the fields drop down list and click again on the corresponding F-Param button. Then click on the Update button. The list of Sub-Expressions now gets a new expression named Sub-Exp001, which holds the final value of the conditional expression that we want $F{total_cost} > $F{revenue).
  2. Finally, click on the Apply button to update the Text-Field element with this conditional expression, and click on the OK button to quit.

Fields Reference

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.

Note that in addition to the report level fields above, you also have page, column, and group (if there are any groups) level fields.

Functions Reference

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.