• esProc
Tutorial
Function Reference
Code Reference
User Reference
External Library Guide
Data File Tool Manual
DQL Tutorial
Cluster Server Manager Manual
SPL WIN Manual
• YModel
User Reference
JSON-style Parameter Guide
• ReportLite
User Reference
• Official Website

# Cell functions

This section introduces cell functions used for report making by listing their descriptions, syntax, parameters, return values and options, and giving related examples.

## avg()

Description:

Calculate average on an expandable cell or a set expression.

Syntax:

avg(exp)

avg(cellExp,exp)

Parameter:

 exp A cell or an expression on which average is computed, generally an expandable cell or a set expression cellExp A cell or a set of cells, which are used as the current cells to compute expression exp

Return value:

A real number

Option:

 @a Count null elements

Example:

Example 1: avg([A1:B10]) Calculate average of cells between A1 and B10, during which the current cell is used as the base cell to compute values of A1 and B10

Example 2: avg(arg1)  arg1 is an integer array, which means computing average of all elements in it

Example 3: avg(B1{})  Compute average of all cells expanded from B1 inclusive within the expansion area defined by the current master cell

Example 4: avg(B1[`0]{}) Compute average of all cells expanded from B1 inclusive under the root coordinates

Example 5: avg@a(list(2,4,null)) Return 2; return 3 when @a option is absent

Example 6: avg(A1[`0]{},B1+C1) Compute average of sum of B1 and C1 that correspond to each A1

## case()

Description:

Return different values according to results of computing different expressions. Compute expressions from left to right in order, during which if an expression is eligible, the function returns its result and will not compute the rest of the expressions. If no expression can satisfy the condition and the default value expression is present, the function returns the default value; if no expression can satisfy the condition and the default value expression is absent, the function returns null.

Syntax:

case(Exp{,valueExp1:resultExp1{,valueExp2:resultExp2{,...{;defaultExp}}}})

Parameter:

 Exp The expression according to which the judgment is made valueExp(n) The value expression resultExp(n) The returned result expression defaultExp Default value expression; when results of all expressions are not eligible, return the result of computing this expression

Example:

Example 1: case(4,1:"Dept 1",2:"Dept 2",3:"Dept 3";"Admin Dept")

Example 2: case(4,1:"Dept 1",2:"Dept 2",3:"Dept 3")

Result: null

Example 3: case(3,1:"Dept 1",2:"Dept 2",3:"Dept 3";"Admin Dept")

Result:  "Dept 3"

## cell()

Description:

Get a cell on the plane. Here the target cell is a computed one.

Syntax:

cell({cellExp}{,rowOffsetExp{,colOffsetExp}})

Parameter:

 cellExp The cell expression (the current cell when the parameter is omitted) rowOffsetExp The row offset expression relative to cellExp, which is an integer colOffsetExp The column offset expression relative to cellExp, which is an integer

Example:

Example 1: cell(,1,-5)  Get the cell that is offset from the current cell by five columns to the left in the next row

Example 2: cell(C1)  Based on the current cell, get the cell that is offset from the current cell by “the distance between the current cell’s source cell and C1”

Example 3: cell(D1,1,3)  Get the cell that is offset from D1 by three columns to the right in the next row

## col()

Description:

Get ordinal number of the column holding the current cell.

Syntax:

col({cellExp})

Parameter:

 cellExp The cell expression (the current cell when the parameter is omitted)

Return value:

Integer type

Example:

Enter =col() in a cell in column B and the return value is 2. If there is a horizontally expanded cell in column B, the column number changes in order.

## count()

Description:

Perform count on an expandable cell or set expression.

Syntax:

count(expression)

Parameter:

 expression A cell or expression to be counted; generally, it is an expandable cell or a set expression

Return value:

Integer type

Option:

 @a Count null elements @d Perform count on data from which duplicates are removed

Example:

Example 1: count([A1:B10])  Take the current cell as the base cell to count cells between A1 and B10, not counting cells whose values are empty

Example 2: count([A1[1]:B10[3]])  Count cells between the 1st cell expanded from A1 and the 3rd cell expanded from B1, not counting cells whose values are empty

Example 3: count(arg1) arg1 is a parameter array; count data in arg1

