Expressions

Expressions let you manipulate, format and parameterize data in SquaredUp. They are written in JavaScript and entered between mustache-style placeholders {{ }}.

The same expression syntax is used throughout SquaredUp, but the purpose and available context depend on where an expression is used.

Where expressions can be used

Expressions are used in the following areas.

Expressions in columns

When editing a column, use a format expression to control how its value is displayed.

A format expression is cosmetic and does not change the underlying value. Sorting and aggregation continue to use the original value.

Examples

• Perform a calculation:
  Divides the value in myColumn by 1,000.
  
  Copy

  {{ $['myColumn'] / 1000 }}

• Combine text and values from multiple columns:
  Inserts values from two columns into a single display string.
  
  Copy

  Progress: {{ $['myColumn'] }} out of {{ $['myColumnTwo'] }}

• Modify a string:
  Returns the first 20 characters of myColumn.
  
  Copy

  {{ $['myColumn'].substring(0, 20) }}

Expressions in custom columns

When creating a custom column, use a value expression to create a new value by manipulating the available data.

A value expression defines the underlying value of the column. You can then apply a format expression to control how that value is displayed.

Examples

• Create a URL column:
  Creates a URL containing the current row’s id value as a query parameter.
  
  Copy

  https://example.com?ticketId={{ $['id'] }}

• Create a state value:
  Returns success when the value is at least 100; otherwise, returns error.
  
  Copy

  {{ $['value'] >= 100 ? 'success' : 'error' }}

• Create a Boolean value:
  Returns true when the log contains ERROR; otherwise, returns false.
  
  Copy

  {{ $['log'].includes('ERROR') }}

Expressions in data stream parameters

When configuring a data stream on the Parameters tab of the tile editor, use parameter expressions to insert dynamic values, such as timeframes and dashboard variables, into supported fields.

Parameter expressions allow a single configuration to adapt automatically to its context instead of relying on hard-coded values.

Fields which allow parameter expressions are denoted by the Insert expression

Use the dashboard timeframe

Inserts the dashboard’s current timeframe into a supported parameter.

{{timeframe}}

Use a dashboard variable

Inserts the current value of a dashboard variable into a supported parameter.

{{variable1}}

Does this single data value = this single-select variable value?

property = '{{ variable1.property }}

This is a simple example, for a single-select variable, where you can just do equals.

SELECT
  *
FROM
  "github_open_prs"
WHERE
  '{{ variable1[0].rawId}}' = repo_id

Does this single data value appear in this collection of variable values?

list_contains(['{{ variable1.map(v => v.property).join("','") }}'], property)

A multi-select variable where you want to compare a value in the dataset with a collection of values (1, many, all etc) coming from the variable.

SELECT
  *
FROM
  "github_open_prs"
WHERE
  list_contains(['{{ variable1.map(v => v.rawId).join("','") }}'], repo_id)

-- 3 objects: list_contains(['243313865','243060753','858611084'], '858611084') -> TRUE
-- 2 objects: list_contains(['243313865','243060753'], '858611084') -> FALSE
-- 1 objects: list_contains(['858611084'], '858611084') -> TRUE

Does this array data value overlap with this collection of variable values?

list_has_any(['{{ variable1.map(v => v.property).join("','") }}'],property::json::VARCHAR[])

When the property column in the data is a string, but it looks like an array i.e.
["property/1234"]

SELECT
    "label",
    SUM("value") AS "total_value"
FROM
  "google_analytics_ga4_service_account_all"
WHERE
    list_has_any(
      ['{{ variable1.map(v => v.sourceId).join("','") }}'],
      property::JSON::VARCHAR[]
    )
GROUP BY
    "label"
ORDER BY
    "total_value" DESC

Writing expressions

Expressions use Mustache-style placeholders {{ }} that contain JavaScript. Within a placeholder, you can access the current row, all rows in the dataset, the current row index, and other available context.

Accessing data

You can access data and context using the following variables:

IntelliSense

The editor used for inputting an expression provides IntelliSense support, making suggestions and showing which commands / properties are available.

IntelliSense is automatically triggered as you type, or can be manually triggered using Ctrl+Space.

Error handling and troubleshooting

If a placeholder throws an error or contains invalid JavaScript syntax, the error message is captured and used as the output of the placeholder for debugging purposes.

Examples

• Combine text and values from multiple columns:
  Inserts values from two columns into a single display string.
  Copy

  Progress: {{ $['myColumn'] }} out of {{ $['myColumnTwo'] }}

  
  If myColumn is 10 and myColumnTwo is 20, the expression displays:
  Copy

  Progress: 10 out of 20

• Format a value with surrounding text:
  Adds a label and unit to a column value.
  
  Copy

  Response time: {{ $['duration'] }} ms

  
  If duration is 250, the expression displays:
  
  Copy

  Response time: 250 ms

How to use expressions with columns

1. From the tile editor, select the Columns tab.
2. Do one of the following:
   • To apply a format expression to an existing column, click on the column you want to edit.
   • To create a new column using a value expression, click Add
     then select Custom.
3. Configure the following fields:
   1. Name:
      Enter a name for the column.
   2. Type:
      Select the data type of the column. Depending on the data type you select this enables additional configuration fields.
   3. Value expression:
      Only displays when editing a custom column. Enter a custom expression to manipulate the actual value of the column.
4. To apply a format expression, do the following:
   1. Select Custom from the Formatting section.
   2. Enter a custom expression in the Format Expression field to cosmetically change how the value displays.
      

5. Click Save.

Was this article helpful?