The Formula Editor has a suite of features to help guide you when using formulas.
You can use the the Search field field to search through the functions by name. Or start typing in the the Formula field field to get a list of functions that match the text.
...
List of functions that match the text
Tip
You can search for data fields, report cells, and parameters in the same manner.
Using functions
Formulas work by applying some calculations to a few values that you give them. A basic example of a formula is 1 is 1 + 2. In this example, the formula comprises one comprises one function, the addition function (+), and two two arguments, the numbers 1 and 2numbers 1 and 2. When the report runs, the formula calculates and returns returns a value of 3of 3.
Not every function takes two arguments, so functions cannot always be written as (argument1 function argument2). In most cases functions instead use the following format:
Function(argument1, argument2, ...)
The addition example could also be written as Addas Add(1, 2). This is the style that most formulas in the application use. Most functions are more abstract than simple arithmetic. Each function has a description which tells you exactly what it does and how to use it.
...
An argument is a value that a function uses to do a calculation. Functions have different amounts and types of arguments. When a function is first entered into the Formula field, there are placeholder values for each required argument. Click on a placeholder to see the description of the argument.
...
Description of
...
the condition argument of the If function
Some arguments are optional. Those are surrounded by brackets [ ]. Some arguments are a list of values. Those are followed by an ellipsis (...).
...
The Today function takes an optional argument.
...
The And function takes a list of arguments.
Tip
Some functions take no arguments. These are formatted with empty parentheses:Function()
You need to supply values for all of the function's required arguments. Type a value into the argument space, or drag a data field or function over the placeholder.
...
Dragging a data field to an argument
Click Click OK when when you are finished.
Formulas are used in several areas besides the report design: custom sorts and groups, drilldowns, and conditional formatting for cells and charts, to name a few. In every area where you can use a formula you can click on a formula icon to open a Formula Editor window. These areas may require a specific type of data to be returned from the formula. Some built-in functions, such as aggregates, may be unavailable. Consult the relevant topic for the specifics.
...
Surround text with double or single quotation marks:
"Hello, World!"
'I am on fire'
If you want to use a quotation mark in the text, then surround the text with the opposite mark:
"You're on fire"
'He says "like" too often'
Do not use quotation marks around numbers.
Data fields
{DataCategory.DataField}
...
where DataCategory is the name of the data category
...
and DataField is the name of the data field
Cell references
To use the value from another cell in a formula, use the following format:
[C#]
...
where C is the letter of the cell column
...
and # is the number of the cell row
Caution
Rearranging cells can cause cell references to break.
Parameters
Parameters return special values depending on some condition. The built-in parameters are:
@pageNumber@
gives gives the current page of the report
@reportName@
gives gives the name of the report
@reportFullName@
gives gives the name and path to the report
To find out if you have more parameters available in your environment, contact your administrator.
...
If there are any typos in the function, the Formula Editor will underline the relevant section in red, and show a brief description of the problem. You need to fix the problem before running the report or the result of the formula will be an error.
...
This formula will not work without a concatenation operator "&"