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

Functions must begin with an ‘=’ sign. You can use more than one function in each cell. Additionally, there are logical functions that allow for if/then/else conditional statements. Function types include date, financial, informational, logical, mathematical, statistical, textual and data driven.

Note

Function names are not case sensitive (e.g. aggSum() is the same as AggSum()).

For a complete list of functions, including description, remarks and examples, refer to the List of Functions article.

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 the Formula Editor article. 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, consult with the system administrator.

Examples

@AssetValue@

@AccountCode.Value@

@Employee.DisplayValue@

Note

Parameters are case sensitive (pageNumber is not the same as pagenumber). 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}

Referencing a Cell

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.

Example: [A2]

Note

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 your formulas.

Using Formulas

Formulas can either be entered:

• with the Formula Editor
• by manually typing them into the desired cell
• by manually typing them into the formula bar below the toolbar

Formula Editor

1. Navigate to the Report Designer.
2. Click in the cell in which you want the formula to appear.
3. Click the Formula Editor  icon in the toolbar.
4. Create the desired formula by selecting the desired functions and clicking the button or by drag-and-dropping them into the Formula box.

   

5. Click Okay.

Note

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 article sections.

Manual Entry

1. Navigate to the Report Designer.
2. Enter the formula into a cell by:
   • clicking in the desired cell and entering the formula into the formula bar just below the toolbar, or
     
   • double clicking in the desired cell and directly entering the formula into the cell textbox
     
3. Press the Enter key on the keyboard to save the formula into the cell