Example 4: count(B1{}) Count cells expanded from B1 inclusive within the expansion area defined by the current master cell

Example 5: count(B1[`0]{})  Count cells expanded from B1 inclusive under the root coordinates

Example 6: count@a(list(3,4,null))  Return 3; return 2 if @a option is absent

Example 7: count@d(list(3,4,3))  Return 2; return 3 if @d option is absent

## disp()

Description:

Get display value in a cell.

Syntax:

disp(cell)

Parameter:

 cell A cell

Return value:

String type

Example:

Example 1: if(like(disp(A1), "中国*"),true,false)

Return true if the display value in A1 contains "中国"; otherwise return false

## ds()

Description:

Get a data set object according to its name. The function is used to change the data set object in an expression. It is flexible but has low performance; make sure you think twice before using it.

Syntax:

ds( stringExp )

Parameter:

 stringExp The data set name

Return value:

Data set object

Note:

The return value is an element of the expression, and cannot be used as the cell value before being computed again

Example:

Example 1: ds("ds1").select(#0)  Get a set of row numbers in data set ds1

## eval()

Description:

Dynamically parse and compute an expression.

Syntax:

eval( StringExp )

eval( StringExp, SubRptExp )

eval( StringExp, DataSetExp )

Parameter:

 StringExp A to-be-computed expression string SubRptExp An embedded subreport object, generally a cell containing a subreport DataSetExp A data set object, usually the ds() function

Return value:

Result value of the expression, whose data type is determined by the expression

Example:

Example 1: eval( "1+5" )

Return 6

Example 2: eval("B2+10", A1)

A1 is an embedded subreport, meaning that the expression computes B2+10 in A1’s subreport

Example 3: eval("salary+100", ds("ds1"))

Compute salary+100 on data set ds1

Description:

Read content of a file as a string or byte[].

Syntax:

Parameter:

 fileName A file name charset Encoding format

Return value:

A string or byte[]

Option:

 @b Read a file as byte[]

Example:

Example 1: fread@b("C:/R50.png")  Read content of the file as byte[] and return it

Example 2: fread("D:/report.xml")  Read content of the file as a string and return it

Example 3: fread("D:/c.txt","UTF-8")  Specify encoding format as "UTF-8"

## graph()

Description:

Perform computation to generate a statistical graph.

Syntax:

graph({graphTypeExp})

Parameter:

 graphTypeExp Graph type expression, whose result is an integer type. Below are values corresponding to graph types: 1: Area graph 2: Bar graph 3: 3D bar graph 4: 3D clustered bar graph 5: Stacked bar graph 6: 3D stacked bar graph 7: Column graph 8: 3D column graph 9: 3D clustered column graph 10: Stacked column graph 11: 3D stacked column graph 12: Line graph 13: Pie graph 14: Scatter graph 15: 3D area graph 16: 3D line graph 17: 3D pie graph 18: Sequence graph 19: Time graph 20: Dual-axis line graph 21: Dual-axis column graph 22: Radar graph 23: Gantt graph 24: Dashboard 25: Milestone

Return value:

A statistical graph

Example:

graph(2)  Compute and generate a bar graph

## if()

Description:

Return different values according to results of computing different Boolean expressions. Compute expressions from left to right in order, during which if an expression is eligible, the function returns its result and will not compute the rest of the expressions. If no Boolean expression returns true and the default value expression is present, the function returns the default value; if no boolean expression returns true and the default value expression is absent, the function returns null.

Syntax:

if(a,b,c)

if(x1:y1,…,xk:yk;y)

Parameter:

 a A boolean expression b A value expression; the function returns result of computing value expression b when Boolean expression a returns true c A value expression; the function returns result of computing value expression c when Boolean expression a returns false xk A boolean expression yk A value expression; the function returns result of computing value expression yi when corresponding expression xi returns true y Default value expression; the function returns result of computing this expression when all Boolean expressions xi return false

Return value:

Indefinite data type, which is determined by the result of computing a value expression; return null if all Boolean expressions return false and there isn’t the default value expression

Example:

Example 1: if(value()>1,"true","false")

Return true if the current cell value is greater than 1; otherwise return false

Example 2: if(value()>90:"Excellent",value()>80:"Good",value()>60:"Passed";"Failed")

Return “Excellent” if the current cell value is greater than 90; return “Good” if it is greater than 80; return “Passed” if it is greater than 60; otherwise return “Failed”

Example 3: if(A4>90:"Excellent",A4>80:"Good",A4>60:"Passed")

Return “Excellent” when A4 is greater than 90; return “Good” when it is greater than 80; return “Passed” when it is greater than 60; otherwise return null

## ifn()

Description:

Judge whether value of the first expression is null, and return the specified value if it is and return the expression value if it isn’t.

Syntax:

ifn( valueExp1, valueExp2 )

Parameter:

 valueExp1 A to-be-computed expression, whose value will be returned when the value isn’t null valueExp2 A to-be-computed expression, whose value will be returned when result of valueExp1 is null

Return value:

Result value of valueExp1 or valueExp2

Example:

Example 1: ifn(A1,"")  Return an empty string if A1 is null; otherwise return A1

Example 2: ifn(value(),0)  Return 0 if the current cell value is null; otherwise return the current cell value

Description:

Get left master cell of the specified cell.

Syntax:

Parameter:

 Cell The specified cell, which is the current cell when this parameter is absent level The level of left master cell; 0 represents the current master cell and add 1 for each one level up; default is 0

Return value:

Cell value

Example:

Example 1: lefthead()  Get left master cell of the current cell

Example 2: lefthead(B3,1)  Get left master cell of B3’s left master cell

## list()

Description:

Get a set of enumerated data.

Syntax:

list(valueExp1{,valueExp2{,valueExp3{,……}}})

Parameter:

 valueExp(n) A constant/expression, or a set expression

Return value:

A set of enumerated data

Option:

 @m Generate a recursive union and expand a list in the function

Example:

Example 1: list(1,3,5,7,9)  {1,3,5,7,9}

Example 2: list("abc","def","ghj")  {"abc","def","ghj"}

Example 3: list@m(arg1,to(1,3),"a","b","c")  Suppose arg1 is an integer array whose value is 7,8,9, then the result value of this expression is {7,8,9,1,2,3,"a","b","c"}

## map()

Description:

Display value reference table function. The function lets you find the display value corresponding to value of the current cell from the reference table, and returns null if no corresponding display value can be found.

Syntax:

map(valueListExp, displayListExp)

Parameter:

 valueListExp The list of actual values; can be an expandable cell or an expression whose result is a list displayListExp The list of display values; can be an expandable cell or an expression whose result is a list There is a one-to-one correspondence between the list of actual values and the list of display values correspond in order

Return value:

String type display value corresponding to actual value in the current cell, or null when there isn’t a corresponding actual value

Example:

Example 1: map(to(1,3),list("M","F","Unknown"))

Write map() function in the display value expression; display "M" when value in the current cell is 1; "F" when the value is 2; "Unknown" when the value is 3; and return the cell value itself when value in the current cell is one of the other values

Example 2: map(arg1,arg2)

Write map() function in the display value expression; in the function, arg1 is a parameter array consisting of 1,2,3 and arg2 is a string array consisting of "M","F","Unknown"; same result as Example 1

Example 3: map(list(1,2,3),list("M","F"," Unknown "))

Write map() function in the display value expression and get same result as Example 1

## max()

Description:

Get the maximum value on an expandable cell or a set expression.

Syntax:

max(exp)

max(exp,cellExp)

Parameter:

 exp A cell or expression for which the maximum value will be computed; generally an expandable cell or a set expression cellExp A cell or a set of cells; take the cell specified in cellExp as the current cell to compute expression exp

Return value:

Indefinite data type, which is determined by the result of computing exp

Example:

Example 1: max([A1:A10])  Get the maximum value among cells between A1 and A10

Example 2: max(arg1)  arg1 is the parameter array; get the maximum value in arg1

Example 3: max(B1{})  Get the maximum value among cells expanded from B1 inclusive within the expansion area defined by the current master cell

Example 4: max(B1[`0]{})  Get the maximum value among cells expanded from B1 inclusive under the root coordinates

