Report Formulas - DingTalk Help Center

1. Use Cases

Sometimes raw form data must be processed with custom logic before it can be displayed. For example:

Form data is submitted by individual employees, but the report needs to show how many employees submit the form each day. This requires the COUNT() function to calculate the result before display.
Users want to add custom fields to a form and link them to hyperlinks for page navigation. This scenario also benefits from custom formula fields.

To meet these needs, first review the formulas that YiDA provides for reports. They fall into the following categories:

Logical
String processing
Aggregate
Array

3. Where to Set Formulas

Path: Create report >> Report design page >> Select dataset >> Select display column field >> FX >> Configure the formula Watch the following video to see how to configure it:

4. Logical

CASEWHEN

Usage 1: CASEWHEN(<condition>,<value>,<condition>,<value>…), equivalent to the CASE WHEN syntax in SQL. Example: CASEWHEN(<Gender field>=“Male”,“male”,<Gender field>=“Female”,“female”). Values of “Male” are replaced with “male”, and values of “Female” are replaced with “female”. Usage 2: CASEWHEN(<condition>,<value>,<condition>,<value>…<value>), equivalent to the CASE WHEN syntax in SQL. Example: CASEWHEN(<Gender field>=“Male”,“male”,“female”). Values of “Male” are replaced with “male”, and any other value is replaced with “female”.

5. String Processing

LEN

Usage: LEN([String field]). Returns the length of the string. Example: LEN(“test”) returns 4. LEN(“测试”) returns 2, since each Chinese character counts as one character, just like an English character.

LEFT

Usage: LEFT(<Measure field>,< n >). Returns a string of length n starting from the first character on the left of the <field> value. Example: LEFT(Field1,3). If Field1 is “ABCDEF”, the return value is “ABC”.

RIGHT

Usage: RIGHT(<Measure field>,< n >). Returns a string of length n counted from the right of the <field> value. Example: RIGHT(Field1,3) returns “DEF” when Field1 is “ABCDEF”.

MID

Usage: MID(<Measure field>, < start >, < n >). Returns a string of length n starting from the start-th character on the left of the <field> value. Example: MID(Field1, 3, 3) returns “CDE” when Field1 is “ABCDEF”.

POS

Usage: POS(<Measure field>,<Substring>). Returns the position of <substring> in <text>. Returns 0 if there is no match. Example: POS(Field1,“DE”) returns 4 when Field1 is “ABCDEF”.

UPPER

Usage: UPPER(<Measure field>). Converts the <field> value to uppercase. Example: UPPER(Field1) returns “ABCDEF” when Field1 is “AbcdeF”.

LOWER

Usage: LOWER(<Measure field>). Converts the <field> value to lowercase. Example: LOWER(Field1) returns “abcdef” when Field1 is “AbcdeF”.

VAL

Usage: VAL(<Measure field>). Converts the <field> value from text to numerical type. Example: VAL(Field1) returns 123 when Field1 is “123”.

LEFTTRIM

Usage: LEFTTRIM(<Measure field>). Removes leading spaces from the <field> value. Example: LEFTTRIM(Field1) returns “ABC” when Field1 is ” ABC”.

RIGHTTRIM

Usage: RIGHTTRIM(<Measure field>). Removes trailing spaces from the <field> value. Example: RIGTTRIM(Field1) returns “ABC” when Field1 is “ABC ”.

NUMBERTOSTRING

Usage: NUMBERTOSTRING(<Numerical>). Converts numerical data to a string. Example: NUMBERTOSTRING(Field1) returns “12345” when Field1 is “12345 ”.

SPLITPART

Usage: SPLITPART(<String field>, delimiter, substring position). Splits a string by the “delimiter” into N substrings (N equals the number of delimiters plus 1). The substring position is counted from 1 (left to right) or from -1 (right to left). Example: SPLITPART(<Field1>,A,2) returns “BCD” when Field1 is “ABCDABHABC”. Setting the substring position to -5 also returns “BCD”.

CONCAT

