Custom fields

Version: 3.2.0+

Custom fields allow you to add extra columns for table and view data, but also on top of aggregated data. These fields can be defined as SQL formulas and depend on the SQL-flavor of the data source you are connected to. You can define any type of expression that is supported by the query language in select statements. Some examples are:

  • a + b, where a and b are the names of existing columns of the table
  • case when year([date_start]) = 2017 then [earnings] * [inflation_rate] else [earnings] end, where date_start, earnings, inflation_rate are columns of the table residing in a SqlServer data source
  • concat("First name", ' ', "Last name"), where First name and Last name are some table columns from a PostgreSQL database

Creating a custom field

To add a custom field, click on the Custom fields button from the header menu of table widgets. When the window opens, click the plus button and introduce the desired name for this new custom field. Introduce the SQL formula in the text area below.

After such fields are added, the ScaiPlatform treats them as regular columns. All the available operations for regular columns are also available on custom fields, i.e. you can filter, order, aggregate, use the fields in graphs, etc.

Make sure that any column names used in the custom field definitions are properly quoted, according to the particular guidelines of the SQL data source you are exploring, e.g. `column name` for MySQL or [column name] for SqlServer

Limitations

  • Custom fields can be defined only based on input columns. Chained custom field definitions are not supported.

    Example: you cannot define a first custom field a as concat([first_name], ' ', [last_name]) and then a second field b as concat('Mr. ', [a]). To avoid this limitation, you can define b as concat('Mr. ', [first_name], ' ', [last_name]) in our simple example, i.e. use only the base columns in custom field definitions.

  • Custom fields can only contain functions and expressions that do not perform any aggregation, but merely add extra columns to a table. Thus, you cannot use aggregate functions like sum, min, max, etc. in custom field definitions.

Custom fields are restricted to source owners. Since custom fields allow the user to execute arbitrary SQL statements on the data source, for security reasons, only source owners are allowed to create these custom fields. If you do not see the button in the header menu of the table widgets, it means that you are not the owner of the data source and cannot add custom fields.