Formatting values

Formatting controls how values display in queries and visualizations. You set a format on a dimension or measure using the format parameter, or define reusable formats at the model level with custom_formats. Formats can also be set on a per-query basis from visualization configuration, using the same format types.

Common questions

When are formats applied?

Formats are applied after the SQL runs, so they do not impact grouping. To handle grouping with truncation, use ROUND() or FLOOR().

How are custom formats and model constants different?

While both custom_formats and constants enable reusability, they serve different purposes:

Use custom_formats when you need to define a complete format object that will be used across multiple fields
Use constants when you need a reusable string value that might be used in format strings or other contexts

Can I use model constants in formats?

Yes. You can reuse a model constant inside any format string — named, custom Excel-style, or a conditional format branch — with @{constant_name}:

Model file

constants:
  compact_time:
    value: 'm:ss "min"'
  margin_display:
    value: '#,##0.00 "bps"'

Dimension or measure

dimensions:
  margin:
    sql: '"margin_bps"'
    format: "@{margin_display}"

Constants are validated against the model: unknown names and malformed @{...} references surface as model validation issues in the IDE.

Do I need a constant to reuse a named format?

No. If a named format like usdcurrency_2 or percent_1 already does what you need, use it directly — there is no benefit to wrapping a named format in a constant.

How do formats work with Markdown?

There are two separate ways Markdown can factor in:

Approach	How	When to use
`format` with a Mustache template	Add `{{field}}` references to a format string. Omni fills them in at display time and returns plain text.	You want to pull in values from other fields — for example, a currency symbol that changes per row.
`markdown` parameter	A separate Mustache template on a dimension or measure that Omni renders through its Markdown processor in data table cells.	You want Omni itself to render bold text, links, or images directly in the workbook table.

The key difference: format returns plain text; markdown returns rendered Markdown HTML in table cells.You can set both on the same field: format controls display in standard value cells (and for numeric/date fields); markdown takes over when the column is shown as Markdown in a table visualization.See Mustache templates, Dimension markdown, and Measure markdown for syntax and examples.

Where to define formats

Formats can be defined in two places:

Approach	Where	Use when
Field-level	The `format` parameter on a dimension or measure	The format is specific to a single field
Model-level	The `custom_formats` parameter in a model file	The same format is used across multiple fields and you want to maintain it in one place

Both approaches support the same format types — the difference is whether the format is defined inline on the field or defined once and referenced by name.

Choosing a format type

Formats accept four shapes. Pick the one that matches your use case:

Use this	When you want to	Examples
Named format	Apply a built-in format. Start here — named formats handle currency, percentages, and compact numbers. Use a currency prefix like `usd`, `gbp`, or `jpy` for a specific currency; omit the prefix to use the locale’s default.	`currency_2`, `usdaccounting_0`, `percent_1`, `big_2`
Excel-style string	Control layout beyond what named formats offer — append units, add text, use semicolon sections for positive/negative display, or add Mustache `{{field}}` references to interpolate other row values.	`'#,##0.00 "kg"'`, `'"{{currency.raw}}" #,###.00'`
Conditional format	Choose the format at runtime based on a field value, user attribute, or filter. Each branch can reference a named format, an Excel-style string, or a model constant.	`depends_on: { field: currency_code }`
Reusable format	Define a reusable format at the model level. Reusable formats can include named formats, Excel-style strings, or conditional logic.	`format: dynamic_currency`, `format: margin_bps`

Named formats

Numeric and currency formats default to two decimal places, with the exception of id. You can explicitly set decimal places by appending _<number_of_places> (up to 4) to a numeric or currency format. For example, format: number_4, format: big_1, format: usdaccounting_0. When decimal length isn’t set, decimals will be truncated to the shortest possible length for each row.

Numeric formats

Format	Example	Notes
`number`	1,234.50 (`number_2`)
`percent`	24.4% (`percent_1`)
`id`	123450	Numbers, no commas
`billions`	1.20B (`billions_2`)
`millions`	5.6M (`millions_1`)
`thousands`	8.90K (`thousands_2`)
`big`	5.60M; 1.23K; 12.23 (`big_2`)	Uses `millions` if `>1M`; `thousands` if `>1000`; otherwise `number`

Currency formats

