The Formula Builder makes it simple to create a complex formula expression. You can create formulas with a variety of functions — from aggregations, boolean expression, conditional statements, data conversions, data manipulations, to calculations.
You can use the Formula Builder when adding or editing:
- Formula Columns in a schema table or business schema view
- Queries in an internal session variable
- Formulas in a filter expression session variable, dashboard filter (including prompts, applied filter, or filter options) or an insight.
To open the Formula Builder:
- Select the Formula link wherever found.
- Drag & Drop the New Formula to a tray and then select the Formula property text box in the Properties Menu.
Users must have View access permissions to Incorta objects including schemas, business schemas, external session variables, internal session variables, and filter expression session variables in order to use the formula builder.
Sorting by a Formula Column is a very 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 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
The Formula Builder anatomy consists of the following user interface components:
- Header bar that allows you expand, collapse, or exit the Formula Builder
- Data Panel on the left, that you can resize, expand, or collapse
- Functions and Variables Panel on the right, that you can resize, expand, or collapse
- Formula Editor with gutters on the left and right
Footer action bar that allows you to
- Format your calculations or programmatic expressions in the Formula Editor.
- Cancel your changes and close the Formula Builder.
- Validate & Save your changes.
By default, the Data Panel in the Formula Builder reflects the existing selection in the Analyzer from which it was opened. In certain cases, the Data Panel will be empty because there is no pre-existing selection context, as is the case for creating, for example, a new internal session variable.
On the left side of the page is a list of columns, pre-populated according to the underlying schema definition. To add columns from additional schemas, click the + (Add icon) on the left-hand side.
You can use the Manage Data Sets Panel to add selected schemas, business schemas, tables, or views to the Data Panel.
The Manage Data Sets Panel contains the Views and Tables tabs that can be filtered using search.
To filter and find items in the Data Panel, enter a search in the Search text box or use the Column Type drop down menu to narrow your results. Column Types include:
For a given column in the tree, select the information icon to view the column details and preview sample data.
To help build a calculation or programmatic expression in the Formula Editor, you can use the Functions and Variables Panel.
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
- Boolean Functions
- Conditional Statements
- Conversion Functions
- Filter Functions
- Miscellaneous Functions
- Arithmetic Functions
- Date Functions
- String Functions
- Query Functions (only with Internal Session Variables)
To learn more about the function categories and individual functions, visit Formula 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 an example of the function’s usage and a summary description of the function’s usage.
In the Variables tab you will find the variables list which includes:
- System Variables
- System Date Variables
- Internal Session Variables
- External 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.
The Formula Editor supports both code and dot completion.
For example, with the bzschAdvWork_Sales business schema in the Data Panel, typing the letter “s” in the editor shows all objects with the “s” in the name:
- Built-in functions
- Business schema
- System data variables
After selecting the function, start typing the fully qualified column name in a business schema.
A fully qualified column name consists of three parts: business_schema.view.column.
Entering a period (.) triggers dot completion in the 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 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
- Built-in function arguments
- Unidentified built-in functions
- Unidentified fully qualified named columns in the Data Panel
- Unidentified variable names.
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.
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 expression accuracy and will prevent a formula being saved, for example, if there is undefined field in an expression. An error message appears above the footer action bar with details about the validation error.
To create a calculation in an insight, follow these steps:
- In the Data Panel of the Analyzer, select New Formula.
- Drag & drop the New Formula to the measure tray.
- In the pill’s Data properties, select the Formula text box to open the Formula Builder.
- Create the calculation in the Formula Editor using data columns, built-in functions, and variables. To add a column, function, or variable, select the object and either drag and drop the object to the Formula Editor canvas or double click to add it to the editor.
- Select Validate & Save to apply your calculation and verify the expression accuracy
- Provide a value for the Name property of the formula
- As needed, select the Format tab in the Properties menu, and the choose the appropriate data format for the calculation result such as Currency Rounded.
- In the Analyzer, select Done in the action bar to save your changes.
To create a Formula Filter for a measure:
- Drag and drop the New Formula button into the measures field.
- Select the down arrow corresponding to the new formula to define it.
- Set the aggregate function to “FORMULA”, in the “Aggregation” field. This causes the Analyzer to refer to the formula expression (entered in the next step) to determine the actual aggregation function.