CLASS
Overview
Syntax | CLASS(object) | Returns the class to which the object parameter belongs. |
Parameter | object | Object |
Return Value | String | / |
Example
Formula | Result | Note |
CLASS(121) | class java.lang.Integer | / |
CORREL
Overview
Syntax | CORREL(array1,array2) | Calculates the correlation coefficient of two cell ranges with the same length (same as the CORREL function in Excel). |
Parameter 1 | array1 | A range of cell values |
Parameter 2 | array2 | A second range of cell values |
Return Value | Number | The correlation coefficient ranges from -1 to +1. The larger the absolute value of the correlation coefficient, the stronger correlation between the two ranges. |
Notes
The number of data points in array1 and array2 must be the same.
If an array or reference parameter contains text, logical values, or empty cells, those values are ignored. However, cells with zero values are included.
Negative values in the function calculation results indicate negative correlation.
Example
Formula | Result | Note |
CORREL([1,2,3],[2,4,6]) | 1 | / |
EVAL
Overview
Syntax | EVAL(exp) | Returns the results of an expression. |
Parameter | exp | A string expression |
Return Value | Number | / |
Notes
As long as the exp parameter in EVAL can eventually be converted into a string expression (like "sum(2,4)" or "2+7"), it can be calculated.
Example
Formula | Result | Note |
EVAL("2+5") | 7 | / |
EVAL("count(2,3)") | 2 | / |
EVAL("sum"+"(2,3,5)") | 10 | / |
EVAL(IF(true, "sum", "count") + "(1,2,3,4)") | 10 | / |
EVAL(IF(false, "sum", "count") + "(1,2,3,4)") | 4 | / |
INDEX
Overview
Syntax | INDEX(key,val1,val2,...) | Returns the position of key in the sequence composed of val1,val2,.... Returns the number of parameters if key does not exist in the sequence. |
Parameter 1 | exp | key and valn can be any type. |
Parameter 2 | val1,val2,... | |
Return Value | Number | / |
Example
Formula | Result | Note |
INDEX(2,2) | 1 | / |
INDEX(2,1,2) | 2 | / |
INDEX(2,4,5,6) | 4 | / |
INDEX("b","b","o","y") | 1 | / |
ISNULL
Overview
Syntax | ISNULL(object) | Judges whether an object contains no valid data (Null) or is an empty string. |
Parameter | object | Object to be tested Returns true if the object is empty or null; otherwise returns false. |
Return Value | String | / |
Example
Enter the formula ISNULL(A1) and preview the report.
LET
Overview
Syntax | LET(variable name, variable value, variable name, variable value, ..., expression) | Assigns values to named variables inside a formula. |
Parameter 1 | variable name | It must be a valid variable name starting with a letter, which can include letters, numbers, and underscores. |
Parameter 2 | variable value | The number (N) of parameters must be odd. The last variable in LET is an expression and the preceding N-1 (even number) variables are name/value pairs. |
Parameter 3 | expression | Result calculated based on the preceding N-1 parameters that have been assigned values to These variable assignments are only valid within this expression. |
Return Value | Number | / |
Example
Formula | Result | Note |
LET(a, 5,b, 6, a+b) | 11 | / |
MEDIAN
MEDIAN(array1): Returns the median of the given numbers (same as the MEDIAN function in Excel).
1. If the number of data elements is odd, MEDIAN takes the value of the middle element.
Example | Result |
MEDIAN([1,2,3]) | 2 |
2. If the number of data elements is even, MEDIAN calculates the arithmetic mean of the two middle values.
Example | Result |
MEDIAN([1,2,3,-1]) | 1.5 |
Note:NVL
Overview
Syntax | NVL(value1,value2) | Returns the first non-null value. Returns the value of value1 if value1 is not null; otherwise returns the value of value2. Returns null if both value1 and value2 are null. |
Parameter 1 | value1 | Any number or null |
Parameter 2 | value2 | Any number or null |
Return Value | Number | / |
Notes
The NVL(value1,value2) here is a short-circuit operator. If the first variable is not null, the value of the first variable is returned and the later variables would not be calculated. The first variable can also be null.
Example
Formula | Result |
NVL(12,20) | 12 |
NVL(null,12) | 12 |
RANK
Overview
Syntax | RANK(number,ref,order) | Returns the rank of a number in an array. If the array is sorted, the rank of the number is its position. |
Parameter 1 | number | The number whose rank you want to find, which can be a Boolean (ture=1, false=0). |
Parameter 2 | Ref | An array of, or a reference to, a list of numbers. Non-numeric values in Ref are ignored. Ref can be a Boolean (zhitrue=1, false=0). |
Parameter 3 | Order | Specifies how to rank number. 0 is used for descending order and any non-zero value is used for ascending order. |
Return Value | Number | / |
Notes
RANK gives duplicate numbers the same rank. However, the presence of duplicate numbers affects the ranks of subsequent numbers. For example, in a group of integers sorted in ascending order, if the number 5 appears twice with a rank of 3, then the number 6 would be ranked 5 (no number would be ranked 4).
Example
Example | Result |
If A1:A5 = 6, 4.5, 4.5, 2, 4, the formula is RANK(A1,A1:A5,1) | 5 When the numbers are sorted in ascending order, the rank of 6 in A1 is 5. |
RANK(3,1,2,"go",3,4,1) | 3 "go" is ignored. |
SEQ
SEQ(): Returns a number that indicates the number of times the function has been executed in the entire report execution process.
Example | Result |
SEQ() | In the first execution, the result is 1. |
In the second execution, the result is 2. |
STDEV
STDEV(array1): Calculates the standard deviation of the given numbers (same as the STDEV function in Excel).
Note:Example | Result |
STDEV([1,2,3]) | 1 |
TOIMAGE
Overview
Syntax | TOIMAGE(path) | Displays images in the specified path. |
Parameter | path | Path of an image |
Notes
The image caching function is enabled by default to speed up the generation of reports. If you do not need caching, append the FALSE value after the parameter.
Simulation Calculation is not supported.
Example
Formula | Result | Note |
TOIMAGE("D:/fr.png",false) | Images are displayed. | / |
WEBIMAGE
Overview
Syntax | WEBIMAGE(path) | Displays images on web pages. It can improve the loading speed of web images. |
Parameter | path | Path of the web page where the image is located |
Notes
Simulation Calculation is not supported.
In FineReport 11.0.17 and earlier versions, the image layout does not take effect, and the image previewed in the cell appears stretched.
In FineReport 11.0.18 and later versions, the images returned by the WEBIMAGE function are used, and the image is tiled during preview if Image Layout is set to Default or Tile. You can select other layout methods under Cell Attributes > Style > Custom > Alignment > Image Layout. Layout settings take effect in both preview and export.
If FineReport 11.0.17 or earlier versions is upgraded to V11.0.18 or later versions, the image in the original template would be displayed as tiled.
Example
Formula | Result |
WEBIMAGE('http://www.fanruan.com/images/index2.jpg') | Images are displayed. |
The effect is shown as follows.
UUID
Overview
Syntax | UUID() | Returns a random number. |
Parameter | Empty/32 | Returns a 36-digit/32-digit random number. |
Return Value | String | / |
Example
Only two types of random numbers are supported now.
Formula | Result | Note |
UUID() | Returns a 36-digit random number, such as 1a1f4e18-a38b-4c4b-be2a-eda139810741. | / |
UUID(32) | Returns a 32-digit random number, such as aa3d3db99f274a2486b035ef852b2fcb. | / |
Add the formula in the designer as shown in the following figure.
The result is shown in the following figure.
QUERY
After FineReport 10.0 (2019/03/01), a function for parsing text in JSON format is provided, which can be used to:
Extract values from the built-in parameter $fine_position.
Extract specific values from text in JSON format for display in reports, thus avoiding the use of text functions for cutting in reports.
The first parameter of this function is a string to be processed (a JSON formatted string or an ordinary string).
The second parameter of this function is a query statement (a JavaScript statement) where $ represents the value of the first parameter and can participate in operations.
One
Extract values from the built-in parameter $fine_position.
Example:
Create a new report and enter the formula: =QUERY($fine_position, "$ instanceof Array ? $.map(el => el.jobTitle) : $.jobTitle").
After the template is mounted on the platform, the template displays the position of the login user.
For example, Alice, Director of Technical Support, logs in to the platform and selects the set template. Then her position is displayed.
Two
Extract specific values from text in JSON format for display in reports, thus avoiding the use of text functions for cutting in reports.
Calling Function | Result |
QUERY("{\"job\":\"Driver\",\"age\":45}", "$.age") | 45 |
QUERY("{\"job\":\"Driver\",\"age\":45}", "function() {return $.age > 50 ? 100 : 0}") | 0 |
QUERY("[\"a\",\"b\"]", "$") | ["a","b"] |
QUERY("[\"a\",\"b\"]", "$[1]") | "b" |
QUERY("{\"value\":20.5,\"status\":true}", "$.status") | true |
QUERY("{\"value\":20.5,\"status\":true}", "$.value") | 20.5 |
QUERY("hello", "$") | "hello" |
QUERY("hello", "$.slice(0, 3)") | "hel" |
QUERY("hello", "$ + \" world\"") | "hello world" |
Previous：Report Function