Usage: CONCAT(<field>,<field>,…). Joins multiple fields into a single string. The input parameters must be string-type fields. Example: CONCAT(<Field1>,<Field2>) returns “ABCD” when Field1 is “A” and Field2 is “BCD”.

REPLACE

Usage: REPLACE([string], [search string], [replacement string]). Returns the replaced string. Example: REPLACE([Field1], [Field2], [Field3]) returns “a12345efg” when Field1 is “abcdefg”, Field2 is “bcd”, and Field3 is “12345”.

STRINGTONUMBER

The new string-processing report formula STRINGTONUMBER() converts text-type numbers into numerical type. This formula is currently supported only on report pages. Use it as follows:

// The field must be a text-type number.
STRINGTONUMBER(<Field>)

6. Aggregate

To demonstrate how aggregate fields work, the following section uses two practical example datasets. Assume the following two datasets, “Sample dataset 1” and “Sample dataset 2”, are collected through multiple forms. Sample dataset 1 Dataset

BU	Department	Budget type	Expense
xxpt	Collaboration	Team building	500
xxpt	Collaboration	Training	200
xxpt	IT	Team building	700
xxpt	IT	Training	150
aliyun	Feitian 1	Team building	200
aliyun	Feitian 1	Training	100

Spreadsheet

BU	Department	Department total expense
xxpt	Collaboration	700
xxpt	IT	850
aliyun	Feitian 1	300

Sample dataset 2 Dataset

BU	Department	Budget type	Expense
xxpt	Collaboration	Team building	500
xxpt	Collaboration	Training	200
xxpt	Collaboration	Team building	500
xxpt	Collaboration	Training	200
xxpt	IT	Team building	700
xxpt	IT	Training	150
xxpt	eHR	Training	0
xxpt	eHR	Team building	0
aliyun	Feitian 1	Team building	200
aliyun	Feitian 1	Training	100

Spreadsheet

BU	Department	Budget type
xxpt	Collaboration	Team building
xxpt	Collaboration	Training
xxpt	IT	Team building
xxpt	IT	Training
xxpt	eHR	Training
xxpt	eHR	Team building
aliyun	Feitian 1	Team building
aliyun	Feitian 1	Training

SUM

Usage: SUM(<Measure field>,[condition],[excluded field]). Returns the sum of <field> values that match [condition]. The [excluded field] does not participate in the cross-calculation of the sum. Example: Using Sample dataset 1 Department team-building expense = SUM(Expense, Budget type=“Team building”):

Report aggregate functions do not currently support logical operations.

Spreadsheet result

BU	Department	Department total expense	Department team-building expense
xxpt	Collaboration	700	500
xxpt	IT	850	700
aliyun	Feitian 1	300	200

BU team-building expense = SUM(Expense, Budget type=“Team building”, Department): Spreadsheet result

BU	Department	Department total expense	Department team-building expense	BU team-building expense
xxpt	Collaboration	700	500	1200
xxpt	IT	850	700	1200
aliyun	Feitian 1	300	200	200

AVG

Usage: AVG(<Measure field>,[condition],[excluded field]) Description: Returns the average of <field> values that match [condition]. The [excluded field] does not participate in the cross-calculation of the sum. Example: Using Sample dataset 1

Calculate the average team-building expense per BU

Report aggregate functions do not currently support logical operations.

To calculate the average team-building expense, apply the calculation only to records whose budget type is “Team building”. Therefore, select the Expense field as the <measure field> and set Budget type=“Team building” as the [condition]. Because each BU has multiple departments and the calculation should ignore departments, add the Department field as the [excluded field]. Formula: BU average team-building expense = AVG(Expense, Budget type=“Team building”, Department): Spreadsheet result:

BU	Department	Department total expense	Department average team-building expense
xxpt	Collaboration	700	600
xxpt	IT	850	600
aliyun	Feitian 1	300	200

Calculate the overall average team-building expense

To calculate the overall average team-building expense, apply the calculation only to records whose budget type is “Team building”. Therefore, select the Expense field as the <measure field> and set Budget type=“Team building” as the [condition]. Because the calculation sums the team-building expense across all departments regardless of BU or department, add both the Department field and the BU field as [excluded fields]. Formula: Overall average team-building expense = AVG(Expense, Budget type=‘Team building’, Department, BU): Spreadsheet result:

