常见的函数

示例数据：内置数据「CUSTOMER」

新建一张模板，新建数据库查询 ds1：SELECT * FROM CUSTOMER，如下图所示：

分别在单元格中输入如下公式：

=value("ds1",3)

注：由于返回的是数组，因此设置扩展属性为从上向下扩展。

=value("ds1",3,4,"America")

注：由于返回的是数组，因此设置扩展属性为从上向下扩展。

 数据集函数概述

将数据集中的数据列直接拖拽到单元格中使用时，如果想要「条件显示」某些数据列的值，那么可以使用数据集函数。

1）参数面板中不支持使用。

2）不支持模拟计算，模拟计算详情参见：2.4节。

3）决策报表填报事件不支持使用数据集函数。

1）概述

筛选出数据集某列中符合条件的数据，返回结果是一个数组，相同数据不会合并

注：当仅返回一条数据时，数据类型是「字符串」而不是数组。

2）注意事项

筛选条件中的判断既可以使用单等号，也可以使用双等号；

字符串也同时可以使用单引号或者是双引号，对结果均没有影响；

tablename.select() 与 sql() 的区别主要在于 tablename.select() 是从数据集取数，sql() 是从数据库取数，不需要先定义一个数据集。详情参见 SQL 函数

在公式中，以 0 开头的字符串在匹配判断时，例如：ds1.select(colname,ID="003") 结果会返回对应 ID 为 0、03、003…… 的结果。若只希望返回 003 ，可以使用 EXACT 来做匹配判断。例如：ds1.select(colname,exact(ID,"003"))

如果想要进行模糊查询，可通过与 Find 函数 嵌套实现，例如公式：ds1.select(产品名称,FIND("苹果",产品名称)!=0)，如下图所示：

3）示例

例如数据集 ds1 取出内置 FRDemo 数据库中的「S产品」表，分别在单元格中输入以下公式：

返回数据集 ds1 产品名称列中的所有产品名称。

返回数据集 ds1 库存量大于 20 且订购量大于 30 的产品。

返回数据集 ds1 供应商为1或者库存量大于 30 的产品。

1）概述

为布尔值，true 表示升序，false 表示降序。

注：若使用升降序参数，那么必须写筛选条件参数，若没有筛选条件，可以用 true 或者空格代替：例如=ds1.group(销售员,true,false)或者=ds1.group(销售员, ,false)

2）示例

如数据集 ds1 取出内置 FRDemo 数据库中的「销量」表：

返回数据集 ds1 华东地区销售总额超过 200 的销售员，并且相邻数据若相同会进行合并。

返回数据集 ds1 销售员列中的值，并且其中只要数据相同就会进行合并，结果为降序排列，中间的参数为过滤条件，若没有条件，可以用空替代或者使用 true 。

返回数据集 ds1 华东地区的销售员，并且相邻数据若相同会进行合并。

1）概述

表示列号。

