Skip to main content
Most fields in the field picker come directly from your database schema. However, by using custom fields you can also create new ad-hoc dimensions and measures to define custom logic and calculations that can be reused. Custom fields can use both SQL and modeled objects. Only users with Querier permissions and higher will be able to make SQL references in calculations (though any user that can touch the workbook page can create new fields from modeled objects). See more on permissions here.

​
Examples

Custom Dimension 
CASE    WHEN ${users.state} = 'California' THEN 'West'    WHEN ${users.state} = 'New York' THEN 'East'    ELSE 'Other'END

concat(${users.first_name}, ' ', ${users.last_name})
Custom Measure 
count(distinct ${users.full_name})
Note how this custom measure also references the previously created full_name custom field. That logic is ‘chained’ together when expressed in the generated SQL.

​
Creating a Custom Field

There are a few ways to create a new Custom Field. Once saved, the Custom Field will become available in the field picker.
  1. Fields can be created from field picker, using the ”+ Add Field” menu at the bottom of the field list. Field can also be adjusted by right-clicking on any existing field and selecting ‘Edit.’
  1. Right click on any existing dimension and choose an aggregation (e.g. count distinct, sum, average, min, max). This will automatically create a new custom measure that appears in the field picker.
  1. Fields can be added from parsed SQL. When queries are run in SQL, Omni will extract valid snippets into associated potential dimensions. They’ll be show in a special section at the top of the field picker. To add these fields to the workbook, select “Add to Workbook” from the Query Fields section:

​
Custom Field Syntax

You can think of a Custom Field as a snippet of SQL logic that gets injected into the generated SQL query whenever the field is brought into the analysis plane. To create one, simply write a snippet of SQL inside the editor. Optionally, you can leverage the substitution operator, ${view.field}, to reference other fields. The substitution operator makes code more reusable and modular, enabling you to reference other objects. It’s particularly beneficial when you want to chain together logic. For example, if you previously created a different custom field, you can reference it without having to repeat the calculation again. And, if in the future you change the definition for that field, the change will propagate to everything else that relies on it. When you add the custom field to a query, you can see the logic applied in the SQL block.