BU	Department	Department total expense	Department average team-building expense	Overall average team-building expense
xxpt	Collaboration	700	600	466.67
xxpt	IT	850	600	466.67
aliyun	Feitian 1	300	200	466.67

MAX

Usage: MAX(<Measure field>,[condition],[excluded field]). Returns the maximum value of <field> under [condition]. The [excluded field] does not participate in the cross-calculation of the sum. Example: Using Sample dataset 1 Department maximum team-building expense = MAX(Expense, Budget type=‘Team building’):

Report aggregate functions do not currently support logical operations.

Spreadsheet result

BU	Department	Department total expense	Department maximum team-building expense
xxpt	Collaboration	700	500
xxpt	IT	850	700
aliyun	Feitian 1	300	200

BU maximum team-building expense = MAX(Expense, Budget type=“Team building”, Department): Spreadsheet result

BU	Department	Department total expense	Department average team-building expense	BU maximum team-building expense
xxpt	Collaboration	700	600	700
xxpt	IT	850	600	700
aliyun	Feitian 1	300	200	200

MIN

Usage: MIN(<Measure field>,[condition],[excluded field]). Returns the minimum value of <field> under [condition]. The [excluded field] does not participate in the cross-calculation of the sum. Example: Using Sample dataset 1 Department minimum team-building expense = MIN(Expense, Budget type=“Team building”):

Report aggregate functions do not currently support logical operations.

Spreadsheet result

BU	Department	Department total expense	Department minimum team-building expense
xxpt	Collaboration	700	500
xxpt	IT	850	700
aliyun	Feitian 1	300	200

BU minimum team-building expense = MIN(Expense, Budget type=“Team building”, Department): Spreadsheet result

BU	Department	Department total expense	Department average team-building expense	BU minimum team-building expense
xxpt	Collaboration	700	600	500
xxpt	IT	850	600	500
aliyun	Feitian 1	300	200	200

COUNTDISTINCT

Usage: COUNTDISTINCT(<Measure field>,[condition],[excluded field]). Returns the count of unique values of <field> under [condition]. The [excluded field] does not participate in the cross-calculation of the sum. Example: Using Sample dataset 2 BU department count = COUNTDISTINCT(Department, Expense>0, Budget type)

Report aggregate functions do not currently support logical operations.

Spreadsheet result

BU	Department	Budget type	BU department count
xxpt	Collaboration	Team building	1
xxpt	Collaboration	Training	1
xxpt	IT	Team building	1
xxpt	IT	Training	1
aliyun	Feitian 1	Team building	1
aliyun	Feitian 1	Training	1

COUNT

Usage: COUNT(<Measure field>,[condition],[excluded field]). Returns the count of <field> values under [condition]. The [excluded field] does not participate in the cross-calculation of the sum. Example: Using Sample dataset 2 Department budget records = COUNT(Department, Expense>0, Budget type)

Report aggregate functions do not currently support logical operations.

Spreadsheet result

BU	Department	Budget type	BU department count
xxpt	Collaboration	Team building	4
xxpt	Collaboration	Training	4
xxpt	IT	Team building	2
xxpt	IT	Training	2
aliyun	Feitian 1	Team building	2
aliyun	Feitian 1	Training	2

7. Date and Time

NOW

Usage: NOW(). Returns the current time, accurate to the second. Example: Omitted.

STRINGTODATE

Usage: STRINGTODATE(<field>,<format>). Converts a string to a date/time value. Example: STRINGTODATE(“1989-09-27”,“yyyy-MM”) returns “198909”. The format for the two functions above follows Java formatting conventions. The supported format types are: yyyy: Year MM: Month dd: Day hh: 12-hour clock (1-12) HH: 24-hour clock (0-23) mm: Minute ss: Second

DATEADD

Usage: DATEADD(<Date field>,<offset>,<time granularity>). Adds an <offset> of the specified <time granularity> to the <date field>.

Time granularity values:
The offset is a specific number to add or subtract. A positive value adds; a negative value subtracts.

