报表公式
1. 使用场景
有时候，原始表单中的数据，我们需要经过一些自定义的处理之后才能展示。
例如：
- 表单中的数据是每个员工填写的，而报表中希望展示每天填写表单的员工人数，这个就需要使用到 COUNT()函数计算之后作为结果来进行展示了
- 用户希望在表单中新增一些自定义的字段，然后通过这些字段关联超链接来做页面跳转，这样的需求也可以使用到这里的自定义公式字段的功能
为了实现上述需求，我们需要先来了解一下，目前宜搭平台对于报表都提供了哪些公式，主要分为下面两大类：
- 逻辑判断类
- 字符串处理类
- 聚合类
- 数组类
2. 报表公式视频讲解
3. 设置公式入口
路径：新建报表 >> 报表设计页 >> 选择数据集 >> 选择显示列字段 >> FX >> 设置对应公式即可
查看以下视频进行设置：
4. 逻辑判断类
4.1 条件判断
标识符 | 含义 |
!= | 不等于，用于判断字段不等于某个值 |
4.2 空值判断
标识符 | 含义 |
IS NULL | 为空 |
IS NOT NULL | 不为空 |
4.3 逻辑判断
标识符 | 含义 | 示例 |
AND | 且 | CASEWHEN(组件1="西瓜" AND 组件2="西瓜",1,2) 组件1、组件2同时等于西瓜时返回值1，否则返回值2 |
OR | 或 | CASEWHEN(组件1="西瓜" OR 组件2="桃子",1,2) 组件1和组件2任意一个时返回值1，否则返回值2 |
NOT | 不等于 | CASEWHEN(NOT(组件1="西瓜") ,1,2) 组件1不等于西瓜就返回值1，否则返回值2 |
5. 字符串处理类
LEN
用法：LEN([字符串字段])，函数返回为字符串的长度
示例：LEN("test")，返回值为4；LEN("测试")，返回值为2，一个中文对应1个英文字符。
LEFT
用法: LEFT(<度量字段>,< n >), 函数返回<字段>值从左边第1个字符开始,长度为n的字符串
示例: LEFT(字段1,3) 返回值为"ABC";字段1的值为"ABCDEF";
RIGHT
用法： RIGHT(<度量字段>,< n >), 函数返回<字段>值从右往左数的长度为n的字符串
示例：RIGHT(字段1,3) 返回值为"DEF";字段1的值为"ABCDEF";
MID
用法：MID(<度量字段>, < start >, < n >), 函数返回<字段>值从从左边第start个开始, 长度为n的字符串
示例：MID(字段1, 3, 3) 返回值为"CDE";字段1的值为"ABCDEF";
POS
用法：POS(<度量字段>,<字串>), 函数返回<字串>在<文本>中的位置, 如无匹配则返回0;
示例：POS(字段1,"DE") 返回值为4;字段1的值为"ABCDEF";
UPPER
用法：UPPER(<度量字段>), 将<字段>的值转换成大写;
示例：UPPER(字段1) 返回值为"ABCDEF";字段1的值为"AbcdeF";
LOWER
用法：LOWER(<度量字段>), 将<字段>的值转换成小写;
示例：LOWER(字段1) 返回值为"abcdef";字段1的值为"AbcdeF";
VAL
用法：VAL(<度量字段>), 将<字段>文本类型转换成数值类型
示例：VAL(字段1) 返回值为 123;字段1的值为"123";
LEFTTRIM
用法：LEFTTRIM(<度量字段>), 去掉<字段>左边的空格后返回
示例：LEFTTRIM(字段1) 返回值为"ABC";字段1的值为" ABC";
RIGHTTRIM
用法：RIGHTTRIM(<度量字段>), 去掉<字段>右边的空格后返回
示例：RIGTTRIM(字段1) 返回值为"ABC";字段1的值为"ABC ";
NUMBERTOSTRING
用法：NUMBERTOSTRING(<数值>), 数值类型的数据转换成字符串
示例：NUMBERTOSTRING(字段1) 返回值为"12345";字段1的值为"12345 ";
SPLITPART
用法：SPLITPART(<字符串字段>,分隔字符，子串位置), 公式会将一个字符串按照“分隔字符”将其分为N个子串(N等于分隔字符的数量+1)，子串位置从1（从左往右）开始计数，或者从-1（从右往左）开始计数
示例：SPLITPART(<字段1>,A,2) 返回值为"BCD";字段1的值为"ABCDABHABC";子串位置填写-5时，也可以取到"BCD"
CONCAT
用法：CONCAT(<字段>,<字段>,.....), 公式会将多个字段连接为一个字符串。入参必须为字符串类型的字段
示例：CONCAT(<字段1>,<字段2>) 返回值为"ABCD";字段1的值为"A"，字段2的值为"BCD"
REPLACE
用法：REPLACE([字符串], [搜索字符串], [替换字符串]), 函数返回为替换后的字符串
示例：REPLACE([字段1], [字段2], [字段3])返回值为"a12345efg";字段1的值为"abcdefg",字段2的值为""bcd",字段3的值为"12345".
ROUND
用法：ROUND(<字段>,<精度>), 四舍五入地对字段进行小数位数调整
示例：ROUND(<字段1>,<精度>) 返回值为"12.33";字段1的值为"12.33333"，精度为2
MOD
用法：MOD(<字段>,<字段>), 取模(取余)运算，返回取模值（或余数）。
说明：若要获得除法的商，可使用/ 四则运算符，比如 6/4 得到 商1余2。
示例: MOD(<字段1>,<字段2>) 字段1的值为"6"，字段2的值为"4"，返回值为"2";
CASEWHEN
用法1：CASEWHEN(<条件>,<值>,<条件>,<值>.....), 类同SQL中的case when语法
示例：CASEWHEN(<性别字段>="男","male",<性别字段>="女","female")，则该字段值为"男"的被替换为"male"，"女"替换为"female"
用法2：CASEWHEN(<条件>,<值>,<条件>,<值>.....<值>), 类同SQL中的case when语法
示例：CASEWHEN(<性别字段>="男","male","female")，则该字段值为"男"的被替换为"male"，非"男"替换为"female"
STRINGTODATE
用法：STRINGTODATE(<字段>,<格式>), 字符串转时间
示例：STRINGTODATE("1989-09-27","yyyy-MM")返回值为"198909"
以上两个函数的format格式目前统一为java 中格式化保持一致，支持格式类型如下：
yyyy：年
MM：月
dd：日
hh：1~12小时制(1-12)
HH：24小时制(0-23)
mm：分
ss：秒
TODAY(可视化图表专用)
用法：TODAY()，获取当天日期
示例：略
NOW
用法：NOW()，获取当前时间，会精确到秒
示例：略
DATEADD
用法：DATEADD(<时间字段>,<偏移量>,<时间粒度>)，在<时间字段>的基础上增加<时间粒度>的<偏移量>。
- 时间粒度取值：
- YEAR：年
- MONTH：月
- DAY：日
- HOUR：时
- MINUTE：分
- SECOND：秒
- 偏移量是需要增加或减少的具体数字，取值为正则表示增加，取值为负则表示减少。
示例1：
假设表单中日期组件的取值为"20200901"，那么DATEADD(日期组件,10,"DAY") = 20200911。
示例2：
假设表单中日期组件的取值为"20200901"，那么DATEADD(日期控件,-1,"DAY") = 20200831。
日期展示格式默认为yyyyMMdd，如需需要修改日期的展示格式可以参考DATEFORMAT函数的示例。
DATEDIFF
用法：DATEDIFF(<时间字段1>,<时间字段2>,<时间粒度>),返回的结果为时间段字段1减去时间段字段2在时间粒度上的差值。
时间粒度可选值为：
- YEAR 年
- MONTH 月
- DAY 日
- HOUR 时
- MINUTE 分
- SECOND 秒
示例：DATEDIFF(日期组件1,日期组件2,"DAY")，返回值为2个日期组件相差天数
注意：参数仅支持日期组件，无法在公式输入固定日期值。
假设表单中日期控件的日期值为“2020-02-14”，假设今天为“2020-02-16”，那么使用
DATEDIFF(NOW(),填写日期,"DAY") = 2，
DATEDIFF(填写日期,NOW(),"DAY") = -2
DATEFORMAT
用法：DATEFORMAT(<日期字段>,<时间格式>),将日期格式化成字符串
以上两个函数的format格式目前统一为java 中格式化保持一致，支持格式类型如下：
- yyyy：年
- MM：月
- dd：日
- hh：1~12小时制(1-12)
- HH：24小时制(0-23)
- mm：分
- ss：秒
示例1:
DATEFORMAT(<日期字段>,"yyyy-MM-dd")，若日期字段有个值为1989年8月4日，则返回值为1989-08-04，格式化时，也可以将"-"改为"/"等字符。
示例2:
DATEFORMAT(<日期字段>,"yyyy-MM-dd HH:mm:ss")，若日期字段有个值为2020年9月1日下午1点30分整，则返回值为2020-09-01 13:30:00，格式化时，也可以将连接符号"-"、“:”改为"/"、“年”、“月”、“日”等字符。
FROMUNIXTIME
用法：将数字型的unix时间日期值unixtime转为日期值
函数声明：
datetime fromunixtime(bigint unixtime)
参数说明：
- unixtime：Bigint类型，秒数，unix格式的日期时间值，若输入为string，double，decimal类型会隐式转换为bigint后参与运算。
- 返回值：Datetime类型的日期值，unixtime为NULL时返回NULL。
示例：
fromunixtime(123456789) = 1973-11-30 05:33:09
WEEK
用法：WEEK([日期], [起始日(可以不指定)]), 计算日期年份中的哪一周, 起始日不指定，默认一周的第一天是周一，起始日的值为：1-7 分别表示周一为第一天.... 周日为第一天
示例：WEEK([日期],)返回值为"20202";字段的值为"2020-01-08"，起始日可以不指定，默认为空。
QUARTER
用法：QUARTER([日期], [是否是财年(可以不指定)]), 计算日期年份中的哪一季度, 是否是财年：1表示按照财年统计(4月份), 0表示按照自然年
示例：QUARTER([字段1], 0)返回值为"1";字段1的值为"2020-02-02"
6. 聚合类
为了便于演示聚合类字段的使用效果，这里以一个实际的案例来辅助介绍
首先，假设我们通过多个表单收集到了如下的两个数据集「示例数据集1」和「示例数据集2」
示例数据集1
数据集
BU | 部门 | 预算类型 | 费用 |
xxpt | 协同 | 团建费 | 500 |
xxpt | 协同 | 培训费 | 200 |
xxpt | IT | 团建费 | 700 |
xxpt | IT | 培训费 | 150 |
aliyun | 飞天1 | 团建费 | 200 |
aliyun | 飞天1 | 培训费 | 100 |
表格
BU | 部门 | 部门总费用 |
xxpt | 协同 | 700 |
xxpt | IT | 850 |
aliyun | 飞天1 | 300 |
示例数据集2
数据集
BU | 部门 | 预算类型 | 费用 |
xxpt | 协同 | 团建费 | 500 |
xxpt | 协同 | 培训费 | 200 |
xxpt | 协同 | 团建费 | 500 |
xxpt | 协同 | 培训费 | 200 |
xxpt | IT | 团建费 | 700 |
xxpt | IT | 培训费 | 150 |
xxpt | eHR | 培训费 | 0 |
xxpt | eHR | 团建费 | 0 |
aliyun | 飞天1 | 团建费 | 200 |
aliyun | 飞天1 | 培训费 | 100 |
表格
BU | 部门 | 预算类型 |
xxpt | 协同 | 团建费 |
xxpt | 协同 | 培训费 |
xxpt | IT | 团建费 |
xxpt | IT | 培训费 |
xxpt | eHR | 培训费 |
xxpt | eHR | 团建费 |
aliyun | 飞天1 | 团建费 |
aliyun | 飞天1 | 培训费 |
SUM
用法：SUM (<度量字段>,[条件],[排除字段]) 返回合乎[条件]下, 对<字段>进行求和的结果, [排除字段] 不参与求和的交叉计算;
示例：使用示例数据集1
部门团建费 =SUM (费用, 预算类型="团建费") :
注意：
目前报表类聚合函数暂不支持进行逻辑运算。
表格结果
BU | 部门 | 部门总费用 | 部门团建费 |
xxpt | 协同 | 700 | 500 |
xxpt | IT | 850 | 700 |
aliyun | 飞天1 | 300 | 200 |
BU 团建费= SUM (费用, 预算类型="团建费", 部门) :
表格结果
BU | 部门 | 部门总费用 | 部门团建费 | BU团建费 |
xxpt | 协同 | 700 | 500 | 1200 |
xxpt | IT | 850 | 700 | 1200 |
aliyun | 飞天1 | 300 | 200 | 200 |
AVG
用法：AVG(<度量字段>,[条件],[排除字段])
用法介绍：返回合乎[条件]下, 对<字段>进行求平均的结果, [排除字段] 不参与求和的交叉计算;
示例：使用示例数据集1
- 计算每个BU下的平均团建费
注意：
目前报表类聚合函数暂不支持进行逻辑运算。
如果要计算平均团建费用，需要将预算类型为团建费的数据费用进行计算，因此选择 费用字段 为 <度量字段> ，配置 预算类型="团建费" 为 [条件] ，由于每个BU下有多个部门，而我们不考虑部门情况，只需要计算每个BU下的平均团建费，因此需要将 部门字段 作为 [排除字段] 。
公式如下：BU平均团建费=AVG(费用, 预算类型="团建费",部门):
表格结果：
BU | 部门 | 部门总费用 | 部门平均团建费 |
xxpt | 协同 | 700 | 600 |
xxpt | IT | 850 | 600 |
aliyun | 飞天1 | 300 | 200 |
- 计算总平均团建费用
如果要计算平均团建费用，需要将预算类型为团建费的数据费用进行计算，因此选择 费用字段 为 <度量字段> ，配置 预算类型="团建费" 为 [条件] 。由于计算总平均团建费用，需要将所有部门的团建费加起来计算，不需要考虑BU以及部门情况，因此需要将 部门字段及BU字段 均作为 [排除字段] 。
公式如下：所有部门平均团建费=AVG(费用, 预算类型='团建费',部门,BU) :
表格结果：
BU | 部门 | 部门总费用 | 部门平均团建费 | 所有平均团建费 |
xxpt | 协同 | 700 | 600 | 466.67 |
xxpt | IT | 850 | 600 | 466.67 |
aliyun | 飞天1 | 300 | 200 | 466.67 |
MAX
用法：MAX(<度量字段>,[条件],[排除字段])返回合乎[条件]下, 对<字段>求最大值的结果, [排除字段] 不参与求和的交叉计算;
示例：使用示例数据集1
部门最高团建费=MAX(费用, 预算类型='团建费') :
注意：
目前报表类聚合函数暂不支持进行逻辑运算。
表格结果
BU | 部门 | 部门总费用 | 部门最高团建费 |
xxpt | 协同 | 700 | 500 |
xxpt | IT | 850 | 700 |
aliyun | 飞天1 | 300 | 200 |
BU最高团建费=MAX(费用, 预算类型="团建费", 部门) :
表格结果
BU | 部门 | 部门总费用 | 部门平均团建费 | BU最高团建费 |
xxpt | 协同 | 700 | 600 | 700 |
xxpt | IT | 850 | 600 | 700 |
aliyun | 飞天1 | 300 | 200 | 200 |
MIN
用法：MIN(<度量字段>,[条件],[排除字段])返回合乎[条件]下, 对<字段>求最小值的结果, [排除字段] 不参与求和的交叉计算;
示例：使用示例数据集1
部门最低团建费=MIN(费用, 预算类型="团建费") :
注意：
目前报表类聚合函数暂不支持进行逻辑运算。
表格结果
BU | 部门 | 部门总费用 | 部门最低团建费 |
xxpt | 协同 | 700 | 500 |
xxpt | IT | 850 | 700 |
aliyun | 飞天1 | 300 | 200 |
BU最低团建费=MIN(费用, 预算类型="团建费", 部门) :
表格结果
BU | 部门 | 部门总费用 | 部门平均团建费 | BU最低团建费 |
xxpt | 协同 | 700 | 600 | 500 |
xxpt | IT | 850 | 600 | 500 |
aliyun | 飞天1 | 300 | 200 | 200 |
COUNTDISTINCT
用法：COUNTDISTINCT(<度量字段>,[条件],[排除字段])返回合乎[条件]下, 对<字段>进行去重计数的结果, [排除字段] 不参与求和的交叉计算;
示例：使用示例数据集2
BU 部门数量 = COUNTDISTINCT(部门, 费用>0,预算类型)
注意：
目前报表类聚合函数暂不支持进行逻辑运算。
表格结果
BU | 部门 | 预算类型 | BU部门数量 |
xxpt | 协同 | 团建费 | 1 |
xxpt | 协同 | 培训费 | 1 |
xxpt | IT | 团建费 | 1 |
xxpt | IT | 培训费 | 1 |
aliyun | 飞天1 | 团建费 | 1 |
aliyun | 飞天1 | 培训费 | 1 |
COUNT
用法：COUNT(<度量字段>,[条件],[排除字段])返回合乎[条件]下, 对<字段>进行计数的结果, [排除字段] 不参与求和的交叉计算;
示例：使用示例数据集2
部门预算条数 = COUNT(部门, 费用>0,预算类型)
注意：
目前报表类聚合函数暂不支持进行逻辑运算。
表格结果
BU | 部门 | 预算类型 | BU部门数量 |
xxpt | 协同 | 团建费 | 4 |
xxpt | 协同 | 培训费 | 4 |
xxpt | IT | 团建费 | 2 |
xxpt | IT | 培训费 | 2 |
aliyun | 飞天1 | 团建费 | 2 |
aliyun | 飞天1 | 培训费 | 2 |
7. 数组类
ArrayToString
用法：ArrayToString(<数组字段>,[分隔符可选])，将数组转换为字符串
示例：ArrayToString(<多选等数组字段>,<分隔符>) 返回值为"产品部-技术部-业务部";字段1的值为"部门"，字段2的值为"-"
StringToArray
用法：StringToArray(<数组字段>,[分隔符可选])，将字符串解析为数组
示例：StringToArray(<多选等数组字段>,<分隔符>) 返回值为"产品部,技术部,业务部";字段的值为"产品部-技术部-业务部"，字段2的值为"-"
ArrayLength
用法：ArrayLength(<数组字段>)，返回数组长度
示例：ArrayLength(<多选等数组字段>)返回值为"3";字段的值为"部门"，其选项为"产品部,技术部,业务部"
ArrayCat
用法：ArrayCat(<数组字段1>,<数组字段2>,...)，返回多数组拼接格式
示例：ArrayCat(<多选等数组字段1>,<多选等数组字段2>,...)返回值为"产品部,技术部,业务部,产品人员,技术人员,业务人员";字段1的值为"产品部,技术部,业务部"，字段2的值为"产品人员,技术人员,业务人员"
8. 常见问题
8.1 在报表中 COUNT() 怎么使用呢？
COUNT 是进行计数的函数，请查看以下视频的详细讲解
8.2 为什么报表提醒：公式解析异常。详细内容:AST语法异常:SQL翻译时遇到未支持的数据库类型：11
目前报表公式字段只能使用当前文档已有的函数公式，若使用文档以外的函数公式就会进行报错，报错提示截图如下：
8.3 CASEWHEN函数中判断组件为空不生效
当前公式内进行数据为空判断时，仅支持数据 IS NULL的写法，数据=""的写法不支持。