Currency formats are available for USD, EUR, GBP, AUD, JPY, and BRL. Prefix the format category with the currency code (for example, usdcurrency, jpyaccounting). Formats without a currency prefix use the organization’s default currency.

Format category	Example	Notes
`accounting` (e.g. `usdaccounting`, `jpyaccounting`)	$(1,234.50) (`usdaccounting_2`)
`currency` (e.g. `usdcurrency`, `gbpcurrency`)	-£1,234.50 (`gbpcurrency_2`)
`bigcurrency` (e.g. `bigusdcurrency`, `bigjpycurrency`)	€5.60M; €1.23K; €12.23 (`biggbpcurrency_2`)	Compact display
`financial` (e.g. `audfinancial`, `brlfinancial`)	(1,234.50) (`financial_2`)	Does not contain a currency mark
`bigaccounting`, `bigfinancial` (e.g. `bigusdaccounting`, `bigjpyfinancial`)	Same compact rules as `big*` formats

Time formats

Time formats are available for dimensions only.

Format	Notes
d3 time formats	Individual timeframe elements can be formatted using `timeframe_metadata`.
`"%Y-%m"`
`"%Y-%m-%d"`
`"%Y-%m-%d %H:%M:%S"`

Time format with timeframe metadata

created_at:
  timeframe_metadata:
    month:
      format: "%Y-%m-%d"

Excel-style strings

In addition to named formats, formats accept Excel-style strings. These follow the same conventions as spreadsheet number formats.

For standard currency display, prefer named formats like usdcurrency_2 or gbpaccounting_0. Use Excel-style strings when you need layout control that named formats don’t offer — for example, appending units, mixing text with numbers, or using semicolon sections for positive/negative display.

Pattern	Description	Example output
`#,##0`	Number with thousands separator, no decimals	1,234
`#,##0.00`	Number with thousands separator, 2 decimals	1,234.56
`0%`	Percentage, no decimals	75%
`0.00%`	Percentage, 2 decimals	75.00%
`0.00E+00`	Scientific notation	1.23E+03
`#,##0 "units"`	Number with literal text suffix	1,234 units
`#,##0.0,`	Divide by 1,000 (trailing comma)	1.2
`#,##0.0,,"M"`	Divide by 1,000,000 with suffix	1.2M

Semicolon-separated sections

Excel-style format strings support up to four sections separated by semicolons, applied in order: positive values; negative values; zero; text. You can omit trailing sections — for example, two sections apply the first to positive values and the second to negative values.

format: '"🚀 "0.0;"📉 "-0.0;0'

This renders:

Positive values as 🚀 1.5
Negative values as 📉 -1.5
Zero as 0

Use quoted YAML strings so " and ; parse correctly.

Mustache field references

Excel-style strings can include Mustache references to other field values using {{view_name.field_name.property}}. This lets you build format strings that incorporate values from other fields in the query, such as a dynamic currency symbol. Supported properties:

Property	Description
`value`	The formatted field value
`raw`	The unformatted field value
`value_url_encoded`	The URL-encoded formatted value

Dynamic currency symbol from another field

revenue:
  sql: '"amount"'
  aggregate_type: sum
  format: '#,##0.00 "{{orders.currency_symbol.value}}"'

Referencing other fields in a format string implicitly requires those fields to be included in the query results. Omni will automatically add them, which may change the GROUP BY clause of the generated SQL.

Mustache templates

Any format string — named, Excel-style, or a conditional branch — can embed Mustache references to other fields in the row using {{view_name.field_name.property}} syntax. Omni resolves these references at display time before applying the format. What happens with the resolved string depends on the field type:

Field type	What Omni does with the resolved format string
Number	Uses it as the Excel-style format string for the number. Use this to embed a dynamic symbol or suffix — for example, a per-row currency symbol.
Date/time	Uses it as the strftime format string, but only if it contains `%`. Resolved strings without `%` are silently ignored.
All other types (string, boolean, etc.)	Returns the resolved string directly as the field’s display value. Use this to build a computed label from multiple fields.

Dynamic currency symbol for a number (embed in format string)

measures:
  revenue:
    sql: '"amount"'
    aggregate_type: sum
    format: '"{{orders.currency_symbol.value}}" #,###.00'

Dynamic currency symbol as a conditional else branch