Example 1: If the value of the date component in the form is “20200901”, then DATEADD(Date component,10,"DAY") = 20200911. Example 2: If the value of the date component in the form is “20200901”, then DATEADD(Date component,-1,"DAY") = 20200831.

The default date display format is yyyyMMdd. To change the date display format, refer to the example in the DATEFORMAT function.

DATEDIFF

Usage: DATEDIFF(<Time field 1>,<Time field 2>,<time granularity>). Returns the difference between time field 1 and time field 2 at the specified time granularity. Time granularity options:

YEAR Year
MONTH Month
DAY Day
HOUR Hour
MINUTE Minute
SECOND Second

Example: DATEDIFF(Date component 1, Date component 2, “DAY”). Returns the number of days between the two date components. Note: The parameters support only date components. Fixed date values cannot be entered directly in the formula. If the date value in the date component in the form is “2020-02-14” and today is “2020-02-16”, then DATEDIFF(NOW(), Submission date, “DAY”) = 2 DATEDIFF(Submission date, NOW(), “DAY”) = -2

DATEFORMAT

Usage: DATEFORMAT(<Date field>,<time format>). Formats a date into a string. The format for the two functions above follows Java formatting conventions. The supported format types are:

yyyy: Year
MM: Month
dd: Day
hh: 12-hour clock (1-12)
HH: 24-hour clock (0-23)
mm: Minute
ss: Second

Example 1: DATEFORMAT(<Date field>,“yyyy-MM-dd”). If the date field value is August 4, 1989, the return value is 1989-08-04. You can also change the ”-” separator to ”/” or another character. Example 2: DATEFORMAT(<Date field>,“yyyy-MM-dd HH:mm:ss”). If the date field value is 1:30 PM on September 1, 2020, the return value is 2020-09-01 13:30:00. You can also change the separators ”-” and ”:” to ”/”, “年”, “月”, “日”, or other characters.

FROMUNIXTIME

Usage: Converts a numeric Unix time value to a date value. Function declaration:

datetime fromunixtime(bigint unixtime)

Parameter description:

unixtime: Bigint type, in seconds, a Unix-format date/time value. If the input is string, double, or decimal type, it is implicitly converted to bigint before the calculation.
Return value: A Datetime-type date value. Returns NULL when unixtime is NULL.

Example:

fromunixtime(123456789) = 1973-11-30 05:33:09

WEEK

Usage: WEEK([date], [start day (optional)]). Calculates which week of the year the date falls in. If the start day is not specified, the first day of the week defaults to Monday. Start day values 1-7 indicate that Monday through Sunday, respectively, is the first day of the week. Example: WEEK([Date],) returns “20202” when the field value is “2020-01-08”. The start day is optional and defaults to empty.

QUARTER

Usage: QUARTER([date], [fiscal year (optional)]). Calculates which quarter of the year the date falls in. Fiscal year: 1 uses fiscal-year statistics (starting in April); 0 uses the calendar year. Example: QUARTER([Field1], 0) returns “1” when Field1 is “2020-02-02”.

8. Array

ArrayToString

Usage: ArrayToString(<Array field>,[delimiter (optional)]). Converts an array to a string. Example: ArrayToString(<Field1>,<Field2>) returns “Product-Engineering-Business” when Field1 is “[Product, Engineering, Business]” and Field2 is ”-”. If Field2 is empty, it returns “Product,Engineering,Business”. The result is shown below:

StringToArray

Usage: StringToArray(<string>,[delimiter (optional)]). Parses a string into an array. Example: StringToArray(<Field1>,<Field2>) returns “[Product, Engineering, Business]” when Field1 is “Product-Engineering-Business” and Field2 is ”-”.

ArrayLength

Usage: ArrayLength(<Array field>). Returns the length of an array. Example: ArrayLength(<Multiselect or other array field>) returns “3” when the field is “Department” with the options “Product, Engineering, Business”.

ArrayCat

