> ## Documentation Index > Fetch the complete documentation index at: https://documentation.kodelabs.com/llms.txt > Use this file to discover all available pages before exploring further. # Calculated fields > Calculated columns, metrics, and URLs you can add to a Building BI data source, including how to create them, data types, and visibility options. Calculated fields add custom logic to a [data source](/products/building-bi/data-sources): calculated columns, calculated metrics, and calculated URLs. SQL must match the source database dialect — [PostgreSQL](https://www.postgresql.org/docs/current/), [ClickHouse](https://clickhouse.com/docs/sql-reference), or [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax). For worked expressions, Real-Time metadata syntax, and examples, see [Calculated columns, metrics, and URLs](/products/building-bi/data-sources#calculated-columns-metrics-and-urls). Editing a calculated column, metric, or URL affects all dashboards portfolio-wide. Always validate logic before you apply changes. Native columns cannot be edited. They are the building blocks that calculations reference. ## Add a calculated field You can create a calculated field from Building BI `Data Sources` or from a chart in the dashboard editor. Both paths open the same `Add Column` form. Open `Data Sources` from the bottom of the Building BI left navigation tray. Do not use the Cloud BMS `Data Sources` button. That page manages connectors, not Building BI tables. See [Data source types](/products/building-bi/reference/data-source-types). ### From Data Sources In Building BI, select `Data Sources` at the bottom of the left navigation tray. Select the data source you want to extend. Select `+ Add Column` in the upper right of the schema view. Choose the field type, then set name, display name, description, data type, and SQL expression. Optionally enable `Hidden` or `Sensitive Values` on the field. | Field | Description | | ------------------ | ---------------------------------------------------------------------------------------------------------------------------------- | | Type | `Calculated`, `Metric`, or `URL`. | | Name | Unique identifier within the data source. Use this name in SQL and chart configuration. | | Display name | Label shown in Building BI. Can differ from `Name`. | | Description | Optional summary of the field's purpose. | | Data type | Number (`DOUBLE_VALUE`), String, Date or DateTime, or Boolean. Required for calculated columns and metrics. | | SQL expression | Logic for the field, written in the database dialect of the source. | | `Hidden` | Hides the field from the available columns list in Building BI. | | `Sensitive Values` | Shows the column name instead of the value. Sensitive fields do not transfer when you [template](/products/building-bi/templates). | Add Column dialog with Calculated selected, Name and Display Name fields, Description, Data Type dropdown, SQL Expression editor, and Create button For schema tabs, samples, and SQL examples, see [Data sources](/products/building-bi/data-sources#calculated-columns-metrics-and-urls). ### From the chart editor While editing a chart, open Chart Parameters `Setup` and select `+ Add Column` (or the `+` control next to a field group such as Metrics). The same portfolio-wide field form opens. Building BI dashboard editor with Chart Parameters Setup open, showing Metrics with formula indicators and the control to add a new calculated field You cannot edit an existing calculated field's SQL from the chart `fx` popup. Open the field from `Data Sources` to change the expression. See [Chart setup parameters](/products/building-bi/reference/chart-setup-parameters). ## Calculated columns Calculated columns work like spreadsheet formulas. They apply logic to each row using only that row's data. Common patterns include extracting time parts (for example, `toHour(date_time_local)`) and isolating one sensor with conditional logic. Use exact database column names (for example, `building_name`, not `"Building Name"`). See [Point time series column reference](/products/building-bi/data-sources#column-reference). On Point Time Series Real-Time (ClickHouse), wrap metadata fields such as `building_name`, `device_name`, or `ontology_point_field` in `dictGetString(...)`. Last Value tables do not need the wrapper. | Table | Metadata syntax required? | | ---------------------------------------- | --------------------------------------------------- | | Point Time Series Real-Time (ClickHouse) | Yes — wrap metadata fields in `dictGetString(...)`. | | Point Time Series Last Value (Virtual) | No — reference the metadata column directly. | Copy-ready Real-Time and Last Value examples are in [Data sources](/products/building-bi/data-sources#calculated-columns-metrics-and-urls). For why the tables differ, see [Real-Time vs Last Value](/products/building-bi/data-sources#key-data-sources). ## Calculated metrics Calculated metrics aggregate across multiple rows. The output recalculates based on the chart's grouping (for example, per hour or per device). You can filter inside the expression (for example, `sumIf`) or keep the metric simple and filter on the chart. See [Filter options](/products/building-bi/reference/filter-options) and worked examples in [Data sources](/products/building-bi/data-sources#calculated-columns-metrics-and-urls). As an alternative to a chart filter, put the condition in a single metric. For example: ```sql theme={"theme":{"light":"github-dark","dark":"github-dark"}} avgIf(number_value, device_name = 'AHU_2') ``` That metric returns the average only for matching rows. You do not need a separate chart filter for that device. Set a data type on every calculated metric. Non-Number metrics change where you can use the field: | Area | Behavior | | --------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Charts that need a numeric metric | String, DateTime, and Boolean metrics cannot be the metric in Bar (all), Line (all), Pie, Sunburst, Tree-map (primary metric), Boxplot, Scatter, Advanced Scatter, Gauge, Sankey, Slicer, Polar (all), or Heat-map. In Time-series they may appear in the tooltip only. See [Chart types](/products/building-bi/reference/chart-types). | | Top N filter | Only Number metrics (`DOUBLE` or `INT`) can be used in the Top N metric section. | | Formatting | String, Date, and Boolean metrics used as a table column or in a Number widget get type-appropriate formatting (for example, prefix or suffix for strings). | | Metric filters | Adapt to the metric's data type. See [Filter options](/products/building-bi/reference/filter-options). | ## Calculated URLs Calculated URLs turn row data into navigation actions. | URL type | Behavior | | -------- | --------------------------------------------------------------------------------------------------------------------------------- | | Internal | Pattern such as `/buildings/{building_id}`. Built automatically; validates IDs; navigates within Cloud BMS on desktop and mobile. | | External | Opens in a new tab to an external resource (for example, a CMMS or a specific work order). | To apply a URL on a table column, open the widget Style settings. Set the column's URL option (for example, `Device URL`) so each cell becomes a hyperlink to that record's page. See [Widget customization](/products/building-bi/widget-customization). Returned URL columns appear in the response prefixed with `INTERNAL_URL::` (for example, `INTERNAL_URL::building_url::building_name`). ### URLs on chart widgets You can assign a single URL column (`urlColumn`) to a dimension, breakdown dimension, hierarchy item, or the Time-series Key column. On charts with a URL applied, right-click a data point to open a context menu. The menu offers navigation to the linked Cloud BMS record (Building, Area, Device, and similar) and cross-filtering. Supported on Tables (Raw, Aggregated, Pivot), Bar (all), Line (all, at marker points), Pie, Sunburst, Heat-map, Tree-map, Scatter, Boxplot, Sankey (nodes only), Polar Bar (all), and Time-series. Chart definitions are in [Chart types](/products/building-bi/reference/chart-types). ## Columns vs metrics Calculated URLs create links. Columns and metrics are easier to confuse: columns run once per row; metrics aggregate across rows. | Feature | Calculated columns | Calculated metrics | | -------- | ------------------------------------------- | ---------------------------------------------------- | | Scope | Single row | Multiple rows | | Function | Applies logic to create a new virtual field | Aggregates data (`SUM`, `AVG`, `COUNT`, and similar) | | Behavior | Independent; does not aggregate | Recalculates based on chart grouping | The graphic below shows a calculated column that labels comfort on every row from that row's temperature. Graphic showing a calculated column Temp Comfort that labels each discharge air temperature row as Too Cold, Comfortable, or Too Hot using per-row CASE logic A calculated metric instead collapses many rows into one aggregated value for the chart. Graphic showing a calculated metric that averages temperature values for AHU 2 filtered on the dashboard, producing a single card value of 69.5 degrees Fahrenheit ## Database and syntax | Table or database | Syntax notes | | ---------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Real-Time (ClickHouse) | Raw data. Requires [ClickHouse](https://clickhouse.com/docs/sql-reference) syntax and `dictGetString` for metadata. See [Database backends](/products/building-bi/data-sources#database-types). | | Last Value (Virtual) | Pre-processed. Generally easier. No metadata wrapper. See [Physical vs virtual data sources](/products/building-bi/concepts/physical-vs-virtual-data-sources). | | PostgreSQL / BigQuery | Same logic, different phrasing. See [PostgreSQL](https://www.postgresql.org/docs/current/) and [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax). | ### Calculated field limitations Calculated columns and metrics in Building BI do not support: * Window functions * Aggregations of aggregations This is a calculated field limit, not a ClickHouse limit. ClickHouse virtual table SQL can use window functions. Put the logic in a [virtual table](/products/building-bi/concepts/physical-vs-virtual-data-sources) when you need window functions or aggregations of aggregations. ## See also * SQL examples and metadata patterns: [Data sources](/products/building-bi/data-sources#calculated-columns-metrics-and-urls) * Source and database types: [Data source types](/products/building-bi/reference/data-source-types) * Columns to reference: [Point time series column reference](/products/building-bi/data-sources#column-reference) * Real-Time vs Last Value: [Real-Time vs Last Value](/products/building-bi/data-sources#key-data-sources) * Chart field slots: [Chart setup parameters](/products/building-bi/reference/chart-setup-parameters) * Filters and Top N: [Filter options](/products/building-bi/reference/filter-options) ## Next steps Write calculated field SQL, including Real-Time metadata syntax and worked examples. See which charts accept metrics, columns, and URL navigation. ## Related reading Compare direct tables with derived virtual sources. Choose Real-Time or Last Value Point Time Series sources. Understand ClickHouse, PostgreSQL, and BigQuery trade-offs. Look up column names for calculations and filters.