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

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 (aggSum is the same as AggSum).

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

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.

NOTE. Parameters are case sensitive (pageNumber is not the same as pagenumber). Parameter names should not contain the ‘@’ symbol.

For a list of parameters and their descriptions, see this article. Your administrator has the ability to create additional parameters.

Data Fields

To use a Data Field as part of a function, enter the name between curly brackets

Ex. {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

Ex. [A2].

A cell reference can be used in functions or alone in a cell following an ‘=’ sign.

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 in the Formula Editor or manually keyed into cells.

Formula Editor

  • Navigate to the Report Designer.

  • Click in the cell in which you want the formula to appear.

  • Click the Formula Editor button ()

  • Create the desired formula by selecting the desired functions and clicking the button or drag-and-dropping them into the Summary box.

NOTE. When embedding functions, begin with the outermost function and add them moving inward.Ex. To get =TRUNCATE(SQRT(162)), first add Truncate then the square root function.

  • Click OK.

Manual Formulas

To manually add formulas:

  1. Navigate to the Report Designer.

  2. Double click in the desired cell.

  3. Enter the formula.

  4. Save the report.

Search