Usage: ArrayCat(<Array field 1>,<Array field 2>,…). Returns the result of concatenating multiple arrays. Example: ArrayCat(<Multiselect or other array field 1>,<Multiselect or other array field 2>,…) returns “Product, Engineering, Business, Product staff, Engineering staff, Business staff” when Field1 is “Product, Engineering, Business” and Field2 is “Product staff, Engineering staff, Business staff”.

9. Statistical

ROUND

Usage: ROUND(<field>,<precision>). Rounds the field to the specified number of decimal places. Example: ROUND(<Field1>,<precision>). If Field1 is “12.33333” and the precision is 2, the return value is “12.33”.

MOD

Usage: MOD(<field>,<field>). Modulo (remainder) operation. Returns the modulo (or remainder) value. Note: To obtain the quotient of division, use the / arithmetic operator. For example, 6/4 returns a quotient of 1 with a remainder of 2. Example: MOD(<Field1>,<Field2>). If Field1 is “6” and Field2 is “4”, the return value is “2”.

ParseDouble

Usage: ParseDouble(<field>). Converts a string or integer to a floating-point number. Example: ParseDouble(<field>). If the field value is 3 or “3”, the result is 3.0.

ParseInt

Usage: ParseInt(<field>). Converts a string or floating-point number to an integer. Example: ParseInt(<field>). If the field value is 3.2 or “3.2”, the result is 3.

10. FAQ

How Do I Use COUNT() in a Report?

COUNT is a function used for counting. Refer to the video below for a detailed explanation.

Why Does the Report Show the Error “Formula Parsing Error. Details: AST Syntax Error: Unsupported Database Type Encountered During SQL Translation: 11”?

Report formula fields can use only a limited set of supported functions. Using unsupported functions triggers an error. See the following error screenshot:

The CASEWHEN Function Does Not Work When Checking Whether a Component Is Empty.

When checking for empty data in a formula, only the Data IS NULL syntax is supported. The Data="" syntax is not supported.

​1. Use Cases

​3. Where to Set Formulas

​4. Logical

​CASEWHEN

​5. String Processing

​LEN

​LEFT

​RIGHT

​MID

​POS

​UPPER

​LOWER

​VAL

​LEFTTRIM

​RIGHTTRIM

​NUMBERTOSTRING

​SPLITPART

​CONCAT

​REPLACE

​STRINGTONUMBER

​6. Aggregate

​SUM

​AVG

​MAX

​MIN

​COUNTDISTINCT

​COUNT

​7. Date and Time

​NOW

​STRINGTODATE

​DATEADD

​DATEDIFF

​DATEFORMAT

​FROMUNIXTIME

​WEEK

​QUARTER

​8. Array

​ArrayToString

​StringToArray

​ArrayLength

​ArrayCat

​9. Statistical

​ROUND

​MOD

​ParseDouble

​ParseInt

​10. FAQ

​How Do I Use COUNT() in a Report?

​Why Does the Report Show the Error “Formula Parsing Error. Details: AST Syntax Error: Unsupported Database Type Encountered During SQL Translation: 11”?

​The CASEWHEN Function Does Not Work When Checking Whether a Component Is Empty.

1. Use Cases

3. Where to Set Formulas

4. Logical

CASEWHEN

5. String Processing

LEN

LEFT

RIGHT

MID

POS

UPPER

LOWER

VAL

LEFTTRIM

RIGHTTRIM

NUMBERTOSTRING

SPLITPART

CONCAT

REPLACE

STRINGTONUMBER

6. Aggregate

SUM

AVG

MAX

MIN

COUNTDISTINCT

COUNT

7. Date and Time

NOW

STRINGTODATE

DATEADD

DATEDIFF

DATEFORMAT

FROMUNIXTIME

WEEK

QUARTER

8. Array

ArrayToString

StringToArray

ArrayLength

ArrayCat

9. Statistical

ROUND

MOD

ParseDouble

ParseInt

10. FAQ

How Do I Use COUNT() in a Report?

Why Does the Report Show the Error “Formula Parsing Error. Details: AST Syntax Error: Unsupported Database Type Encountered During SQL Translation: 11”?

The CASEWHEN Function Does Not Work When Checking Whether a Component Is Empty.