### Formula Basics

Formulas allow you to do calculations, make logical comparisons, parse strings, insert images, and more. Formulas are compositions of functions, parameters, data fields, cell references, and number, date, or text values. All formulas begin with an equal sign (=).

In the Report Designer, users can either key their desired formulas directly into a cell or enter them with the assistance of the built-in **Formula Editor**.

To add a formula to a report cell, select the cell, then click the formula icon to open the **Formula Editor **window. The Formula Editor has a suite of features to help guide you when using formulas.

You can use the **Search** field to search through the functions by name. Or start typing in the **Formula** field to get a list of functions that match the text.

Click on a function, or use the up and down arrow keys, to highlight a function and see a description of what it does. To add the selected function to the formula, press the **Enter** key.

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 + 2`

. In this example, the formula comprises one *function*, the addition function (`+`

), and two *arguments*, the numbers `1`

and `2`

. When the report runs, the formula calculates and *returns *a value of `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 `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.

**Arguments**

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.

Some arguments are optional. Those are surrounded by brackets [ ]. Some arguments are a list of values. Those are followed by an ellipsis (...).

*Today([useCultureFormat]) And(listOfStatements…)*

*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.

Click **Okay** when you are finished.

Formulas are used in several areas besides the report design: custom sorts and groups, drilldowns, conditional formatting for cells and charts, and custom crosstab fields, 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.

**Note about sections**

Formulas which reference data fields or cells, with the exception of aggregate functions, should be in the same report section as the reference data. Detail sections repeat for every data field, group sections repeat for every group, and page sections repeat for every page. Since most formulas expect only one reference value, and not repeated values, referencing a repeated field or cell from outside of its section can return irregular data. Formulas need to repeat alongside their reference values. Aggregate functions are the exception since they are designed to evaluate once for a group of data. For more information, see **Sections**.

**Manually typing formulas**

You have the option of typing in your functions, data fields, parameters, and cell references manually. Use the following formatting guidelines. Formulas must be preceded with an equal sign when being manually entered into cells.

**Text**

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 the current page of the report

· `@reportName@`

gives the name of the report

· `@reportFullName@`

gives the name and path to the report

See **Parameters** for more information.

**Syntax checking**

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.

**Types of functions**

There are several types of functions, which are grouped into the following categories:

**Aggregate**

Aggregate functions act on a group of data, and return a calculation based on that group. Unlike with other functions, aggregate functions return a different value based on the section of the report they are in. To get an aggregate for each group of a grouped data field, place the function in a Group Footer or Group Header section. To get an aggregate for an entire data field, place the function in a Report Footer or Report Header section. Aggregate functions will not work in any other report section.

**Tip: **Aggregate functions cannot be used in conditional formulas or ExpressView formula columns.

**Operators**

Operators perform basic mathematical or logical operations on two arguments. Unlike other functions, operators go between their arguments, and parentheses are not necessary. For example:

· `="Hello "&"world!"`

· `={Order.UnitPrice}-{Order.Discount}`

The operator `^`

is also available, such that `A^B`

returns the result of raising `A`

to the power of `B`

. This would be written as `AB`

on paper.

There are also several logic operators that are listed in the following section.

**Logical**

Logic functions measure truth conditions, which are used in conditional functions to do different things depending on whether their arguments are true or not. The main conditional functions are the `If()`

function, the `Switch()`

function, and the **conditional formatting** editors for cells and charts.

**What is truth? (advanced users)**

Functions which measure logical conditions will return a value of either `True`

or `False`

, depending on whether the condition is met or not. These values, also called *Booleans*, are represented implicitly when passing the result of logic conditions to other functions. This means that there is no explicit representation of `True`

or `False`

that you can display in a report cell. Rather, if you want to display the result of a logical function, the application will convert `True`

or `False`

to strings with the text `"True"`

or `"False"`

.

**Tip: **Strings are surrounded by double quotation marks (").

For example, the formula `=(2 > 1)`

has an implicit value of `True`

. In a report cell, this would resolve to the string `"True"`

. However, for the formula `=If(2 > 1, x, y)`

, the `If()`

function evaluates this argument as the Boolean value `True`

, not as the text string `"True"`

.

Some functions have Boolean arguments that you simply want to pass a static truth value. To pass a static `True`

or `False`

to other functions, use the functions `True()`

or `False()`

.

**Note about conditional formatting**

Conditional formatting formulas must evaluate to either `True`

or `False`

. Consider these formulas each to be the condition for an invisible `If()`

function. If the condition is met, then the formatting will take place; and otherwise, it will not. You should not manually put an `If()`

function inside a conditional formula in most circumstances.

**Logic operators**

The following logical operators are available:

`=`

equal`!=`

not equal`>`

greater`<`

less`<=`

less or equal`>=`

greater or equal

Logic operators are used as `A•B,`

where `A`

and `B`

are arguments and `•`

is the operator. They return `True`

if the operant condition is met, and `False`

otherwise.

**Date**

Date functions do calculations based on Dates, Times, or a type called a *DateTime*, which is a combination of a date and time.

Dates can represent a day or month of the year, such as

`January`

or`January 1st`

, or a specific date, such as`January 1st, 2017`

.Times can represent a quantity of time, such as

`one year`

, or a time of day, such as`12:00 pm`

.DateTimes represent a specific date and time, such as

`January 1st, 2017 12:00pm`

. However, sometimes functions will return only a date or time as a DateTime type. There will be a default or placeholder value for the other component, which should be ignored.

Functions that input a date accept a Date or DateTime argument. Functions that input a time accept a Time or DateTime argument. To pass text as a date or time to a function, first wrap the text in the `DateValue()`

or `TimeValue()`

function. Some functions can interpret a date or time as a string, but as a general rule, it is better to convert the value first.

**How formatting affects date display**

By default, the application interprets text strings that "look" like dates or times, as a DateTime. This means that, when your **cell formatting** is set to **General** or **Date**, the cell contents will be displayed in the DateTime format. You can test this out by simply typing `"Jan-02"`

into a report cell, then running the report. Instead of seeing the text `"Jan-02"`

, you will see a formatted date, and maybe a time attached to the end. Note that since you did not type a year or time, the application uses placeholder values for the year (the current year) and the time (12:00 AM).

You may or may not want this to happen. An example of erroneous behavior could happen if you type a Time, say `"02:00 AM"`

, into a cell formatted as **General**. The application sees this value as a DateTime. If the default formatting for DateTimes omits the time component, you would end up with only a default date value displayed, and no time at all.

Change the cell formatting to **Date** to select how the date or time should display for this cell. For example, if you set the formatting to `MM/dd`

, and run the report again, the date `"Jan-02"`

will display as `01/02`

. This is likely closer to what you would have wanted.

If you do not want to format a date or time cell at all, set the cell formatting to **Text**. The cell text will be displayed as is. However, note that if you pass this cell as an argument to a date function, the text will be interpreted as a DateTime regardless of the formatting.

**Day/month ambiguity (American versus European style dates)**

Americans represent the date `"Jan-02-2017"`

as `01/02/2017`

when written with backslashes or hyphens. However, Europeans would write this same date as `02/01/2017`

instead, with the day and month places switched. Date functions always interpret a date written this way in the American style: Month/Day/Year. To avoid potential ambiguity, dates should be written in a different way, preferably by spelling out the name of the month.

**Financial**

Financial functions are used for a variety of monetary calculations, such as interest rates or asset depreciation.

**Database and Data Type**

Data functions are used to check whether values are of a certain type. They are often used within logical formulas to handle conditions where data fields have irregular data. Such formulas are often referred to as *sanity checking* or *error checking*. Data functions can be used to display custom error messages instead of the default errors, and to make sure that a report will run even if it has erroneous data.

**Arithmetic and Geometric**

These functions are used for a variety of mathematical calculations, such as finding the square root of a number, or rounding a number to the nearest decimal place.

**String**

String functions are used for manipulating text values in different ways. They can be used for extracting parts of strings, combining multiple strings together, or transforming strings into different forms. These are best used on text that follows predictable patterns, such as street names or phone numbers. They can be combined with conditionals to handle less uniform text patterns.

**Formatting**

Shows the input argument in **bold**, *italic*, or __underlined__ text. Styles apply alongside any **cell formatting**. These can be used to format only some parts of the text.

**Other**

These are miscellaneous functions that do not fit into any other categories. For more information, see the function descriptions in the Formula Editor.

**Combining functions**

It is possible to combine multiple functions into one formula calculation. This occurs often when using logical functions, because you will often want to evaluate a function, and then check the value with an `If()`

function to decide what to display. This is called function *nesting*, since you are using functions as arguments in the parentheses of other functions.

The following formula nests a logical comparison of the result of the `AggCount()`

function inside an `If()`

function:

`If(AggCount({Products.ProductName}) >= 10, "Okay", Bold("Warning!"))`

**Caution: **Pay extra attention to the parentheses to make sure that every function is closed correctly. This is the most common cause of errors when nesting functions.

Now that the report has become more intricate, it may help to have a better way to see the data presented. This can best be done through visualizations. Click here for more information.