Templated Filters

Omni allows parameterization of SQL using the mustache template engine, which can be used in the definition of both views and fields. This is perhaps best understood through examples.

Overview

Templated filters can inject dynamic text into a SQL query, often a dimension or fact table. Our syntax requires an opening and closing reference around the dynamic input, using {{# }} to open, and {{/ }} to close to dynamic sections. Inside the braces, you use the name of the object, usually of the form view.field_name.filter (the word "filter"). This will correspond to a filter_only field in the view (see examples below). Inside the pair of braces, you will include the lookup value.

Default values can be set using {{^ }}default_value_clause{{/ }} immediately after the existing templated filter syntax. For example, {{# users.filter_field.filter }}name{{/ users.filter_field.filter }}{{^ users.filter_field.filter }}name like '%foo%'{{/ users.filter_field.filter }} would use a default filter of name like 'foo'.

Usage Examples

Parameterized `WHERE` clause in views based on an SQL query

This simple example allows a query-view to be parameterized. This example uses a filter-only field (see more on this in the dedicated section below), but can also reference a field in the view itself.

# Reference this view as user_fact_example
sql: |
  SELECT 
    user_id,
    COUNT(*) AS total_items
  FROM order_items
  WHERE 
    {{# user_fact_example.order_date.filter }} order_items.created_at {{/ user_fact_example.order_date.filter}}
    AND {{# user_fact_example.status.filter }} order_items.status {{/ user_fact_example.status.filter}}
  GROUP BY 1

dimensions:
  user_id:
    sql: '"USER_ID"'

  total_items:
    sql: '"TOTAL_ITEMS"'

measures:
  count:
    aggregate_type: count

filters:
  order_date:
    type: timestamp
  status:
    type: string
    suggest_from_field: order_items.status

The filters will appear as filter-only fields in the field picker, in their own section under the measures section. These can then be used as normal filters, and the specified expression is inserted in place.

Timeframe Selector

tip

Note that the UI based timeframe control may often be a better path.

A field of dynamic granularity can be constructed such that a single filter on a dashboard swaps the granularity for multiple time series in one.

By editing the .view file, it is necessary to add a dynamic dimension and a filter. In the example below, replace all references to created_date with the appropriate date field and order_items with the fully qualified view reference.

# In the .view file

dimensions:
  created_date_dynamic:
    sql: |
      CASE 
        WHEN {{# order_items.timeframe_selector.filter }} 'Daily' {{/ order_items.timeframe_selector.filter }} THEN ${created_at[date]} 
        WHEN {{# order_items.timeframe_selector.filter }} 'Weekly' {{/ order_items.timeframe_selector.filter }} THEN ${created_at[week]} 
        WHEN {{# order_items.timeframe_selector.filter }} 'Monthly' {{/ order_items.timeframe_selector.filter }} THEN ${created_at[month]}
      END
    label: Dynamic
    timeframes: [Date]

filters:
  timeframe_selector:
    type: string
    suggestion_list: [Daily, Weekly, Monthly]

This timeframe_selector filter can then be used either in workbooks or on dashboards across multiple tiles to update them all at once.

Conversion Rates

A common analytical pattern is to compare conversion rates with 1, 3, 7, 30, or however many days.

For the more static form, one might do something like this:

# Reference this view as order_items

dimensions:
  days_after_user_signup:
    sql: DATEDIFF(DAY,${users.created_at},${order_items.created_at}) # assumes a join has been made to a users view

measures:
  count_users_with_order_within_7_days:
    aggregate_type: count_distinct
    sql: ${user_id}
    filters:
      days_after_user_signup:
        less_than: 8

  user_created_to_order_conversion_rate_7d:
    sql: 1.0 * ${count_users_with_order_within_7_days} / NULLIF(${users.count},0)

This is very useful, but requires that measures be created for 1, 3, 7, 30, etc. -day conversion rates. We can add a dynamic option:

measures:
  user_created_to_order_conversion_rate_dynamic:
    sql: |
      1.0 * COUNT(
        CASE
          WHEN {{# order_items.conversion_days.filter }} ${days_after_user_signup} {{/ order_items.conversion_days.filter }}
          THEN ${order_items.user_id}
        END)
      / NULLIF(${users.count},0)
    format: PERCENT_1

filters:
  conversion_days:
    type: number

Filter-only Fields

filters:
  order_date:
    type: timestamp
  status:
    type: string
    suggest_from_field: order_items.status

filters:
  rolling_window:
    type: string
    suggestion_list:
      - value: "6"
        label: 7 days
      - value: "13"
        label: 14 days
    default_filter:
      is: "6"
    filter_single_select_only: true

 filters:
   timeframe_selector:
     type: string
     suggest_from_field: date_spine.date_string
     suggest_from_topic: opp_line_items

For specific filter syntax, like measure filters see here.

`aliases:`

	filter_name:
		aliases: [old_filter_name]

Similar to table level aliases, occasionally a field name may change in your database, which can cause content to break. To fix this, we can add aliases: to the field in question pointing references from the old field name to the updated field name, restoring content and eliminating content related errors. This behaves similar to table level aliases, as shown

`default_filter:`

  status_templated_filter:
    type: string
    default_filter:
      is: "Completed"
    suggestion_list: [ Compeleted, Returned ]

  rolling_window:
    type: string
    suggestion_list:
      - value: "6"
        label: 7 days
      - value: "13"
        label: 14 days
    default_filter:
      is: "6"
    filter_single_select_only: true

default_filter will automatically populate the filter value in the templated object
This is often paired with filter_single_select_only and suggestion_list when a single-selection is required
For setting the filter arguments, use the filter syntax

`description:`

  status_templated_filter:
    type: string
    default_filter:
      is: "Completed"
    suggestion_list: [ Compeleted, Returned ]
    description: This is for filtering stuff in the status fact table

Metadata about the field, made available in the workbook/dashboard table UI upon hovering over a field with a description, or on right click from the field picker
Omni expects unquoted text (quotes will be removed / ignored)

`display_order:`

  status_templated_filter:
    type: string
    default_filter:
      is: "Completed"
    suggestion_list: [ Compeleted, Returned ]
    description: This is for filtering stuff in the status fact table
    display_order: 1

  other_status_templated_filter:
    type: string
    default_filter:
      is: "Completed"
    suggestion_list: [ Compeleted, Returned ]
    description: This is for filtering stuff in the other status fact table
    display_order: 2

Omni expects a whole number
This will override the sort order for the field picker, inside the field's grouping (i.e. inside a given view)
- display_order will supersede alphabetical sorting
For example, if the two fields above in users are given display_order: they will float to the top of the field list in users, and the remaining fields would be sorted alphabetically
To rearrange views, display_order can be used at the view level
For fields inside groups using group_label, the group will be ranked with the min of all the fields in the group (i.e. if there are 3 fields with display_order of 4, 5 and {empty}, the group will have a display_order of 4)

`filter_single_select_only:`

filters:
  rolling_window:
    type: string
    suggestion_list:
      - value: "6"
        label: 7 days
      - value: "13"
        label: 14 days
    default_filter:
      is: "6"
    filter_single_select_only: true

This argument will require the filter field to use a choose-one dropdown rather than the default flexible filter modal
Recommended with templated filters that require one-and-only-one object

`group_label:`

  rolling_window:
    group_label: Time Filters
    type: string
    filter_single_select_only: true

This will nest a group of fields in the field picker for curated organization
Omni expects unquoted text (quotes will be removed / ignored)
Note measures and dimensions will still be in separate sections in the field picker under each view
Fields can be nested under timeframes using the group label - the text rather than the field should be used (i.e. Created At not created_at)

`hidden:`

rolling_window:
  hidden: true

Remove the field from the UI. Still referenceable in the model, but hidden in the workbook UI.
Expects 'true' or 'false'

`ignored:`

Likely should not be used with filters, can just delete or comment out if it's not desired in the model

`label:`

  status:
    type: string
    label: Status Filterer
    suggestion_list:
      - value: "Completed"
      - value: "Returned"

Label will override the field name for all UI appearances of the field
Omni expects unquoted text (quotes will be removed / ignored)

`required_access_grants:`

  status:
    type: string
    label: Status Filterer
    suggestion_list:
      - value: "Completed"
      - value: "Returned"
    required_access_grants: <access_grant_reference>

required_access_grants: limit a user's ability to query a field based on an assigned user attribute
In order to define an access for a field, the referenced access_grant: has to already be developed in the model file. Read more about setting up an access grant here

`suggestion_list:`

filters:
  rolling_window:
    type: string
    suggestion_list:
      - value: "6"
        label: 7 days
      - value: "13"
        label: 14 days

  status:
    type: string
    suggestion_list:
      - value: "Completed"
      - value: "Returned"

Suggestion list will explicitly set the suggestions on

`suggest_from_field:`

 filters:
   timeframe_selector:
     type: string
     suggest_from_field: date_spine.date_string
     suggest_from_topic: opp_line_items

 filters:
   status_picker:
     type: string
     suggest_from_field: order_items.status

This argument can be used to populate a filter with another field's values
Commonly used with the filter is injected in sql with a corresponding modeled field

`suggest_from_topic:`

 filters:
   timeframe_selector:
     type: string
     suggest_from_field: date_spine.date_string
     suggest_from_topic: opp_line_items

This needs to be paired with suggest_from_list and can tune the specific topic (ie access-filters and default filters) for a given suggestion

`type:`

filters:
  order_date:
    type: timestamp
  status:
    type: string

Currently, filter-only fields can only be defined in the IDE. They require a name, and a type. The accepted types for a filter-only field are:
- array
- boolean
- interval
- json
- number most common
- string most common
- timestamp most common

`view_label:`

filters:
  order_date:
    type: timestamp
    view_label: Filters
  status:
    type: string
    view_label: Filters

This will nest a given field under a different view than it's default parent view, for example, grouping user_facts fields under the users view for better organization and discovery
Note that filters will always be below dimensions and measures
Sometimes can be valuable to pull all filters into a separate psuedo-view, would do that with the view argument as seen here

Parameters

SQL Query View Example

schema: PUBLIC
sql: |-
  Select status, sum(sale_price) as sum_sale_price
  from public.order_items 
  WHERE created_at BETWEEN {{filters.filter_only_parameter_example.dates.range_start}} AND {{filters.filter_only_parameter_example.dates.range_end}}
  group by 1

dimensions:
  status:
    sql: '"STATUS"'

  sum_sale_price:
    sql: '"SUM_SALE_PRICE"'

measures:
  count:
    aggregate_type: count

filters:
  dates:
    type: timestamp

Parameters can be used in Omni in tandem with Filter-only Fields. The value of a filter-only field can be referenced using the structure {{filters.table_name.field_name.value}}, and can be used in definitions for other fields. When that filter-only field is applied to a workbook or dashboard, it can be used to change the value of the parameter field, and thus interact with the query accordingly.

Consider using the filter_single_select_only argument when using a string filter-only field to parameterize some selector.

Users can also reference the start or end range of a date filter using the structure {{filters.table_name.field_name.range_start}} and {{filters.table_name.field_name.range_end}}.

Overview​

Usage Examples​

Parameterized WHERE clause in views based on an SQL query​

Timeframe Selector​

Conversion Rates​

Filter-only Fields​

aliases:​

default_filter:​

description:​

display_order:​

filter_single_select_only:​

group_label:​

hidden:​

ignored:​

label:​

required_access_grants:​

suggestion_list:​

suggest_from_field:​

suggest_from_topic:​

type:​

view_label:​

Parameters​

SQL Query View Example​