Example 5: max(B1+C1,A1[`0]{}) Get the maximum sum of B1 and C1 that correspond to every A1

## maxc()

Description:

Return a List of cells containing the maximum value among a cell (or a set of cells). Multiple cells containing the maximum value will all be returned if there are any.

Syntax:

maxc(cellExp1{,cellExp2{,......}})

Parameter:

 cellExp1 A cell/set of cells or an expression of cell/set of cells

Return value:

A List of cells

Example:

Example 1: maxc(A1,A2,A3,A4)  Get a List of cells containing the maximum value between A1 and A4

Example 2: maxc(A1{},A2)  Get a list of cells containing the maximum value among cells expanded from A1 inclusive and A2

## maxwidth()

Description:

Get the maximum length of characters among display strings in non-merge cells in the current column.

(Note: The length of characters whose ASCII code is greater than 255)

Syntax:

maxwidth()

Return value:

Integer type

Example:

Generally, the function is used in the header cell of a column to dynamically control the column width.

Example 1: Write 8*maxwidth() in the cell width property for the column cell header to dynamically adjust the current column width as “maximum length of characters for display cell value *8 pixels”

## min()

Description:

Get the minimum value on an expandable cell or a set expression.

Syntax:

min(exp)

min(exp,cellExp)

Parameter:

 exp A cell or expression for which the maximum value will be computed; generally an expandable cell or a set expression cellExp A cell or a set of cells; take the cell specified in cellExp as the current cell to compute expression exp

Return value:

Indefinite data type, which is determined by the result of computing exp

Example:

Example 1: min([A1:A10])  Get the minimum value among cells between A1 and A10

Example 2: min(arg1)  arg1 is the parameter array; get the minimum value in arg1

Example 3: min(B1{})  Get the minimum value among cells expanded from B1 inclusive within the expansion area defined by the current master cell

Example 4: min(B1[`0]{})  Get the minimum value among cells expanded from B1 inclusive under the root coordinates