如果tablename.select(#0)则输出数据表行号数组（数据条数）

tablename.select(#1)则输出数据库表中第一列的数组数据

2）注意事项

填报场景下 ds1.select(#0) 这个公式如果联动计算有异常，需要检查父子格关系。例如出现下图所示的计算结果时：

remoteEvaluate(String) 方法不支持 tablename.select 函数。

3）示例

例如数据集 ds1 取出内置 FRDemo 数据库中的「销量」表：

返回数据集中的行号

返回数据集中对应列数据

1）概述

表示行号

2）注意事项

报表的图表标题不支持该函数。

决策报表里的图表块不支持该函数。

JavaScript 中不支持该函数。

3）示例

例如数据集 ds1 取出内置 FRDemo 数据库中的「销量」表：

在单元格中输入如下公式：

=if(条件,ds1.group(customerid),ds2.group(customerid))

并设置其扩展属性为从上到下。

公式说明：

条件为真，单元格使用数据集 ds1 中的 customerid 列，否则使用 ds2 中的 customerid 列 。

在单元格输入公式=sum(ds1.select(销量))，返回数据集 ds1 销量列的总和。

sum 求和公式也可以换用其他如 count、max 等。

直接将数据列拖拽到单元格时会自动从上到下扩展。但是使用数据集函数获得数据为一个数组，是显示在一个单元格中的，需要另外给单元格设置扩展属性，数据才会进行扩展。

例如在单元格输入公式=ds1.group(销售员,地区=="华东",true)，设置扩展房方向为「纵向」，如下图所示：

预览报表如下图所示：

 将日期型转化为中文形式

因政府、事业单位的正式文件中的落款日期都是中文的。

如：在 FineReport 中制作填报模板，使用了日期控件，希望在做填报时，将当前日期控件中选择的日期值（FR 中默认是yyyy-MM-dd的日期格式），能够以中文的方式输出显示，然后再入库，但入库的数据还是默认的数值型的 yyyy-MM-dd 的日期格式。

实现效果如下图：

使用NUMTO()函数，通过字符转化方式，将其转化为中文输出即可。

NUMTO(number,bool)：返回 number 的中文表示。

注：其中 bool 用于选择中文表示的方式，当没有 bool 时，采用默认方式（false）显示。

示例：NUMTO(2345,true)返回值为二三四五

示例：NUMTO(2345,false)返回值为二千三百四十五

示例：NUMTO(2345)返回值为二千三百四十五

新建一张普通报表，右击任意单元格，选择控件设置，选择控件为日期控件，格式为 yyyy-MM-dd。如下：

再右击此单元格，选择形态>公式形态，输入公式：REPLACE(NUMTO(YEAR($$$), true), "零", "〇") + "年" + NUMTO(MONTH($$$), false) + "月" + NUMTO(DAY($$$), false) + "日"

注：REPLACE(NUMTO(YEAR($$$),true),"零","〇")，表示将其中输出的中文的零替换为特殊字符 〇。

注：REPLACE 函数的具体使用，可参见文本函数。

保存模板后，选择填报预览。

在日期控件中选择好日期后，点击其他处，显示效果如上图所示。

注：有些日期的中文形式还想含有星期，公式如下所示。

示例：

【REPLACE(NUMTO(YEAR(today()),true),"零","〇")+"年"+format(today(),'MMMMM')+NUMTO(DAY(today()),false)+"日"+format(today(),'EEEEE')】返回值为【二O一八年二月十八日星期天】

点击下载模板：将日期型转化为中文形式.cpt

字符串与数组的相加有两种方式，一是直接使用+号，另外一种是使用字符串连接函数 concatenate，那么这两个有区别吗？

字符串会和每个数组元素分别进行相加。

如公式：="a"+[1,2,3]+"b"，其结果是：a1b,a2b,a3b。

数组会被作为字符串与其他字符串串联。

如公式：=concatenate("a",[1,2,3],"b")，其结果是 a1,2,3b。

SQL函数

数据集函数 能够从数据集中直接进行条件取数，但是有的时候用户希望某个单元格能够直接获取到数据库中的某个值，而不是先要定义一个数据集后，再去取数据。

这时就可以用 SQL 函数。

注：行序号可以省略，这样返回值为数据列。

仅支持查询 sql 语句。

示例数据：内置数据库 FRDemo 中的 STSCORE 数据表。

从内置数据库「FRDemo」里的 STSCORE 表取第三行第三列数据值。

从表 STSCORE 中，可看到第 3 行第 3 列的值为 Alex，如下图所示：

现在若要直接在报表的单元格中显示数据值：Alex，而不是通过先定义一个数据集后，再去取数据的方式，使用 sql() 公式，此时只需在单元格中输入：=sql("FRDemo","SELECT * FROM STSCORE",3,3)即可，预览就可看到 Alex 值，如下图所示：

示例数据：内置数据库 FRDemo 中的 STSCORE 数据表。

需要取出班级为 Class1 的第 3 列所有值。

在单元格中输入：=sql("FRDemo","SELECT * FROM STSCORE where CLASSNO = 'Class1' ",3)，显示效果（班级为 Class1 的第 3 列所有值），如下图所示：

公式说明：

若需要显示某个具体值，如显示 Jonny （即班级为 Class1 的第 3 列第 4 行的值），写法如下：

=sql("FRDemo","SELECT * FROM STSCORE where CLASSNO = 'Class1' ",3,4)

若参数值为变量如为报表参数或者是某个单元格，则写法如下：=sql("FRDemo","SELECT * FROM STSCORE where CLASSNO = '"+$class+"' ",3,4) 或=sql("FRDemo","SELECT * FROM STSCORE where CLASSNO = '"+A1+"' ",3,4)

例如希望过滤控件选择不同班级，显示不同班级下所有的同学的名字。

首先设置模板参数「class」，然后在单元格中输入公式：=sql("FRDemo","SELECT * FROM STSCORE where CLASSNO = '"+$class+"' ",3)，如下图所示：

显示效果如下图所示：

如果传递的参数是获取当前单元格的值，即用 $$$ 作为参数时，字符串类型同样需要拼接单引号，例如：

=sql("FRDemo","SELECT * FROM STSCORE where CLASSNO = '"+$$$+"' ",3,4)

注1：如果参数或者单元格值有多个，那么 SQL 函数的写法如下：=sql("FRDemo","SELECT * FROM STSCORE where CLASSNO in ('"+$class+"') and COURSE  in ('"+$COURSE+"') ",3,4)

注2：class 参数返回值的分隔符需为','，具体请查看下拉复选框参数联动。

在 SQL 中还可以使用 IF 函数进行判断并拼接模板参数，例如希望实现当参数 class 为空时，选择全部学生姓名，可输入公式：

=sql("FRDemo","SELECT * FROM STSCORE where 1=1 "+if(len(class)== 0,"","and CLASSNO = '"+class+"'"),3) 

公式说明：

这里的+是指字符串拼接符号

将前面的 SQL 语句通过+进行拼接

当参数「class」为空，查询语句相当于：SELECT * FROM STSCORE 

当参数「class」不为空时，查询语句相当于：SELECT * FROM STSCORE  WHERE 1=1 and CLASSNO ='"+$class+"' 

当参数「class」为空，相当于：sql("FRDemo","SELECT * FROM STSCORE",3)  

当参数「class」不为空时，查询语句相当于：sql("FRDemo","SELECT * FROM STSCORE  WHERE 1=1 and CLASSNO ='"+$class+"'",3)

如果在 SQL 中参数为模糊查询时，可使用如下公式：

=sql("FRDemo","SELECT * FROM STSCORE where CLASSNO like '%"+$class+"%' ",3,4)

当需要判断条件多的时候，使用 IF 函数 可能会觉得用要对每种情况都进行判断，比较麻烦，那么可以使用 switch 函数与NVL函数结合进行多条件赋值。

例如希望对班级进行设置：当前值是 Class1 则显示一班，如果是 Class2，则显示二班，如果是 Class3，则显示三班，否则则显示四班，如下图所示：

使用 switch 函数与NVL函数结合进行多条件赋值。

新建数据查询 ds1，数据查询语句如下：SELECT * FROM stscore，如下图所示：

1）设置模板样式，如下图所示：

注：插入斜线可参见 插入 2.7 节。

2）设置公式

单击 A2 单元格，右键单击「单元格元素>插入数据列>高级」，或者双击单元格，点击「高级」，在自定义显示的值中填入公式：nvl(switch($$$,'Class1','一班','Class2','二班','Class3','三班'),'四班')，如下图所示：

公式说明：

1）PC端

保存模板，点击分页预览，如下图所示：

注：若希望不分页展示，可以选择「数据分析预览」。

2）移动端

NVL 函数的使用方法：NVL(value1,value2,value3,...)：在所有参数中返回第一个不是 null 的值。

注：6.5 之前的版本 NVL 只支持 2 个参数，现在升级到支持多个参数。

下面以填报的示例来说明 NVL 多参数的用法。

填报应用中，可能会遇到一组单元格中，只需将其中不为空的值保存至数据库的某个字段，如下图所示，对于语文成绩级别这个字段，可能有四个值，入库的时候只保存选择的级别。

在数据库中新建一个表，表名为 C，新建如下数据：

然后，添加其报表数据集 ds1，SQL 语句为：select * from C

如下图所示，拖动字段到对应单元格并做相应合并：

设置 B5、C5、E5、E6、E7、E8 为文本类型控件即可。

对 E5 单元格，设置条件属性：不等于 4 时，公式为$$$!=4，赋新值为空，如下：

对 E6 单元格：设置为不等于 3 时，公式为$$$!=3，赋新值为空；

对 E7 单元格：设置为不等于 2 时，公式为$$$!=2，赋新值为空；

对 E8 单元格：设置为不等于 1 时，公式为$$$!=1，赋新值为空。

为了保证“优秀”等不设置控件的单元格在添加记录时能默认添加，可设置单元格属性表-其他属性>插入行策略>原值，如下图：

在 NUMBERQ 的值中，输入=NVL(E5,E6,E7,E8)，取出第一个不为空的值进行填报，如下：

在设计器中，点击填报预览，效果：

点击增加记录后，点击提交，如下：

提交成功时，效果如下：

刷新页面，可见提交成功，提交值就是第一个不为空的值 3，如下：

点击下载模板：NVL函数.cpt

报表中，若使用到复杂的FR脚本表达式，如：=if (很长很长的公式 > 0，执行语句 1(很长很长的公式)，执行语句 2(很长很长的公式))，首先想到的是：将很长很长的公式先放在一个单元格（如：A1）中，然后在另一个单元格中，输入=if(a1>0, 执行语句1(a1), 执行语句2(a1))。对于这样的很长公式，一般公式中还会有部分内容是一些其他的简单运算，若再使用单元格去求算一下，最终求算最终结果时就得引用多个单元格。这样的赋值方式，不但多占用空间内存使得重复计算，其性能往往也不是很好。据此 FR 已增加 LET 公式，可将其很长的公式直接赋值，且可直接使用此公式求解最终结果（复杂的脚本表达式），还可提高其性能。

LET(变量名,变量值,变量名,变量值,...,表达式)：局部变量赋值函数，参数的个数 N 必须为奇数, 最后一个是表达式，前面是 N-1(偶数)为局部变量赋值对。

变量名：必须是合法的变量名，以字母开头，可包括字母，数字和下划线。

表达式：根据其前面的 N-1 个参数赋值后，需计算的结果表达式，且这些变量赋值只在这个表达式内部有效。

示例：

LET(a, 5,b, 6, a+b)等于 11。

下面根据如上所遇的情况示例介绍，您可根据实际情况，参照示例使用此公式。

1）一个很长的公式为((10+20+30)*MAX(10,20,30)+DATEDIF("2001/2/28","2004/3/20","D"))*COS(0.5)/ROUND(2.15, 1)，若将其赋给 a，需计算 IF(a>1000,(a+200)/a,(a-200)*a)的结果表达式。

2）通常的做法：在任意单元格（如：A1）中，输入((10+20+30)*MAX(10,20,30)+DATEDIF("2001/2/28","2004/3/20","D"))*COS(0.5)/ROUND(2.15, 1)，然后在另一个单元格（如：B1）中，输入=IF(A1>1000,(A1+200)/A1,(A1-200)*A1)。

3）而计算时是将 A1 中的值，对应带入其结果表达式中的。如这边带入的话就是计算=IF((((10+20+30)*MAX(10,20,30)+DATEDIF("2001/2/28","2004/3/20","D"))*COS(0.5)/ROUND(2.15, 1))>1000,((((10+20+30)*MAX(10,20,30)+DATEDIF("2001/2/28","2004/3/20","D"))*COS(0.5)/ROUND(2.15, 1))+200)/(((10+20+30)*MAX(10,20,30)+DATEDIF("2001/2/28","2004/3/20","D"))*COS(0.5)/ROUND(2.15, 1)),((((10+20+30)*MAX(10,20,30)+DATEDIF("2001/2/28","2004/3/20","D"))*COS(0.5)/ROUND(2.15, 1))-200)*(((10+20+30)*MAX(10,20,30)+DATEDIF("2001/2/28","2004/3/20","D"))*COS(0.5)/ROUND(2.15, 1)))

不仅多占用单元格且使得表达式重复计算，其性能往往也不是很好。

4）LET 公式的写法：只需在单元格中直接赋值和写入结果表达式。在一个单元格中，直接输入=LET(a, ((10 + 20 + 30) * MAX(10, 20, 30) + DATEDIF("2001/2/28", "2004/3/20", "D")) * COS(0.5) / ROUND(2.15, 1), IF(a> 1000, (a+ 200) / a, (a - 200) * a))即可执行结果表达式，从而得出最终结果。

对比可看出，使用 LET 公式明显比通常的做法来得简单，且公式中若再有其他简单运算，如：最大值、平均值等等，也可不必再次占用其他单元格然后再引用此类单元格，这边可直接一步到位求算结果（亦可在 LET 中直接写一些常用的计算公式），这样也大大地提高了性能。

在制作报表时，某数据列如收入金额是数字类型，其中数据包含小数，且小数位数不超过 2 位，对该列进行求和（使用公式 sum）等处理时，会发现最终的结果如：123456.409999996，即小数位数超过 2 位。

此时您可能会有疑问，为什么小数位数不是 2 位？若您的报表对有效位数比较敏感，如金额汇总，总金额最多精确到分，即小数位数最多 2 位，该怎么办？

此情况时，可使用 round() 函数对计算后的结果按指定位数四舍五入来解决。

1）概述

返回某个数字按指定位数舍入后的数字。

需要进行舍入的数字

指定的位数，按此位数进行舍入。

如果 num_digits 大于 0，则舍入到指定的小数位。

如果 num_digits 等于 0，则舍入到最接近的整数。

如果 num_digits 小于 0，则在小数点左侧进行舍入。

因浮点数存在精度计算丢失问题，导致计算结果里可能带上 9999、0000 这些，因此加入第三个参数来控制是否需要去除 9999、0000。

false 表示需要过滤 9999、0000 这些数据；true 表示保留，参数为空则默认为 false。

2）注意事项

2020-07-08 及之后版本的 JAR 包才会有第三个参数。

2020-07-08 及之后版本的 JAR 包 number 参数支持字符串。

3）示例

报表中的销量列求和，设计如下：

如上对收入 sum 求和后精度变大了。

遇到这样的情况时，可使用 round 函数对求和结果按 2 位小数位四舍五入，即 B5 单元格中的使用方法。

在制作报表时，我们可能需要根据某一单元格的值对数据集进行检索并返回对应的值。

此情况时，可使用 map() 函数，根据数据集的名字，找到对应的数据集，找到其中索引列的值为 key 所对应的返回值。 map() 函数首先检索模板数据集，再检索服务器数据集。

示例：

MAP(1001,"employee",1,2)返回 employee 数据集，第 1 列中值为 1001 那条记录中第 2 列的值。

MAP(1001,"employee","name","address")返回 employee 数据集，name 列中值为 1001 那条记录中 address 列的值。注：只返回第一个找到的值。

MAP(object,string,int,int)：根据数据集的名字，找到对应的数据集，找到其中索引列的值为 key 所对应的返回值。

object：索引值，需要查询的内容。

string：数据集的名字，定义的数据查询的名字。

int：索引值所在列序号。

int：返回值所在列序号。

注：后两个参数也可以写列名代替。根据数据集的名字,找到对应的数据集,找到其中索引列的值为key所对应的返回值。数据集的查找方式是依次从报表数据集找到服务器数据集。索引列序号与返回值序列号的初始值为1。

新建数据查询 ds1 ，SQL 语句如下SELECT * FROM 供应商，如下图所示：

新建普通报表，分别给 A1~A5 单元格赋值，单击 A2 单元格，右键选择单元格元素，选择插入公式，公式如下：

MAP(A1, "ds1", "公司名称", "供应商ID")返回 ds1 数据集，“公司名称”列中值为 A1 那条记录中“供应商 ID”列的值。

MAP(A1, "ds1", 2, 1)返回 ds1 数据集，第 2 列中值为 A1 那条记录中第1列的值。

MAP("妙生", "ds1", "公司名称", "供应商ID")返回 ds1 数据集，“公司名称”列中值为“妙生”那条记录中“供应商 ID”列的值。

MAP("妙生", "ds1", 2, 1)返回 ds1 数据集，第2列中值为“妙生”那条记录中第1列的值。

1）PC端

保存模板，点击分页预览，如下图所示：

2）移动端

点击下载模板：Map函数.cpt

语法：treelayer(TreeObject, Int, Boolean, String)

定义：返回一个树对象 TreeObject 第 n 层的值，一般为树数据集，或下拉树、视图树等树对象，并且可以设置返回值类型及分隔符。

详细解释：

最上层为 1 ，第二层为 2 ，依此类推，若无则返回最底层

false：返回值类型为数组，默认值

true：返回值类型为字符串

以双引号表示，默认为逗号：","

以一个下拉树控件展示 FRDemo 数据库中的部门层级树为例，来讲解 treelayer 函数的作用。

新建普通报表，新建数据集 ds1，SQL 语句为：select * from department，如下图所示：

新建一个树数据集 Tree1，数据集为 ds1，依赖字段为 did，父标记字段为 fid，如下图所示：

树数据集效果预览如下图所示：

在参数栏添加两个控件：一个下拉树控件和一个文本控件，如下图所示：

下拉树控件的控件名称修改为 tree。勾选 多选和结果返回完整层次路径。

数据字典选择自动构建，依靠树数据集 Tree1 构建，实际值和显示值都选择 department。如下图所示：

文本控件的控件值选择公式：treelayer($tree, true, "\',\'")，如下图所示：

保存模板，点击预览，勾选总部>人力资源部>人力资源文员、总部>市场部>业务员，如下图所示：

根据文本控件值中填入的公式，预览时的返回值和分隔符也会有所不同，详情如下表所示：

人力资源文员

业务员

人力资源部

市场部

人力资源部

市场部

点击下载模板：treelayer函数.cpt

在使用 FineReport 进行可视化展示时，经常会需要对日期数据进行处理，本文介绍几种日期类型数据的处理应用场景。

在使用公式month()或者是day()时，如果月份或日期是一位数，则显示出来的也只有一位数，比如说 1 月 9 号，获取月份时显示的是 1，而不是 01，获取日期时，显示的是 9，而不是 09，如果需要获取到 01 或者是 09，这个该如何通过公式实现呢？

通过公式或者自定义函数实现。

1）使用 RIGHT、CONCATENATE、MONTH 组合函数实现。

例如显示当前时间（2021-08-31）对应的月份和天数且以两位数字显示。

在单元格分别输入：

月份公式：=right(concatenate('0',month(today())),2)

天数公式：=right(concatenate('0',day(today())),2)

公式说明：

concatenate('0',month(today()))

concatenate('0',day(today()))

right(concatenate('0',month(today())),2)

right(concatenate('0',day(today())),2)

效果查看：

2）通过 FORMAT 来格式化字符串。

在单元格分别输入：

月份公式：=FORMAT(MONTH("2021-08-31"), "00")

天数公式：=FORMAT(day("2021-08-31"), "00")

公式说明：

MONTH("2021-08-31")

day("2021-08-31")

返回月份数字：8

返回天数数字：31

FORMAT(MONTH("2021-08-31"), "00")

FORMAT(day("2021-08-31"), "00")

效果查看：

如果觉得公式比较复杂，则可以通过自定义函数实现，代码如下：

自定义函数的详细定义步骤请参照 自定义函数。

有些情况下需要生成特定字符且带「年月日时分秒」这样的字符串，类似 china20170726144516 这样的编号，可以直接利用时间公式和字符串拼接函数来实现。

在单元格中写入公式：="china" + FORMAT(now(), "yyyMMddhhmmss") 结果为：china20180101124516，即字符串“china”后面加当前的「年月日时分秒」。

注：一般编号非必要的情况下，不要全用数字，因为在导入导出到 EXCEL 时数字有可能出错。