JSONExtract Function
Pick out one or more portions of a JSON input string as a single value, an array of values, a JSON object, or list of items, via a standard JSONPath expression.
Formulas and Functions
String Functions
String functions are commonly used for interpreting and manipulating text fields.
Concatenate
Description
Joins several text strings into one text string.
Remark
The "&" operator can be used instead of Concatenate() to join strings
Example
Concatenate("This ","is ","one ","string!") ...
Custom Function: Year to Date Check
Returns true if the provided date value is within this year to date, otherwise returns false.
What are formulas?
Formulas allow you to do calculations, parse strings, insert images, and much more. Formulas are the composition of functions, parameters, Data Fields, and references to other cells.
Financial Functions
Common methods for monetary calculations such as interest and depreciation.
Db
Description
Returns the depreciation of an asset for a specified period using the fixed-declining balance method. Cost is the initial cost of the asset. Salvage is the value at the end of the depreciation ...
Custom Function: Month Check
Returns true if the provided date value is within a full specified month, or a specified month to date. Returns false otherwise.
Custom Function: Last Year to Date Check
Returns true if the provided date value is within last year to date, otherwise returns false.
Database and Data Type Functions
Used for determining the type of information contained in a cell. Helpful for error and sanity checks.
DataRowCount
Description
Returns the number of rows retrieved from the data source when executing the report.
Example
Suppose report is run to retrieve basic information on ...
Date Functions
Date functions can be used to do calculations and formatting on date and time values.v2019.2.35 v2020.1.18 v2021.1.6 For all of the functions below except for DateCustomFormat() and Time(), passing Null(), DbNull() or any value that resolves to Null() or DbNull(), including null in a database ...
Other Functions
CellValue
Description
Returns the value of the current cell.
Remark
Used in Conditional Formatting.
Example
Suppose a cell of a report displays the price of products.
Ex. CellValue()> 150 returns True if the price of the product is greater than ...
Logical Functions
Logical functions can be used to handle conditional information.
And
Description
Returns TRUE if all its arguments are TRUE; returns FALSE if any argument is FALSE.
Remark
The arguments must evaluate to TRUE or FALSE.
NOTE. The And function can take more than two arguments as ...
List of Functions
All built-in functions are listed here.
Internal Parameters
The following parameters are built into the application and can be used on any report. Their names are reserved and cannot be overridden.
Report Specific Parameters
The following parameters are utilized within reports to gather specific report information.
pageNumber
Description
Display the ...
Formula Editor
The Formula Editor's features guide users through creating formulas.
Use the Search field to search through the functions by name. Or start typing in the Formula field to get a list of functions that match what's entered.
TipSearch for data fields, report cells references, and parameters ...
Formatting Functions
Apply font styling changes (bold, italic, underline) to cell contents.
Bold
Description
Applies bold font weight to the input.
Example
="The second half of " & bold("this sentence is bold.")
The second half of this sentence is bold.
Italic
Description
Applies italic ...
FilterValue and General Format Best Practices
The FilterValue function may take up to three arguments:
Required: the filter’s index in the list of filters
Optional: the sub-index, starting with 1 for filters that contain multiple values (e.g. filters with an Is Between, Is Not Between, Is One Of or Is Not One Of operator). If ...
Aggregate Functions
Aggregate Functions calculate values based on two factors: the input cell, which is usually but not always a data field, and the report section that the function is located, relative to the input. The location of the function determines which, and how many cell values will be sent to the ...
Custom Aggregate Function: AggConcat()
Returns a concatenated string of values separated by a specified delimiter string.
Custom Aggregate Function: AggCountIf()
Returns the count of values of dataToAggregate with a corresponding true condition. Only those records with a true condition are included in the count.
Custom Aggregate Function: AggDistinctAvg()
Returns the average of only unique values in a series of values.