Example 5: min(B1+C1, A1[`0]{}) Get the minimum sum of B1 and C1 that correspond to every A1

## minc()

Description:

Return a List of cells containing the minimum value among a cell (or a set of cells). Multiple cells containing the minimum value will all be returned if there are any.

Syntax:

minc(cellExp1{,cellExp2{,......}})

Parameter:

 cellExp1 A cell/set of cells or an expression of cell/set of cells

Return value:

A List of cells

Example:

Example 1: minc(A1,A2,A3,A4)  Get a List of cells containing the minimum value between A1 and A4

Example 2: minc(A1{},A2)  Get a list of cells containing the minimum value among cells expanded from A1 inclusive and A2

## plot()

Description:

Get position of a specified value in a completely divided set.

Syntax:

plot(valueExp, ListExp)

Parameter:

 valueExp A value expression; the value can be a string, a numeric value, a date, a time, etc. ListExp Return an array having same data type as valueExp; members of the array should be ordered in ascending order

Return value:

Integer type

Option:

 @c A value is eligible when it is equivalent to an element of ListExp @z Arrange elements of ListExp in descending order; default is ascending order

Example:

Example 1: plot( 0, list(0,10,100) )  Return 1; the first subset is <0 that does not contain 0, and the second subset is >=0 and <10 where 0 belongs; the subsets are numbered from 0 - 0,1,2,3 in order, so the result is 1

Example 2: plot@c(0, list(0,10,100))  Return 0

Example 3: plot(10, list(0,10,100))   Return 2

Example 4: plot@cz(10,list(100,10,0))  Return 1

Note:

Division refers to the case that a set is divided into multiple subsets. Take the array {0,10,100} as an example. If we divide the set of integers/real numbers into 4 subsets without taking account of the equality case, the subsets are <0, >10 (elements in this subset are sure to be >=0), <100 (elements in this subset are sure to be >=10) and others (elements are >=10) in order;

If we divide the set of integers/real numbers into 4 subsets, taking account of the equality case, the subsets are <=0, <=10 (and >0), <=100 (and >10) and others (elements are >100) in order;

For array {100,10,0}, we divide it into 4 subsets without taking account of the equality case. They are >100, >10 (and <=100), >0 (and <=10) and others (elements are <=0) in order;

If we divide it by taking account of the equality case, the 4 subsets are >=100, >=10 (and <100), >=0 (and <10) and others (elements are <0) in order.

## query()

Description:

Execute the specified SQL statement and return the set of result data, which can only be a single column of data. When there are multiple fields in the SQL statement, return the result value of the first field.

Syntax:

query(sqlStatement{,arg1{,arg2{,arg3{,……}}}}{;dbName})

Parameter:

 sqlStatement A legal SQL statement arg(n) A parameter in the SQL statement, which is a constant or an expression dbName Logical database name; represent the default database when the parameter is null

Return value:

A set of data, whose data type is determined by the first selected field in the SQL statement

Example:

Example 1: query("select NAME from EMPLOYEE where GENDER =?","F";"demo")

Select records meeting condition GENDER ="F" from EMPLOYEE table in demo database and return their NAME field values

Example 2: query("select * from EMPLOYEE where GENDER =? and EID <?","F",11;"demo")

Select records meeting conditions GENDER ="F" and EID <11 from EMPLOYEE table in demo database and return their NAME field values and return values of their first field

## row()

Description:

Get ordinal number of the row holding the current cell.

Syntax:

row({cellExp})

Parameter:

 cellExp The cell expression (the current cell when the parameter is omitted)

Return value:

Integer type

Example:

Enter =row() in a cell in row 3 and return 3; if there is a vertically expandable cell in row 3, the row number changes in order.

## seq()

Description:

Get ordinal number of a specified expandable cell among cells expanded according to same master cell – that is, the ordinal number of the expandable cell when those cells are ordered by row/column number in ascending order after expansion.

Syntax:

seq({cellExp})

Parameter:

 cellExp A cell expression that should return an expandable cell

Return value:

Integer type

Example:

Suppose that A1 is an expandable cell and B1’s master cell is A1. We enter =seq(A1) in B1 and B1’s value change from 1 to the number of cells expanded from A1 inclusive in order.

## sort()

Description:

Sort elements in an array.

Syntax:

sort(arrayExp)

Parameter:

 arrayExp An array expression, such as group function, select function and list function

Return value:

An array, where data types of elements are determined by parameter arrayExp

Option:

 @z Sort in descending order

Example:

Example 1: sort([5,2,3,4])  Return {2,3,4,5}

Example 2: sort@z([5,2,3,4])  Return {5,4,3,2}

Example 3: sort(list(5,2,3,4))  Return {2,3,4,5}

Example 4: sort(ds1.select(EID))  Sort return value by EID in ascending order

Example 5: sort(ds1.group(DEPT)) Sort return value by grouped DEPT in ascending order

## spl()

Description:

Dynamically parse and compute an esProc expression. This is equivalent to executing eval() function in esProc.

Syntax:

spl(StringExp ,{argExp})

Note:

Take an expression string as the result of StringExp, and parse and calculate it. The question mark (?) corresponds to result of computing argExp. If there are multiple question marks, probably there are multiple argExps and they correspond to each other one by one.

If the number of question marks is greater than that of argExps, identify their correspondence from the first argExp.

Usually, we can use the ordinal number to specify parameter for a question mark, such as spl("?2/?1", 3, 6 ), where the first question mark corresponds to the second parameter and the second question mark corresponds to the first parameter. In this example, the function returns 2.

Parameter:

 StringExp A to-be-computed string expression argExp A parameter expression

Keyword:

 ? Represent value of argExp in a StringExp

Return value:

Expression result value, whose data type is determined by the expression

Example:

 A 1 ="1+3" 2 =spl(A1) 4 3 =4 4 =spl("?+5",A3) ? is a keyword representing parameter A3’s value and the function returns 9 5 =spl("replace(?,\"X\",\"*\")","ZXcm") Use replace() function to replace value of the first parameter and return “Z*cm” 6 =spl("(?+1)/?",3,4) Value of the first ? is 3 and that of the second ? is 4, and the function returns 1.0 7 =spl( "(?+?)*?",1, 3 ) Value of the first ? is 1, that of the second ? is 3, and that of the third ? is 1, and the function returns 4 8 =spl("?+?", 3 ) Return 6 because the number of argExps is less that that of the question marks and the parameter is repeatedly used 9 =spl("?2/?1", 3, 6 ) The first ? corresponds to the second parameter and the second ? corresponds to the first parameter, and the function returns 2.0

## sublist()

Description:

Get a subset of a specified set.

Syntax:

sublist(srcList,startPos,count)

Parameter:

 srcList Source set expression startPos Start position; count from 0 count The number of elements to be returned

Return value:

A subset of a set

Example:

Example 1: sublist( list(1,3,5,7,9), 1, 2 )  Return { 3, 5 }

## sum()

Description:

Perform sum on an expandable cell or a set expression.

Syntax:

sum(exp)

sum(exp,cellExp)

Parameter:

 exp A to-be-summed cell or expression, generally an expandable cell or a set expression cellExp A cell or a set of cells; take the cell specified in cellExp as the current cell to compute expression exp

Return value:

A real number or string type data, which is determined by data type of the to-be-summed cell

Example:

Example 1: sum([A1:A10])

Compute sum of values between A1 and A10; the to-be-summed cells are numeric type or string type

Example 2: sum(arg1)

arg1 is a parameter array; compute sum of all elements in arg1

Example 3: sum(B1{})

Compute sum of cells expanded from B1 inclusive within the expansion area defined by the current master cell

Example 4: sum(B1[`0]{})

