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 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.
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
@Parameter.DisplayValue@ respectively. For more information, contact the system administrator.
Parameters are case sensitive (
pageNumberis not the same as
pagenumber). Parameter names may not contain the at symbol (
To use a Data Field as part of a formula, enter its name between curly braces.
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.
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.
- 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.
=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.
- Navigate to the Report Designer.
- Enter the formula into a cell by:
- Press the Enter key on the keyboard to save the formula into the cell