measures:
  total_value:
    format:
      depends_on:
        filter: reporting_currency_filter
        conditions:
          - condition:
              is: converted_EUR
            value: eurcurrency
          - condition:
              is: converted_USD
            value: usdcurrency
      else: '"{{order_items.reporting_currency_symbol.value}}" #,###.00'

Computed display value for a string field

dimensions:
  location:
    sql: '"city"'
    format: '{{orders.city.value}}, {{orders.country.value}}'

When used on a string or other non-number field, the resolved string is returned as plain text — it is not processed as Markdown HTML. If you need Markdown rendering in table cells (bold text, links, images), use the markdown parameter instead.

Mustache templates in format can be made reusable using custom_formats or constants, the same as any other format string.

Conditional formats

Conditional formats apply different format strings depending on a runtime value — for example, formatting a revenue measure with the right currency symbol based on a currency_code dimension, or showing different decimal precision based on a user attribute. Define a depends_on source and an ordered list of conditions using Omni filter syntax:

Source	Resolves at	Use when
`field`	Display time (per row)	The format depends on a value in the query results
`user_attribute`	Query time	The format depends on the user running the query
`filter`	Query time	The format depends on a filter applied to the query

The first matching condition determines the format. If no condition matches, the else format is used. See the custom_formats documentation for a complete list of properties.

Conditional format based on a field value

dimensions:
  currency_code:
    sql: '"currency_code"'
measures:
  revenue:
    sql: '"amount"'
    aggregate_type: sum
    format:
      depends_on:
        field: currency_code
        conditions:
          - condition:
              is: "USD"
            value: usdcurrency_0
          - condition:
              is: "GBP"
            value: gbpcurrency_0
        else: currency_0

Conditional format with numeric conditions

dimensions:
  age:
    sql: '"age"'
measures:
  total:
    sql: '"amount"'
    aggregate_type: sum
    format:
      depends_on:
        field: age
        conditions:
          - condition:
              greater_than: "18"
            value: "#,##0.00"
          - condition:
              between:
                - 10
                - 18
            value: "#,##0"
        else: "0"

Conditional format based on a user attribute

revenue:
  sql: '"amount"'
  aggregate_type: sum
  format:
    depends_on:
      user_attribute: preferred_currency
      conditions:
        - condition:
            is: "USD"
          value: usdcurrency_2
        - condition:
            is: "EUR"
          value: eurcurrency_2
      else: currency_2

Conditional format based on a filter value

revenue:
  sql: '"amount"'
  aggregate_type: sum
  format:
    depends_on:
      filter: currency_filter
      conditions:
        - condition:
            is: "USD"
          value: usdcurrency_2
        - condition:
            is: "GBP"
          value: gbpcurrency_2
      else: currency_2

Branch formats referencing model constants

constants:
  high_precision:
    value: "#,##0.0000"
  low_precision:
    value: "#,##0.0"

measures:
  measurement:
    sql: '"value"'
    aggregate_type: avg
    format:
      depends_on:
        field: unit_type
        conditions:
          - condition:
              is: "scientific"
            value: "@{high_precision}"
          - condition:
              is: "summary"
            value: "@{low_precision}"
      else: "@{low_precision}"

Reporting currency filter driving named formats

filters:
  reporting_currency:
    type: string
    suggestion_list:
      - value: converted_EUR
        label: Converted to EUR
      - value: converted_USD
        label: Converted to USD

measures:
  revenue_display:
    sql: '"amount"'
    aggregate_type: sum
    format:
      depends_on:
        filter: reporting_currency
        conditions:
          - condition:
              is: converted_EUR
            value: eurcurrency
          - condition:
              is: converted_USD
            value: usdcurrency
      else: usdcurrency

Reusable formats (`custom_formats`)

Reusable formats are format objects defined in a model file using the custom_formats parameter. They let you centrally manage format definitions and apply them consistently across multiple dimensions and measures. Reusable formats can include any valid format type — named formats, Excel-style strings, Mustache templates, or conditional formats.

Referencing custom formats

Reference a reusable format using either a plain string or explicit object syntax: Plain string (recommended):

<field_name>:
  format: <custom_format_name>

Explicit object:

<field_name>:
  format:
    custom_format: <custom_format_name>