Compute sum of cells expanded from B1 inclusive under the root coordinates

Example 5: sum(B1+C1,A1[`0]{})

Compute sum of B1 and C1 corresponding to every A1

## throwe()

Description:

Throw a ReportError exception.

Syntax:

throwe({msg})

Parameter:

 msg Exception information string

Example:

throwe("Cell value error")  Throw an exception, which reads “Cell value error”

## to()

Description:

Generate a set of continuous integers.

Syntax:

to(startExp,endExp{,stepExp})

Parameter:

 startExp Expression of start data of the to-be-generated integers endExp Expression of end data of the to-be-generated integers stepExp Expression of step between integers; default is 1

Return value:

An array of continuous integers

Example:

Example 1: to(1,5)  Equivalent to list(1,2,3,4,5); start from 1 and add 1 each time until 5

Example 2: to(1,5,2)  Equivalent to list(1,3,5); start from 1 and add 2 each time until 5

Example 3: to(-5,-10,-2)  Equivalent to list(-5,-7,-9); start from -5 and subtract 2 each time until -9

Example 4: to(-10,-8)  Equivalent to list(-10,-9,-8); start from -10 and add 1 each time until -8

Description:

Get top master cell of the specified cell.

Syntax:

Parameter:

 Cell The specified cell, which is the current cell when the parameter is absent level The level of top master cell; 0 represents the current master cell and add 1 for each one level up; default is 0

Return value:

Cell value

Example:

Example 1: tophead()  Get top master cell of the current cell

Example 2: tophead(E3,1)  Get top master cell of E3’s top master cell

## value()

Description:

Get the current cell’s value.

Syntax:

value()

Return value:

The current cell’s value

## valueat()

Description:

Return an element of an array or a set expression according to the specified position.

Syntax:

valueat(arrayexpsuffixExp)

Parameter:

 arrayexp An array or a set expression suffixExp An integer for specifying ordinal number of the to-be-returned element in the set; count from 0

Return value:

An element of a set, whose data type is determined by type of elements in the set

Example:

Example 1: valueat(to(5,8),2)  Return 7

Example 2: valueat(["a","b","c"],2)  Return "c"

## fexists()

Description:

Check whether a file exists or not, and return true if it does; otherwise return false.

Syntax:

fexists(fileName)

Parameter:

 fileName Name of the to-be-checked file

Return value:

Boolean type

Option:

 @m Search the main directory if the file, when it is an absolute path, cannot be found; directly search the main directory for the file when a relative path is used @c Search the class directory if the file, when it is an absolute path, cannot be found; directly search the class directory for the file when a relative path is used @a Search the application root directory if the file, when it is an absolute path, cannot be found; directly search the application root directory for the file when a relative path is used Default file path is the absolute path

Example:

Example 1: fexists("D:/1.jpg")

Example 2: fexists@m("D:/1.jpg")   false Search D:\ drive for 1.jpg and return true if it is found; otherwise search the main directory, and return true if it is found or return false if it isn’t found

Example 3: fexists@c("1.jpg")  Search class path for 1.jpg, and return true if it is found or return false if it isn’t found

Example 4: fexists@a("1.jpg")  Search application root directory for 1.jpg, and return true if it is found or return false if it isn’t found