# Tools → Formula Builder

## About the Formula Builder

With the Formula Builder, you can create and edit expressions for the following:

- A Formula Column in a physical schema table, Incorta Analyzer Table, business schema view, Incorta View, or insight
- A Query for an internal session variable
- A Formula for a filter expression session variable, dashboards filter, or an insight filter.

## Formula Builder Access Permissions

A user that belongs to a group with the Schema Manager or the SuperRole role can access the Schema Manager, Table Editor, and Business Schema Manager. A user that belongs to a group with the Analyze User or Individual Analyzer role can create a Dashboard with the Analyzer.

### Invoke the Formula Builder

You can invoke the Formula Builder from the following tools when defining an expression within a specific context:

- Analyzer for Incorta Analyzer Table
- Analyzer for Incorta View
- Analyzer for Insights
- Business Schema Designer
- Dashboards Filters
- Schema Manager
- Table Editor

To learn more, review How to invoke the Formula Builder.

## Formula Builder Anatomy

The Formula Builder anatomy consists of the following user interface components:

- Header bar
- Data panel
- Manage Data Sets panel
- Expression Editor
- Functions and Variables panel
- Footer Action bar

### Header bar

The Header bar allows you expand, collapse, or exit the Formula Builder.

### Data panel

The Data panel contains a hierarchical tree. The parent object in the tree is either a schema or business schema. The child object is either a table or runtime business view. The grandchild object is a column (or formula column).

The hierarchical tree consists of zero or more schemas or business schemas. The user friendly column label shows in the tree and not the column name.

To filter and find columns in the Data panel, enter a search term in the Search text box or use the Column Type drop down menu to narrow your results. The column types are general categories for data types when applicable:

Column Type | Data Type |
---|---|

Boolean | (Only available as a return value from an expression) |

Date | Date |

Numerical | Int, Long, Double |

String | String and Text |

Timestamp | Timestamp |

For a given column in the tree, select the **Information icon** to view the column details and preview sample data.

You can also manage the tree hierarchy. Top open the More Options menu, select the** kebab (⋮)** icon. In the More Options menu, you can:

- Collapse to Schema Level
- Collapse to Table Level
- Expand All
- Sort by Name
- Sort by Original Order

To add a column to the formula expression in the Formula Editor from the Data panel, you can either **drag & drop** or **double-click** the column. The expression in the Formula Editor shows the three part, fully qualified column name.

### Manage Data Sets panel

You can use the Manage Data Sets panel to select and deselect schemas, business schemas, tables, or views that appear in the Data panel.

The Manage Data Sets panel contains the Views and Tables tabs that can be filtered using search.

Here are the steps to add a schema, table, business schema, or runtime business view to the Data panel.

- In the Data panel, select
**Manage Dataset**. - In the Manage Data Sets panel, select either the
**Views**or**Tables**tab. - Optionally, use search to find a specific schema, table, business schema, or runtime business view.
- From the tree, select an item check box.
- Select
**X**to close the Manage Data Sets panel.

### Formula Editor

The **Formula Editor** supports both code and dot completion.

For example, with just the `bz_sch_Sales`

business schema in the Data Panel, typing the letter “s” in the Formula Editor shows all objects with “s” in the name:

- Built-in functions
- Built-in date system variables
- Built-in miscellaneous system variables
- Business schema view columns
- Internal session variables
- External session variables
- Global variables (not supported in all contexts)

After selecting the function, start typing the fully qualified column name in the business schema.

A fully qualified column name for a business schema view consists of three parts: business_schema.view.column.

Entering a period `.`

triggers dot completion in the Formula Editor. To select the view, use the **Tab** keystroke.

You can continue typing, use the tab keystroke to complete a selection, or simply select a column in the list.

The Formula Builder supports using comments in the Formula Editor as follows:

- // Single line comment
- /* Multi-

line

comment */

This feature is available in the 5.1.2 release.

#### Color Coding

The Formula Editor uses color coding to signify textual meaning as follows:

**Orange**for built-in functions and mathematical expressions**Mustard Yellow**for system, session, and presentation variables**Violet**for columns in the Data Panel-
**Red**for- Built-in function arguments
- Unidentified built-in functions
- Unidentified fully qualified named columns in the Data Panel
- Unidentified variable names.

#### Left and Right Gutters

The Formula Editor contains two gutters located on either side of the Formula Editor canvas.

The **Left Gutter** shows **line numbers** and allows for you to both collapse (**+**) and expand (**-**) parenthesized expressions when available.

The **Right Gutter** indicates the **cursor line** position with a horizontal line. In addition, the right gutter shows a grey bar to indicate a selected object name in the editor canvas and the beginning and end of parenthesized expressions.

### Functions and Variables panel

