> ## Documentation Index
> Fetch the complete documentation index at: https://help.dingtalk.io/llms.txt
> Use this file to discover all available pages before exploring further.
# 报表公式
> 本文介绍了如何在报表中使用自定义公式处理原始表单数据，包括逻辑判断类、字符串处理类等公式的用法和示例。
## 1. 使用场景
有时候，原始表单中的数据，我们需要经过一些自定义的处理之后才能展示。
例如：
* 表单中的数据是每个员工填写的，而报表中希望展示每天填写表单的员工人数，这个就需要使用到 COUNT()函数计算之后作为结果来进行展示了
* 用户希望在表单中新增一些自定义的字段，然后通过这些字段关联超链接来做页面跳转，这样的需求也可以使用到这里的自定义公式字段的功能
为了实现上述需求，我们需要先来了解一下，目前宜搭平台对于报表都提供了哪些公式，主要分为下面两大类：
* 逻辑判断类
* 字符串处理类
* 聚合类
* 数组类
## 2. 报表公式视频讲解
## 3. 设置公式入口
**路径**：新建报表 >> 报表设计页 >> 选择数据集 >> 选择显示列字段 >> FX >> 设置对应公式即可
查看以下视频进行设置：
## 4. 逻辑判断类
### CASEWHEN
**用法1**：CASEWHEN(\<条件>,\<值>,\<条件>,\<值>.....), 类同SQL中的case when语法
**示例**：CASEWHEN(\<性别字段>="男","male",\<性别字段>="女","female")，则该字段值为"男"的被替换为"male"，"女"替换为"female"
**用法2**：CASEWHEN(\<条件>,\<值>,\<条件>,\<值>.....\<值>), 类同SQL中的case when语法
**示例**：CASEWHEN(\<性别字段>="男","male","female")，则该字段值为"男"的被替换为"male"，非"男"替换为"female"
## 5. 字符串处理类
### LEN
**用法**：LEN(\[字符串字段])，函数返回为字符串的长度
**示例**：LEN("test")，返回值为4；LEN("测试")，返回值为2，一个中文对应1个英文字符。
### LEFT
**用法**:LEFT(\<度量字段>,\< n >), 函数返回\<字段>值从左边第1个字符开始,长度为n的字符串
**示例**:LEFT(字段1,3) ，字段1的值为"ABCDEF";返回值为"ABC";
### 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".
### STRINGTONUMBER
新增本文处理类报表公式 `STRINGTONUMBER()`，该公式用于将文本类型的数字，转换成数值类型。
该公式目前仅支持报表页面使用，使用公式语法如下：
```javascript theme={"theme":{"light":"github-light","dark":"github-dark"}}
// 字段为文本类型的数字。
STRINGTONUMBER（<字段＞）
```
## 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. 时间处理类
### NOW
**用法**：NOW()，获取当前时间，会精确到秒
**示例**：略
### 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：秒
### DATEADD
**用法：**`DATEADD(<时间字段>,<偏移量>,<时间粒度>)`，在\<时间字段>的基础上增加\*\*\<时间粒度>\*\*的\<偏移量>。
* 时间粒度取值：
* 偏移量是需要增加或减少的具体数字，取值为正则表示增加，取值为负则表示减少。
**示例1：**
假设表单中日期组件的取值为"20200901"，那么`DATEADD(日期组件,10,"DAY") = 20200911`。
**示例2：**
假设表单中日期组件的取值为"20200901"，那么`DATEADD(日期控件,-1,"DAY") = 20200831`。
日期展示格式默认为`yyyyMMdd`，如需需要修改日期的展示格式可以参考[**DATEFORMAT函数**](/zh/yida/form/yrofmw)的示例。
### 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转为日期值
函数声明：
```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
datetime fromunixtime(bigint unixtime)
```
参数说明：
* unixtime：Bigint类型，秒数，unix格式的日期时间值，若输入为string，double，decimal类型会隐式转换为bigint后参与运算。
* 返回值：Datetime类型的日期值，unixtime为NULL时返回NULL。
示例：
```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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"
## 8. 数组类
### ArrayToString
**用法**：ArrayToString(\<数组字段>,\[分隔符可选])，将数组转换为字符串
**示例**：ArrayToString(\<字段1>,\<字段2>) 返回值为"产品部-技术部-业务部";字段1的值为"\[产品部，技术部，业务部]"，字段2的值为"-"；若字段2的值为空，则返回"产品部,技术部,业务部",效果如下所示：
### StringToArray
**用法**：StringToArray(\<字符串>,\[分隔符可选])，将字符串解析为数组
**示例**：StringToArray(\<字段1>,\<字段2>) 返回值为"\[产品部,技术部,业务部]";字段1的值为"产品部-技术部-业务部"，字段2的值为"-"
### ArrayLength
**用法**：ArrayLength(\<数组字段>)，返回数组长度
**示例**：ArrayLength(\<多选等数组字段>)返回值为"3";字段的值为"部门"，其选项为"产品部,技术部,业务部"
### ArrayCat
**用法**：ArrayCat(\<数组字段1>,\<数组字段2>,...)，返回多数组拼接格式
**示例**：ArrayCat(\<多选等数组字段1>,\<多选等数组字段2>,...)返回值为"产品部,技术部,业务部,产品人员,技术人员,业务人员";字段1的值为"产品部,技术部,业务部"，字段2的值为"产品人员,技术人员,业务人员"
## 9. 统计类
### ROUND
**用法**：ROUND(\<字段>,\<精度>), 四舍五入地对字段进行小数位数调整
**示例**：ROUND(\<字段1>,\<精度>) 字段1的值为"12.33333"，精度为2；返回值为"12.33";
### MOD
**用法**：MOD(\<字段>,\<字段>), 取模(取余)运算，返回取模值（或余数）。
说明：若要获得除法的商，可使用/ 四则运算符，比如 6/4 得到 商1余2。
示例: MOD(\<字段1>,\<字段2>) 字段1的值为"6"，字段2的值为"4"，返回值为"2";
### ParseDouble
**用法**：ParseDouble（\<字段>），字符串或整数强转浮点数
**示例**：ParseDouble（＜字段＞），如果字段的值是3或“3”，则结果3.0
### ParseInt
**用法**：Parselnt（＜字段>），字符串或浮点数强转整数
**示例**：Parselnt（＜字段＞），如果字段的值是3.2或“3.2”，则结果3
## 10. 常见问题
### 在报表中 COUNT() 怎么使用呢？
COUNT 是进行计数的函数，请查看以下视频的详细讲解
### 为什么报表提醒：公式解析异常。详细内容:AST语法异常:SQL翻译时遇到未支持的数据库类型：11
目前报表公式字段只能使用当前文档已有的函数公式，若使用文档以外的函数公式就会进行报错，报错提示截图如下：
### CASEWHEN函数中判断组件为空不生效
当前公式内进行数据为空判断时，仅支持`数据 IS NULL`的写法，`数据=""`的写法不支持。