Text Functions

dateformatnoshift takes a date field and converts it into a grouping without appylying any sort of time zone shift:

dateformatnoshift([Field], "yyyy-MM-dd")

would create a grouping of 2022-06-15, while

parsedate([Field], "dd-MM-yyyy")

would create a grouping of 15-06-2022.

directreplace performs text replacement on the specified parameters:

directreplace([Field], "ABC", "zzz")

would replace A312 in an example of Code ABC Test with Code zzz test.

firstvalue returns the first value in the list of parameters containing a value:

firstvalue([Invoice Number], [Credit Note Number])

would return Invoice Number if present, and otherwise retrieve Credit Note Number. This is particularly useful when you're using a first value-style report/

indexof returns the index of a specified character.

indexof([Field], "Z")

would return a zero-offset value of 2 on a row containing a value in Field of XYZ.

json parses a field containing JSON using a JSON Path expression.

json([Field], "$.name")

would return a value of the name field found on the JSON structure in the data.

lastindexof returns the last index of a specified character.

lastindexof([Field], "Z")

would return a zero-offset value of 2 on a row containing a value in Field of Z Z.

lower returns a value in lower case.

lower([Field])

parsedate takes a grouping field and converts it into a date:

parsedate([Field], "yyyy-MM-dd")

would create a date out of a field containing 2022-06-15.

parsedate([Field], "dd-MM-yyyy")

would create a date out of a field containing 15-06-2022.

regex uses regular expression matching to parse text.

regex([Field], "A([0-9])*", 0)

would return A312 in an example of Code A312 Test.

replace performs text replacement using regular expressions:

replace([Field], "A([0-9])*", "zzz")

would replace A312 in an example of Code A312 Test with Code zzz test.

split breaks the selected text value apart by a delimiter and returns the selected part.

split([Field], "|", 1)

on a value of A|B|C would return B.

stringliteral returns the specified text. For example:

stringliteral("")

will simply return an empty cell for you.

stringliteral("Placeholder Text")

will return the value of Placeholder Text.

striphtml removes any HTML tag fields from the value.

striphtml([Field])

on a value of:

XY<br>Z

would return:

XYZ

substring returns the subset of characters between the specified start and end indices.

substring([Field], 5, 10)

returns the characters starting at the 5th letter and ending at the 10th letter of the value in Field.

substring([Field], 5)

returns the characters starting at the 5th letter of the value in Field.

tail returns the last N characters of the specified text value.

tail([Field], 3)

on a value of ABC DEF would return DEF.

trim removes any leading or trailing whitespace.

trim([Field])

on a value of XYZ would return XYZ.

uniqueconcat creates a comma separated list of values. For example, you might have multiple assignees possible per task, but want to see all assignees in a single table cell. To do this, you could use a calculation of:

uniqueconcat([Task ID], [Assignee Name])

For each unique task ID, it will combine the set of matching Assignee Names so that you'll get "Jim, Anna" or "Bob, Mike, Nancy" as values per task.

uniqueconcat creates a list of values separated by the specified delimiter. For example, you might have multiple assignees possible per task, but want to see all assignees in a single table cell separated by a pipe. To do this, you could use a calculation of:

uniqueconcatwithdelimier([Task ID], [Assignee Name], "|")

For each unique task ID, it will combine the set of matching Assignee Names so that you'll get "Jim|Anna" or "Bob|Mike|Nancy" as values per task.

upper returns a value in upper case.

upper([Field])