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.

Open

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.

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.

Open

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.

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

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.

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

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!"))

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.