See the custom_formats parameter page for syntax, properties, and examples.

Locale and number formatting

The default_numeric_locale model parameter controls how all formatted numbers are displayed — including the thousands delimiter, decimal delimiter, and default currency symbol. When you set a locale, every named format and Excel-style string in that model respects the locale’s conventions automatically.

Locale	Thousands	Decimal	Default currency	`currency_2` renders as
`en_US` (default)	`,`	`.`	`$`	$1,234.50
`en_GB`	`,`	`.`	`£`	£1,234.50
`fr_FR`	(space)	`,`	`€`	€1 234,50
`de_DE`	`.`	`,`	`€`	€1.234,50
`ja_JP`	`,`	`.`	`¥`	¥1,234.50
`br_BR`	`.`	`,`	`R$`	R$1.234,50
`nl_NL`	`.`	`,`	`€`	€1.234,50

Locale interaction with format types

Named formats (currency_2, accounting_0, etc.) — The locale’s default currency symbol is used when no currency prefix is specified. Prefixed formats like usdcurrency_2 override the locale’s default symbol.
Excel-style strings (#,##0.00) — the #, ,, . tokens are locale-aware. A format of #,##0.00 renders 1.234,50 under de_DE.
Currency-prefixed named formats (gbpcurrency_2, jpyaccounting_0) — Always use the specified currency symbol, regardless of locale. The delimiter and decimal conventions still come from the locale.

Durations and wallclock-style times

Timestamp dimensions should use strftime-style strings (they must include % in the format). See Time formats. For numeric durations stored as milliseconds (or seconds) that you want to display as hours, minutes, or seconds:

Convert the raw value to a fraction of a day (divide milliseconds by 86400000). This will get the fraction that Excel-style time format codes expect.

Apply a format string using either:

An Excel-style string (possibly through a model constant), or
A conditional format that picks a shorter or longer pattern based on how large the duration is (for example, show h:mm:ss when duration is at least 1 hour, otherwise m:ss).

h displays clock hours (wraps at 24). If your durations can exceed 24 hours, use [h] for elapsed hours — for example, [h]:mm:ss renders 25 hours as 25:00:00 instead of 1:00:00. The same applies to [m] for elapsed minutes.

Duration dimension: scale ms to Excel day fraction

dimensions:
  total_duration_ms:
    sql: '"total_duration_ms"'

  duration_as_excel_day:
    sql: ${total_duration_ms} / 86400000.0
    label: Total duration
    format:
      depends_on:
        field: total_duration_ms
        conditions:
          - condition:
              greater_than_or_equal_to: 3600000
            value: 'h "hr" mm "min" ss.0 "sec"'
          - condition:
              greater_than_or_equal_to: 60000
            value: 'm "min" ss.00 "sec"'
      else: 'ss.000 "sec"'

Adjust the thresholds (3600000 ms = 1 hour, 60000 ms = 1 minute) to control when the display switches between hour-, minute-, and second-scale patterns.

Getting started

Manage content

Analyze & build

Visualize & present

Deliver content

Model data

Manage integrations

Common questions

Where to define formats

Choosing a format type

Named formats

Numeric formats

Currency formats

Time formats

Excel-style strings

Semicolon-separated sections

Mustache field references

Mustache templates

Conditional formats

Reusable formats (`custom_formats`)

Referencing custom formats

Locale and number formatting

Locale interaction with format types

Durations and wallclock-style times

Getting started

Manage content

Analyze & build

Visualize & present

Deliver content

Model data

Manage integrations

​Common questions

​Where to define formats

​Choosing a format type

​Named formats

​Numeric formats

​Currency formats

​Time formats

​Excel-style strings

​Semicolon-separated sections

​Mustache field references

​Mustache templates

​Conditional formats

​Reusable formats (custom_formats)

​Referencing custom formats

​Locale and number formatting

​Locale interaction with format types

​Durations and wallclock-style times

Common questions

Where to define formats

Choosing a format type

Named formats

Numeric formats

Currency formats

Time formats

Excel-style strings

Semicolon-separated sections

Mustache field references

Mustache templates

Conditional formats

Reusable formats (`custom_formats`)

Referencing custom formats

Locale and number formatting

Locale interaction with format types

Durations and wallclock-style times