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.
Functions
A function takes zero or more inputs, called arguments, and returns a value based on some calculation or manipulation of those arguments. For example, a Year() function takes a date as an argument and returns only the year portion of the date. Use the Year() function on a report or on a chart to display or work with only the year portion of a date.
There are many categories of functions to choose from, and the system administrator has the ability to add their own Custom Functions to the system to fill a specific need. Functions can be combined together with other functions and with other operators. Nearly all formulas used in this application will use at least one function.
For a complete list of functions, including description, remarks and examples, refer to the List of Functions.
Parameters
Parameters are special variables that can be assigned when logging in to the application, or they can be prompted for when executing a report. There are also several built-in parameters.
To use a parameter in a formula, enter its name between @ signs. Parameters can be used as function arguments or alone in a cell following an = sign.
For a list of parameters and their descriptions, see Formula Editor. The system administrator has the ability to create additional parameters.
A special type of parameter called a dropdown parameter can be created by the system administrator. These parameters have two values: the Value, used by the server for processing and the Display Value that appears in cells and is used in formulas. These distinct values can be accessed with @ParameterName.Value@ and @Parameter.DisplayValue@ respectively. For more information, contact the system administrator.
Examples
@AssetValue@@AccountCode.Value@@Employee.DisplayValue@
Parameters are case sensitive (
pageNumberis not the same aspagenumber). Parameter names may not contain the at symbol (@).
Data Fields
To use a Data Field as part of a formula, enter its name between curly braces.
Example: {Orders.OrdersID}
Cell References
To reference another cell’s value, enter the column name with a capital letter and the row number between square brackets. A cell reference can be used in formulas or alone in a cell following an = sign.
Cell references will update if rows or columns are added or deleted; however dragging a cell will not update cell references. This may cause errors in some formulas.
Example: [A2]
Adding Formulas to a Report
This section covers adding formulas to an Advanced Report. However, formulas may also be used on ExpressView and in Dashboards. Refer to ExpressView: Formula Columns (v2021.1+) and Dashboard Designer (v2019.2+) for more information about using formulas in those Report Design environments.
Formula Editor
- Click in the cell in which you want the formula to appear.
- Click the Formula Editor
icon in the toolbar. - Create the desired formula by sele
- cting the desired functions and clicking the button or by drag-and-dropping them into the Formula box.
- Click Okay.
When nesting functions, begin with the outermost function and add them moving inward.
Example: =TRUNCATE(SQRT(162)) will first find the square root of 162, then remove the numbers after the decimal point returning only the whole number portion. Details of the Truncate and Sqrt functions can be found in their respective topic sections.