To help build a calculation or programmatic expression in the Formula Editor, you can use the Functions and Variables panel.

#### Functions tab

In the **Functions** tab, you can filter the list of functions by entering a search term or using the Function Category drop down menu.

The function categories are:

- Aggregation Functions (not available in the context of physical schema table formula column)
- Boolean Functions
- Conditional Statements
- Conversion Functions
- Filter Functions
- Miscellaneous Functions
- Arithmetic Functions
- Date Functions
- String Functions
- Query Functions (only available in the context of an internal session variable)

To learn more about the built-in functions, visit Built-in Functions.

To **view the function syntax** that includes input arguments and associated data types and the function return data type, select a function in the function list.

In most cases, there is a summary description of the function and an example of the function’s usage.

#### Variables tab

In the **Variables** tab you will find the variables list which includes:

- Built-in date system variables
- Built-in miscellaneous system variables
- External session variables
- Global variables
- Internal session variables
- Presentation variables (only when the context is a dashboard with existing presentation variables)

To view the Variable syntax, select the Variable in the variable list which will include the return type and descriptive summary.

### Footer Action bar

In the **Footer Action bar**, select **Format** to format the formula expression in the Formula Editor canvas with automatic tab indentation.

The editor canvas shows vertical lines to detail the tab indentation positions.

Select **Cancel** to close Formula Builder without saving your changes.

**Validate & Save** verifies the expression accuracy and will prevent a formula being saved, for example, if there is an undefined field in an expression. An error message appears above the Footer Action bar with details about the validation error.

## How to invoke the Formula Builder

You can invoke the Formula Builder in a variety of ways for the following tools and contexts:

Tool | Object Type | Expression Type | Note |
---|---|---|---|

Analyzer for an Incorta View | Formula Column | Formula | |

Analyzer for an Incorta View | Individual Filter | Filter | |

Analyzer for an Incorta View | Measure Filter | Filter | |

Analyzer for an Insight | Formula Column | Formula | |

Analyzer for an Insight | Individual Filter | Filter | |

Analyzer for an Insight | Measure Filter | Filter | |

Analyzer for an Insight | Sort By | Filter | |

Analyzer for an Incorta Analyzer Table | Individual Filter | Filter | |

Analyzer for an Incorta Analyzer Table | Formula Column | Formula | |

Analyzer for an Incorta Analyzer Table | Measure Filter | Filter | |

Analyzer for an Incorta Analyzer Table | Sort By | Filter | |

Business Schema Designer | Formula Column | Formula | |

Dashboards Filters | Prompts | Formula | |

Dashboards Filters | Applied Filters | Filter | |

Dashboards Filters | Filter Options | Filter | |

Schema Manager | Filter Expression Session Variable | Filter | |

Schema Manager | Internal Session Variable | Query | |

Table Editor | Formula Column | Formula | |

Table Editor | Load Filter | Formula | Individual row filtering for in-memory data in the Analytics Service |

Table Editor | Runtime Security Filters Formula | Formula |

Sorting by a Formula Column is an expensive operation that in certain situations can cause instability such as race conditions and out of memory errors.

To enable sorting by a Formula Column, an System Administrator needs to modify the `engine.properties`

file in the Analytics Service and add the following property:`engine.allow_formula_sorting_in_flat_table = true`

### Analyzer

Using a formula and the Formula Builder in the Analyzer, you can create a formula column, individual filter, measure filter, or sort by expression. You can do this for an Incorta Anlayzer Table, Incorta View, and an insight. Here are the steps:

- In the Data panel, select
**Add Formula**. - Drag and drop
**New Formula**to a tray in the Insight panel or target box in the Properties panel. - In the Formula Builder, in the Formula Editor, create the expression.
- In the Footer bar, select
**Validate & Save**.

To learn more about a specific concept, please review one of the following:

- Concepts → Aggregate Filter
- Concepts → Incorta Analyzer Table Formula Column
- Concepts → Incorta View Formula Column
- Concepts → Individual Filter

### Business Schema Designer

Here are the steps to create a formula column for a business schema view using the Business Schema Designer.

- In the Data panel, select
**New Formula**. - Drag and drop
**New Formula**to the view canvass. - Specify the formula column name, label, description, and function type (Measure or Dimension).
- To open the Formula Builder, select
**Set Formula**. - In the Formula Builder, in the Formula Editor, create the expression.
- In the Footer bar, select Validate & Save.

To learn more, please review Concepts → Business Schema View Formula Column.

### Dashboards filters

For Dashboards Filters, you can create a formula for a:

- Prompt
- Applied filter
- Filter option

#### Prompt

Here are the steps to create a formula for a prompt:

