1. Home
  2. On-premises 5.2
  3. Guides
  4. Tours
  5. Concepts
  6. Tools
    1. Alert Editor
    2. Alias Sync Tool
    3. Analyzer
    4. Apache Parquet Merge Tool
    5. Avro Extractor Tool
    6. Business Schema Designer
    7. Business Schema Manager
    8. Cluster Management Console
    9. Command Line Interface
    10. Content Manager
    11. Dashboard Filters Manager
    12. Data Agent
    13. Data APIs for External Notebooks
    14. Data Manager
    15. Formula Builder
    16. Formula Validation Tool
    17. Inspector Tool
    18. Join Editor
    19. Load Job Viewer
    20. Model Update Viewer
    21. Notebook Editor
    22. Profile Manager
    23. Public API
    24. Query Plan Viewer
    25. Scheduler
    26. Schema Designer
    27. Schema Diagram Viewer
    28. Schema Manager
    29. Schema Wizard
    30. Security Manager
    31. Table Editor
    32. Tenant Management Tool
    33. Versioning Migration Tool
  7. References
  8. Integrations
  9. Data applications
  10. Releases

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 Analyzer 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 Analyzer 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 TypeData Type
Boolean(Only available as a return value from an expression)
DateDate
NumericalInt, Long, Double
StringString and Text
TimestampTimestamp

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.

Note

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

  • // Single line comment
  • /* Multi-
    line
    comment */

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.

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:

ToolObject TypeExpression TypeNote
Analyzer for an Incorta Analyzer ViewFormula ColumnFormula
  ●  Runtime calculation
  ●  Returns scalar value
Analyzer for an Incorta Analyzer ViewIndividual FilterFilter
  ●  Runtime filtering
  ●  Filters individual rows
Analyzer for an Incorta Analyzer ViewMeasure FilterFilter
  ●  Runtime filtering
  ●  Filters after aggregation of rows
  ●  Not available for a Listing Table
Analyzer for an InsightFormula ColumnFormula
  ●  Runtime calculation
  ●  Returns scalar value
Analyzer for an InsightIndividual FilterFilter
  ●  Runtime filtering
  ●  Filters individual rows
Analyzer for an InsightMeasure FilterFilter
  ●  Runtime filtering
  ●  Filters after aggregation of rows
  ●  Not available for a Listing Table
Analyzer for an InsightSort ByFilter
  ●  Runtime sorting
  ●  Sorts individual rows
Analyzer for an Incorta Analyzer TableIndividual FilterFilter
  ●  Computed and persisted
  ●  Filters individual rows
Analyzer for an Incorta Analyzer TableFormula ColumnFormula
  ●  Computed and persisted
  ●  Returns scalar value
Analyzer for an Incorta Analyzer TableMeasure FilterFilter
  ●  Computed and persisted
  ●  Filters after aggregation of rows
  ●  Not available for a Listing Table
Analyzer for an Incorta Analyzer TableSort ByFilter
  ●  Runtime sorting
  ●  Sorts individual rows
Business Schema DesignerFormula ColumnFormula
  ●  Runtime calculation
  ●  Enforces aggregation function
  ●  Returns scalar value
Dashboards FiltersPromptsFormula
  ●  Runtime calculation
  ●  Returns scalar value or array
Dashboards FiltersApplied FiltersFilter
  ●  Runtime filtering
  ●  Filters individual rows on all dashboard tabs where applicable
Dashboards FiltersFilter OptionsFilter
  ●  Runtime filtering
  ●  Filters individual rows on all dashboard tabs where applicable
Schema ManagerFilter Expression Session VariableFilter
  ●  Runtime filtering
  ●  Filters individual rows
Schema ManagerInternal Session VariableQuery
  ●  Runtime query
  ●  Returns scalar value or array
Table EditorFormula ColumnFormula
  ●  Computed and persisted
  ●  Row by row calculation
Table EditorLoad FilterFormulaIndividual row filtering for in-memory data in the Analytics Service
Table EditorRuntime Security Filters FormulaFormula
  ●  Runtime filtering
  ●  Returns scalar value or array
  ●  Filters individual rows
  ●  Row level security (RLS)
Warning

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 Analyzer 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:

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

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, refer to Concepts → Load Filter.

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

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, refer to 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.

To learn more, refer to Concepts → Runtime Security Filter.

Additional Considerations

Backslash support as an escape character

Staring the 5.2 release, the Engine supports using the backslash mark \ as an escape character that you can use within a string in a formula to treat the next character as literal text. For strings that you enclose in double quotes and include double quotation marks within, you must precede each quotation mark that you want to include in the string with a backslash \. The same applies to single quotation marks within a string enclosed in single quotes. In addition, to include a backslash in a string, precede it with another backslash as an escape character.

For example:

The formula:

concat(
    "Place a string between two double quotes: \"TEXT\"",
    ' or two single quotes: \'TEXT\'.',
    "Use a backslash \\ as an escape character."
)

The Result:

Place a string between two double quotes: "TEXT" or two single quotes: 'TEXT'. Use a backslash \ as an escape character.