1. Overview
1. Version
Report Server Version | Function change |
|---|---|
| 10.0 | - |
| 10.0.17 | General reports and aggregation reports support the use of cross-sheet formulas for formula judgment. |
2. Function introduction
In this paper, we will introduce the formulas and usage that can be used in the process of setting up task schedule.
Note 1: in task schedule, the section "scheduling cycle - execution condition - formula judgment" supports transferring cells to other formulas as parameters; other places do not support transferring cells to other formulas as parameters.
II. FineReport Template
| Type | formula/parameter/function | Position | Note |
|---|---|---|---|
| Parameter | User global parameters： ${fine_username}${fine_position}${fine_role}${fr_username}${fr_userposition}${fr_authority} | Scheduling object - parameter setting Scheduling object - file name Scheduling cycle - execution condition - formula judgment File processing - platform notification - subject and content File processing - email notification - subject and content File processing - client notification - subject and content File processing SMS notification - SMS parameters | 1) As a parameter, do not add {} in "scheduling object - parameter setting" and "scheduling cycle - execution condition - formula judgment", for example, $fine_roles, In other cases, {} should be added, such as ${fine_ role}. 2) To use the user global parameters, you need to check that in addition to the general results, the results are generated separately according to the users in the default user group 3) Mount to platform, FTP upload attachment and print file use the file under the general result folder, cannot use "user global parameters" |
| dataset parameter | Scheduling object - parameter setting Scheduling object - file name Scheduling cycle - execution condition - formula judgment File processing - platform notification - subject and content File processing - email notification - subject and content File processing - client notification - subject and content File processing S- MS notification - SMS parameters | 1) As a parameter, do not add {} in "scheduling object - parameter setting" and "scheduling period - execution condition - formula judgment", for example: $p; in other cases, add {}, for example: ${P} 2) You can only get the parameters of the template dataset, but not the parameters of server dataset and report function Note: on the right side of "scheduling object - parameter setting", click "set parameter default value". After the parameter list is loaded, the dataset parameters can be referenced in other places of task schedule. | |
| Template paramter | Scheduling object - parameter setting Scheduling object - file name Scheduling cycle - execution condition - formula judgment File processing - platform notification - subject and content File processing - email notification - subject and content File processing - client notification - subject and content File processing S- MS notification - SMS parameters | As a parameter, do not add {} in "scheduling object - parameter setting" and "scheduling period - execution condition - formula judgment", for example, $region "; in other cases, add {}, for example, ${region} Note: on the right side of "scheduling object - parameter setting", click "set parameter default value". After the parameter list is loaded, template parameters can be referenced in other places of task schedule. | |
| Global Parameter | Scheduling object - parameter setting Scheduling object - file name Scheduling cycle - execution condition - formula judgment File processing - platform notification - subject and content File processing - email notification - subject and content File processing - client notification - subject and content File processing S- MS notification - SMS parameters | As a parameter, do not add {} in "scheduling object - parameter setting" and "scheduling period - execution condition - formula judgment", for example, $area "; in other cases, add {}, for example, ${area} Note: on the right side of "scheduling object - parameter setting", click "set parameter default value". After the parameter list is loaded, global parameters can be referenced in other places of task schedule. | |
| custom paramter | Scheduling object - parameter setting Scheduling object - file name Scheduling cycle - execution condition - formula judgment File processing - platform notification - subject and content File processing - email notification - subject and content File processing - client notification - subject and content File processing S- MS notification - SMS parameters | As a parameter, do not add {} in "scheduling object - parameter setting" and "scheduling period - execution condition - formula judgment", for example, $p "; in other cases, add {}, for example, ${p} | |
| Result report cell value | Example： sum(B8,C8)>1000 | Scheduling object - parameter setting Scheduling object - file name Scheduling cycle - execution condition - formula judgment File processing - platform notification - subject and content File processing - email notification - subject and content File processing - client notification - subject and content File processing S- MS notification - SMS parameters | 1) When it is used as a parameter, A2 is directly used in "scheduling cycle - execution condition - formula judgment". In other cases, it cannot be used as a function parameter and can only use the format of ${A2} 2) Only the cell values of the default sheet page are taken, and cross sheet fetching is not allowed 3) If the value of A1 cell can be expanded to 1, 2 and 3: "Scheduling cycle - execution condition - formula judgment" function: in jars before December 27, 2018, using the ${A1} formula gets 1; in jars after December 27, 2018, using the ${A1} formula gets an array of 1, 2, 3 elements Other functions: use the ${A1} formula to get 1 |
| Report formula | Example： ${report0~A1} | Scheduling object - file name Scheduling cycle - execution condition - formula judgment File processing - platform notification - subject and content File processing - email notification - subject and content File processing - client notification - subject and content File processing S- MS notification - SMS parameters | As a parameter, report0 ~ A1 is directly used in “scheduling cycle - execution condition - formula judgment”. In other cases, ${report0 ~ A1} is used; Note: jar after November 8, 2019 supports the use of form formula. |
| report block formula | example： ${block1~A4} | Scheduling object - file name Scheduling cycle - execution condition - formula judgment File processing - platform notification - subject and content File processing - email notification - subject and content File processing - client notification - subject and content File processing S- MS notification - SMS parameters | As a parameter, block1 ~ A4 is directly used in “scheduling cycle - execution condition - formula judgment”. In other cases, ${block1 ~ A4} is used; Note: jar after November 8, 2019 supports the use of form formula. |
| Cross-sheet formula | example: 'sheet1'!A1 | Scheduling cycle-execution conditions-formula judgment | Note:10.0.17 and later report projects support the use of cross-sheet formulas for formula judgment, other places do not support use. Report projects before 10.0.17 do not support cross-sheet formulas. |
| commonly used formula | SUM、COUNT、 AVERAGE、CHAR、DATE、MAX、 MIN、TIME、 RANGE | Scheduling object - parameter setting Scheduling object - file name Scheduling cycle - execution condition - formula judgment File processing - platform notification - subject and content File processing - email notification - subject and content File processing - client notification - subject and content File processing S- MS notification - SMS parameters | As a parameter, ${} should not be added in "scheduling object - parameter setting" and "scheduling period - execution condition - formula judgment". In other cases, ${} should be added |
| Math and trigonometry | ABS、ACOS、ACOSH、ASIN、ASINH、ATAN、ATAN2、AVERAGE、CEILING、COMBIN、COS、SOSH、COUNT、DECIMAL、DEGREES、EVEN、EXP、FACT、FLOOR、INT、LN、LOG、LOG10、MAX、MIN、MOS、ODD、PI、POWER、PRODUCT、PROMOTION、RADIANS、RAND、RANDBETWEEN、ROUND、ROUND5、ROUNDDOWN、ROUNDUP、SIGN、SIN、SINH、SQRT、SUM、SUMPRECISE、SUMSQ、TAN、TANH、TOBINARY、TOHEX、TOOCTAL、TRUNC、WEIGHTEDAVERAGE | Scheduling object - parameter setting Scheduling object - file name Scheduling cycle - execution condition - formula judgment File processing - platform notification - subject and content File processing - email notification - subject and content File processing - client notification - subject and content File processing S- MS notification - SMS parameters | 1) As a parameter, ${} should not be added in "scheduling object - parameter setting" and "scheduling period - execution condition - formula judgment". In other cases, ${} should be added 2) The parameter of WeightedAverage can only be a constant array, not in the form of "A1: A4" |
| Text function | CHAR、CNMONEY、CODE、CONCATENATE、ENDWITH、ENMONEY、ENNUMBER、EXACT、FIND、FORMAT、INDEXOF、LEFT、LEN、LOWER、MIN、NUMTO、PROPER、REGEXP、REPEAT、REPLACE、RIGHT、SPLIT、STARTWITH、SUBSTITUTE、TODOUBLE、TOINTEGER、TRIM、UPPER | Scheduling object - parameter setting Scheduling object - file name Scheduling cycle - execution condition - formula judgment File processing - platform notification - subject and content File processing - email notification - subject and content File processing - client notification - subject and content File processing S- MS notification - SMS parameters | As a parameter, ${} should not be added in "scheduling object - parameter setting" and "scheduling period - execution condition - formula judgment". In other cases, ${} should be added. |
| Date and time function | DATE、DATEDELTA、DATEDIF、DATEINMONTH、DATEINQUARTER、DATEINWEEK、DATEINYEAR、DATESUBDATE、DATETONUMBER、DAY、DAYS360、DAYSOFMONTH、DAYSOFQUARTER、DAYSOFYEAR、DAYVALUE、HOUR、LUNAR、MINUTE、MONTH、MONTHDELTA、NOW、SECOND、TIME、TODATE、TODAY、WEEK、WEEKDATE、WEEKDAY、YEAR、YEARDELTA | Scheduling object - parameter setting Scheduling object - file name Scheduling cycle - execution condition - formula judgment File processing - platform notification - subject and content File processing - email notification - subject and content File processing - client notification - subject and content File processing S- MS notification - SMS parameters | As a parameter, ${} should not be added in "scheduling object - parameter setting" and "scheduling period - execution condition - formula judgment". In other cases, ${} should be added. |
| Logic function | AND、BITNOT、BITOPERATION、IF、OR、REVERSE、SWITCH | Scheduling object - parameter setting Scheduling object - file name Scheduling cycle - execution condition - formula judgment File processing - platform notification - subject and content File processing - email notification - subject and content File processing - client notification - subject and content File processing S- MS notification - SMS parameters | As a parameter, ${} should not be added in "scheduling object - parameter setting" and "scheduling period - execution condition - formula judgment". In other cases, ${} should be added. |
| Array function | ADD2ARRAY、ARRAY、GREPARRAY、INARRAY、INDEXOFARRAY、JOINARRAY、MAPARRAY、RANGE、REMOVEARRAY、REVERSEARRAY、SLICEARRAY、SORTARRAY、UNIQUEARRAY | Scheduling object - parameter setting Scheduling object - file name Scheduling cycle - execution condition - formula judgment File processing - platform notification - subject and content File processing - email notification - subject and content File processing - client notification - subject and content File processing S- MS notification - SMS parameters | As a parameter, ${} should not be added in "scheduling object - parameter setting" and "scheduling period - execution condition - formula judgment". In other cases, ${} should be added作 |
| reort function | COL、DECODE、ENCODE、I18N、ROW、COLCOUNT、COLNAME、FIELDS、MAP、RECORDS、ROWCOUNT、SQL、TABLEDATAFIELDS、TABLEDATAS、TABLES、VALUE | Scheduling object - parameter setting Scheduling object - file name Scheduling cycle - execution condition - formula judgment File processing - platform notification - subject and content File processing - email notification - subject and content File processing - client notification - subject and content File processing S- MS notification - SMS parameters | 1) COL and ROW are written in cells. Although they will not report errors in task schedule, they are meaningless. The return value is constant 0. COL() returns the column number of the current cell. For example, if the current cell is A5, write in A5: = col(), return 1; if the current cell is C6, write in A5: = col(), return 3 2) The parameters of COLCOUNT、COLNAME、MAP、ROWCOUNT、TABLEDATAFIELDS、VALUEfunctions include datasets. In task schedule, only global datasets can be used, but template data sets cannot be obtained. |
| other function | WEBIMAGE、CLASS、CORREL、EVAL、INDEX、ISNULL、LET、MEDIAN、NVL、QUERY、RANK、SEQ、UUID、LUNAR | Scheduling object - parameter setting Scheduling object - file name Scheduling cycle - execution condition - formula judgment File processing - platform notification - subject and content File processing - email notification - subject and content File processing - client notification - subject and content File processing S- MS notification - SMS parameters | WEBIMAGEused to show the image on website， return the format in tast schedule similar tocom.fr.file.WebImage@23dd10da |
| Hierarchical coordinate function | CIRCULAR、CROSSLAYERTOTAL、LAYERTOTAL、MOM、PROPORTION、SORT | Scheduling object - parameter setting Scheduling object - file name Scheduling cycle - execution condition - formula judgment File processing - platform notification - subject and content File processing - email notification - subject and content File processing - client notification - subject and content File processing S- MS notification - SMS parameters | -- |
| Custom function | REPORTCHECK、UBM、SUBSECTION、CELLSUM | Scheduling object - parameter setting Scheduling object - file name Scheduling cycle - execution condition - formula judgment File processing - platform notification - subject and content File processing - email notification - subject and content File processing - client notification - subject and content File processing S- MS notification - SMS parameters | 1) Reportcheck will not report an error, but it will not take effect 2) Cellsum has a return value, but it is meaningless |
III. No scheduling object
| Type | formula/paramter/function | position | note |
|---|---|---|---|
| Parameter | 用户全局参数： ${fine_username} ${fine_position} ${fine_role} ${fr_username} ${fr_userposition} ${fr_authority} | cannot be used | cannot be used |
| Commonly used function | SUM、COUNT、AVERAGE、CHAR、DATE、MAX、MIN、TIME、RANGE | Scheduling cycle - execution condition - formula judgment File processing - platform notification - subject and content File processing - email notification - subject and content File processing - client notification - subject and content File processing S- MS notification - SMS parameters | As a parameter, ${} should not be added in "scheduling period - execution condition - formula judgment". In other cases, ${} should be added. |
| Math and trigonometry | ABS、ACOS、ACOSH、ASIN、ASINH、ATAN、ATAN2、AVERAGE、CEILING、COMBIN、COS、SOSH、COUNT、DECIMAL、DEGREES、EVEN、EXP、FACT、FLOOR、INT、LN、LOG、LOG10、MAX、MIN、MOS、ODD、PI、POWER、PRODUCT、PROMOTION、RADIANS、RAND、RANDBETWEEN、ROUND、ROUND5、ROUNDDOWN、ROUNDUP、SIGN、SIN、SINH、SQRT、SUM、SUMPRECISE、SUMSQ、TAN、TANH、TOBINARY、TOHEX、TOOCTAL、TRUNC、WEIGHTEDAVERAGE | Scheduling cycle - execution condition - formula judgment File processing - platform notification - subject and content File processing - email notification - subject and content File processing - client notification - subject and content File processing S- MS notification - SMS parameters | As a parameter, ${} should not be added in "scheduling period - execution condition - formula judgment". In other cases, ${} should be added. |
| Text function | CHAR、CNMONEY、CODE、CONCATENATE、ENDWITH、ENMONEY、ENNUMBER、EXACT、FIND、FORMAT、INDEXOF、LEFT、LEN、LOWER、MIN、NUMTO、PROPER、REGEXP、REPEAT、REPLACE、RIGHT、SPLIT、STARTWITH、SUBSTITUTE、TODOUBLE、TOINTEGER、TRIM、UPPER | Scheduling cycle - execution condition - formula judgment File processing - platform notification - subject and content File processing - email notification - subject and content File processing - client notification - subject and content File processing S- MS notification - SMS parameters | As a parameter, ${} should not be added in "scheduling period - execution condition - formula judgment". In other cases, ${} should be added. |
| Date and time function | DATE、DATEDELTA、DATEDIF、DATEINMONTH、DATEINQUARTER、DATEINWEEK、DATEINYEAR、DATESUBDATE、DATETONUMBER、DAY、DAYS360、DAYSOFMONTH、DAYSOFQUARTER、DAYSOFYEAR、DAYVALUE、HOUR、LUNAR、MINUTE、MONTH、MONTHDELTA、NOW、SECOND、TIME、TODATE、TODAY、WEEK、WEEKDATE、WEEKDAY、YEAR、YEARDELTA | Scheduling cycle - execution condition - formula judgment File processing - platform notification - subject and content File processing - email notification - subject and content File processing - client notification - subject and content File processing S- MS notification - SMS parameters | As a parameter, ${} should not be added in "scheduling period - execution condition - formula judgment". In other cases, ${} should be added. |
| Logic function | AND、BITNOT、BITOPERATION、IF、OR、REVERSE、SWITCH | Scheduling cycle - execution condition - formula judgment File processing - platform notification - subject and content File processing - email notification - subject and content File processing - client notification - subject and content File processing S- MS notification - SMS parameters | As a parameter, ${} should not be added in "scheduling period - execution condition - formula judgment". In other cases, ${} should be added. |
| Array function | ADD2ARRAY、ARRAY、GREPARRAY、INARRAY、INDEXOFARRAY、JOINARRAY、MAPARRAY、RANGE、REMOVEARRAY、REVERSEARRAY、SLICEARRAY、SORTARRAY、UNIQUEARRAY | Scheduling cycle - execution condition - formula judgment File processing - platform notification - subject and content File processing - email notification - subject and content File processing - client notification - subject and content File processing S- MS notification - SMS parameters | As a parameter, ${} should not be added in "scheduling period - execution condition - formula judgment". In other cases, ${} should be added. |
| Report function | COL、DECODE、ENCODE、I18N、ROW、COLCOUNT、COLNAME、FIELDS、MAP、RECORDS、ROWCOUNT、SQL、TABLEDATAFIELDS、TABLEDATAS、TABLES、VALUE | Scheduling cycle - execution condition - formula judgment File processing - platform notification - subject and content File processing - email notification - subject and content File processing - client notification - subject and content File processing S- MS notification - SMS parameters | 1) COL and ROW are written in cells. Although they will not report errors in task schedule, they are meaningless. The return value is constant 0. 2) The parameters of COLCOUNT、COLNAME、MAP、ROWCOUNT、TABLEDATAFIELDS、VALUEfunctions include datasets. In Bi project, the server data set of Bi will be obtained, and in fr project, the global data set of FR will be obtained. |
| Other function | WEBIMAGE、CLASS、CORREL、EVAL、INDEX、ISNULL、LET、MEDIAN、NVL、QUERY、RANK、SEQ、UUID、LUNAR | Scheduling cycle - execution condition - formula judgment File processing - platform notification - subject and content File processing - email notification - subject and content File processing - client notification - subject and content File processing S- MS notification - SMS parameters | 1）Even thoughSEQis executed，but only once, so the number is always 1, which is meaningless. 2) WEBIMAGEused to show the image on website， return the format in tast schedule similar tocom.fr.file.WebImage@23dd10da |
| Custom function | UBM、SUBSECTION | Scheduling cycle - execution condition - formula judgment File processing - platform notification - subject and content File processing - email notification - subject and content File processing - client notification - subject and content File processing S- MS notification - SMS parameters | Functions that take cell values cannot be used in non scheduled objects. |