- In Dashboard Filters, select Prompts.
- In the Data panel, select
**New Formula**. - Drag and drop
**New Formula**to the dashboard runtime filters tray. - Open the Properties for the New Formula pill.
- In Properties, to open the Formula Builder, select the
**Formula text box**. - In the Formula Builder, in the Formula Editor, create the expression.
- In the Footer bar, select
**Validate & Save**. - Optionally specify if the formula for the prompt is a default filter and/or mandatory filter.

To learn more, please review Concepts → Prompts.

#### Applied filter

Here are the steps to create a formula for an applied filter:

- In Dashboard Filters, select Applied Filters.
- In the Data panel, select
**New Formula**. - Drag and drop
**New Formula**to the dashboard applied filters tray. - Open the Properties for the New Formula pill.
- In Properties, to open the Formula Builder, select the
**Formula text box**. - In the Formula Builder, in the Formula Editor, create the expression.
- In the Footer bar, select
**Validate & Save**. - Optionally specify the properties of the applied filter including Name, Operator, and Values.

To learn more, please review Concepts → Applied Filter.

#### Filter option

Here are the steps to create a formula for a filter option:

- In Dashboards Filters, in the
**Action bar**, select**Filter Options**(Filter with Gear icon). - In the Data panel, select
**New Formula**. - Drag and drop
**New Formula**to the filter tray in**Filter Options**. - Open the Properties for the New Formula pill.
- In Properties, to open the Formula Builder, select the
**Formula text box**. - In the Formula Builder, in the Formula Editor, create the expression.
- In the Footer bar, select
**Validate & Save**. - Optionally specify the properties of the applied filter including Name, Operator, and Values.

To learn more, please review Concepts → Filter Option.

### Schema Manager

In the Schema Manager, you can create an expression for:

- an internal session variable
- a filter expression session variable

#### Internal session variable

Here are the steps to create a query expression for a internal session variable using the Formula Builder:

- In the Schema Manager, in the Action bar, select
**+ New**→**Session Variable**→**Internal Variable**. - In the Internal Variable dialog, specify a variable name and a description.
- To open the Formula Builder, select the Query
**text box**. - In the Formula Builder, in the Formula Editor, create the query expression with built-in functions in the
**Query**category. - In the Footer bar, select
**Done**. - In the Internal Variable dialog, optionally select
**Test As**or**Test**. - To save your changes, select
**Add**.

To learn more, please review Concepts → Internal Session Variable.

#### Filter expression session variable

Here are the steps to create a filter expression session variable with the Formula Builder:

- In the Schema Manager, in the Action bar, select
**+ New**→**Session Variable**→**Filter Expression**. - In the Filter Expression dialog, specify a filter expression name and a description.
- To open the Formula Builder, select the Filter
**text box**. - In the Formula Builder, in the Formula Editor, create the filter expression with built-in functions in the
**Boolean**category. - In the Footer bar, select
**Done**. - To save your changes, select
**Add**.

When you test a filter expression session variable, you will only see the expression text.

To learn more, please review Concepts → Filter Expression Session Variable.

### Table Editor

In the Table Editor, you can create an expression with the Formula Builder for a:

- Load filter
- Physical schema table formula column
- Runtime security filter

#### Load filter

Here are the steps to create a load filter in the Table Editor.

- In the Table Editor, in the Load Filter section, select to open the Formula Builder, select the Load Filter
**text box**. - In the Formula Builder, in the Formula Editor, create the load filter expression.
- In the Footer bar, select
**Validate & Save**. - To save your changes, in the Action bar, select
**Done**.

To learn more, please review:

#### Physical schema table formula column

A physical schema table formula column performs a row-by-row calculation and persist the result. Here are the steps to create a Formula Column in the Table Editor.

- In the Table Editor, in the Action bar, select
**+ New**→**Formula Column**. -
In the Formula Columns section, for the new formula column, define the various properties:

- Show in Analyzer
- Name
- Label
- Function

- To open the Formula Builder, select the Column Formula
**text box**. - In the Formula Builder, in the Formula Editor, create the expression.
- In the Footer bar, select
**Validate & Save**. - To save your changes, in the Action bar, select
**Done**.

Incorta infers the data type of the returned scalar value. You can use a built-in conversion function as a wrapper function to explicitly define how Incorta infers the data type.

To learn more, please review Concepts → Physical Schema Table Formula Column.

#### Runtime security filter

Here are the steps to create a Runtime Security Filter in the Table Editor.

- In the Table Editor, in the Action bar, select
**+ New**→**Security Filters**→**Formula**. - In the Runtime Security Filter section, in Formula, select the
**text box**to open the Formula Builder. - In the Formula Builder, in the Formula Editor, create the runtime security filter expression.
- In the Footer bar, select
**Validate & Save**. - To save your changes, in the Action bar, select
**Done**.