Runtime filters

Runtime filters

Runtime filters provide the ability to apply filters on an embedded Liveboard or Answer using Visual Embed SDK. You can also apply runtime filters when querying data from a Liveboard, Answer, or visualization object via a REST API call.

Note

The runtime filters operation returns an error if the URL exceeds 2000 characters. Ensure that you add the runtime filter parameters before #/path in the URL, so that the parameters persist. For examples, see Apply runtime filters on a Liveboard.

How runtime filters work🔗

The runtime filters can be specified in the ThoughtSpot object URL as query parameters. When you apply a runtime filter to a Liveboard, ThoughtSpot will try to find a matching column in the Liveboard visualizations to filter data. The runtime filter requires the following attribute-value pairs.

column name

String. Name of the column to filter by. For example, item type or product. This attribute is defined as col1, col2, col3 in the URLs and as columnName in the runtimeFilters array in the Visual Embed SDK.

operator

String. The runtime filter operator. For example, EQ or IN. This attribute is defined as op1, op2, op3 in the object URLs and as operator in the runtimeFilters array in the Visual Embed SDK.

Supported runtime filter operators
OperatorDescriptionNumber of Values

EQ

equals

1

NE

not equal to

1

LT

less than

1

LE

less than or equal to

1

GT

greater than

1

GE

greater than or equal to

1

CONTAINS

contains

1

BEGINS_WITH

begins with

1

ENDS_WITH

ends with

1

BW_INC_MAX

between inclusive of the higher value

2

BW_INC_MIN

between inclusive of the lower value

2

BW_INC

between inclusive

2

BW

between non-inclusive

2

IN

is included in this list of values

multiple

values

String, Integer, or Boolean. The list of operands. For example, if the column name is defined as State, the value can be the name of the state like Michigan.

This attribute is defined as val1, val2, val3 in the object URLs and as values in the runtimeFilters array in the Visual Embed SDK.

Some operators like EQ, LE accept a single operand, whereas BW_INC_MAX, BW_INC_MIN, BW_INC, BW, and IN accept multiple operands.

Supported data types🔗

You can apply runtime filters on these data types:

  • VARCHAR

  • BIGINT

  • INT

  • FLOAT

  • DOUBLE

  • BOOLEAN

  • DATE

  • DATE_TIME

  • TIME

Important

For the DATE and DATE_TIME data types, you must provide the date and time values in the Epoch time format. The Epoch time is also referred to as POSIX or Unix time. Epoch time is an integer value representing the number of seconds elapsed since 1 JAN 1970 00:00:00 UTC. You may have to convert time zones to calculate the appropriate timestamp.

For example, if you want to filter data for 2020-05-22, you can specify the date value in the Epoch time format as 1590192000, or use the following JavaScript method to calculate the timestamp:

new Date('2020-05-22').getTime() / 1000

Runtime filters in Visual Embed SDK🔗

If you are embedding a Liveboard, visualization, or Answer using Visual Embed SDK, you can apply filters using the runtimeFilters property. In the full app embed mode, ThoughtSpot applies runtime filters on all Liveboard, visualization, and Answer objects in the embedded app.

The following example shows how to apply runtime filters on an embedded visualization in the SDK. Here, the runtime filter is operating on the Revenue column to filter the data matching 100000.

liveboardEmbed.render({
    liveboardId: '133e6c5f-e522-41a0-b0ad-b9c3b066e276',
    vizId: '28b73b4a-1341-4535-ab71-f76b6fe7bf92',
      runtimeFilters: [{
      columnName: 'Revenue',
      operator: RuntimeFilterOp.EQ,
      values: ['100000' ]
      }]
  });

Apply multiple runtime filters in the SDK🔗

The following examples show how to apply multiple runtime filters on Liveboard visualizations using the SDK:

liveboardEmbed.render({
    liveboardId: '543619d6-0015-4667-b257-eff547d13a12',
    runtimeFilters: [{
            columnName: 'item type', // eg: color
            operator: RuntimeFilterOp.EQ,
            values: ['Jackets'] // eg: red
        },
        {
            columnName: 'Region',
            operator: RuntimeFilterOp.IN,
            values: ['Midwest', 'East', 'West']
        },
        {
            columnName: 'Date',
            operator: RuntimeFilterOp.EQ,
            values: ['1656680400']
        }
    ]
});
Example video

The following video shows how to apply multiple runtime filters on a Liveboard.

Copy sample code Try it out in Playground

Runtime filter operator examples🔗

OperatorExample (Visual Embed SDK)

EQ
equals
Number of values allowed: 1

liveboardEmbed.render({
    liveboardId: '543619d6-0015-4667-b257-eff547d13a12',
    runtimeFilters: [{
        columnName: 'state',
        operator: RuntimeFilterOp.EQ,
        values: ['california']
    }]
});

NE
Not equal to or does not include
Number of values allowed: 1

liveboardEmbed.render({
    liveboardId: '543619d6-0015-4667-b257-eff547d13a12',
    runtimeFilters: [{
        columnName: 'item type',
        operator: RuntimeFilterOp.NE,
        values: ['jackets']
    }]
});

LT
less than
Number of values allowed: 1

liveboardEmbed.render({
    liveboardId: '543619d6-0015-4667-b257-eff547d13a12',
    runtimeFilters: [{
        columnName: 'revenue',
        operator: RuntimeFilterOp.LT,
        values: ['1000000']
    }]
});

LE
less than or equal to
Number of values allowed: 1

liveboardEmbed.render({
    liveboardId: '543619d6-0015-4667-b257-eff547d13a12',
    runtimeFilters: [{
        columnName: 'revenue',
        operator: RuntimeFilterOp.LE,
        values: ['5000000']
    }]
});

GT
greater than
Number of values allowed: 1

liveboardEmbed.render({
    liveboardId: '543619d6-0015-4667-b257-eff547d13a12',
    runtimeFilters: [{
        columnName: 'revenue',
        operator: RuntimeFilterOp.GT,
        values: ['1000000']
    }]
});

GE
greater than or equal to
Number of values allowed: 1

liveboardEmbed.render({
    liveboardId: '543619d6-0015-4667-b257-eff547d13a12',
    runtimeFilters: [{
        columnName: 'revenue',
        operator: RuntimeFilterOp.GE,
        values: ['5000000']
    }]
});

CONTAINS
contains
Number of values allowed: 1

liveboardEmbed.render({
    liveboardId: '543619d6-0015-4667-b257-eff547d13a12',
    runtimeFilters: [{
        columnName: 'item type',
        operator: RuntimeFilterOp.CONTAINS,
        values: ['Bags']
    }]
});

BEGINS_WITH
begins with
Number of values allowed: 1

liveboardEmbed.render({
    liveboardId: '543619d6-0015-4667-b257-eff547d13a12',
     runtimeFilters: [{
          columnName: 'product',
          operator: RuntimeFilterOp.BEGINS_WITH,
          values: ['travel']
     }],

});

ENDS_WITH
ends with
Number of values allowed: 1

liveboardEmbed.render({
    liveboardId: '543619d6-0015-4667-b257-eff547d13a12',
    runtimeFilters: [{
        columnName: 'item type',
        operator: RuntimeFilterOp.ENDS_WITH,
        values: ['shirts']
    }]
});

BW_INC_MAX
between inclusive of the higher value
Number of values allowed: 2

liveboardEmbed.render({
    liveboardId: '543619d6-0015-4667-b257-eff547d13a12',
    runtimeFilters: [{
        columnName: 'revenue',
        operator: RuntimeFilterOp.BW_INC_MAX,
        values: ['25','30']
    }]
});

BW_INC_MIN
between inclusive of the lower value
Number of values allowed: 2

liveboardEmbed.render({
    liveboardId: '543619d6-0015-4667-b257-eff547d13a12',
    runtimeFilters: [{
        columnName: 'revenue',
        operator: RuntimeFilterOp.BW_INC_MIN,
        values: ['25','50']
    }]
});

BW_INC
between inclusive
Number of values allowed: 2

liveboardEmbed.render({
    liveboardId: '543619d6-0015-4667-b257-eff547d13a12',
    runtimeFilters: [{
        columnName: 'sales',
        operator: RuntimeFilterOp.BW_INC,
        values: ['10','50']
    }]
});

BW
between non-inclusive
Number of values allowed: 2

liveboardEmbed.render({
    liveboardId: '543619d6-0015-4667-b257-eff547d13a12',
    runtimeFilters: [{
        columnName: 'sales',
        operator: RuntimeFilterOp.BW,
        values: ['25','50']
    }]
});

IN
is included in this list of values
Number of values allowed: multiple

liveboardEmbed.render({
    liveboardId: '543619d6-0015-4667-b257-eff547d13a12',
    runtimeFilters: [{
        columnName: 'item type',
        operator: RuntimeFilterOp.IN,
        values: ['jackets', 'bags', 'shirts']
    }]
});

SDK Events🔗

See UpdateRuntimeFilters and Filters in embedded UI.

Apply runtime filters via REST API v1 endpoints🔗

To apply runtime filters on a Liveboard object in a REST API request, add the runtime filters to the API request URL as shown here:

URL format
https://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/pinboarddata?id={Liveboard_id}&col1={column-name}&op1={operator}&val1={value}
Example
https://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/pinboarddata?id=e36ee65e-64be-436b-a29a-22d8998c4fae&col1=State&op1=EQ&val1=California

The following example shows how to apply a runtime filter on a visualization object of a Liveboard:

URL format
https://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/pinboarddata?id={Liveboard_id}&vizid={visualization_id}&col1={column-name}&op1={operator}&val1={value}
Example
https://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/pinboarddata?id=543619d6-0015-4667-b257-eff547d13a12&vizid=%5B%224ff5b939-453d-40ff-8fc2-a1d972047c86%22%5D&col1=State&op1=EQ&val1=California

The following is another example of a REST API request URL with a filter. Here the runtime filter is operating on the column Category and returning values that are equal to mfgr%2324.

https://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/pinboarddata?
id=e36ee65e-64be-436b-a29a-22d8998c4fae&col1=Category
&op1=EQ&val1=mfgr%2324

Apply additional filters🔗

You can add additional filters by incrementing the number at the end of each parameter in the runtime filter for each filter you add, for example, col2, op2, val2, and so on. To add additional filters on a particular column, you can specify multiple values by separating them with an ampersand (&) as shown in the example here:

val1=foo&val1=bar

You can also use the IN operator for multiple values, as shown in this example:

col1=<column_name>&op1=IN&val1=<value>&val1=<value>

The following example passes multiple variables to a single column as well as multiple columns. It shows that the data values are returned as epochs.

col1=region&op1=IN&val1=midwest&val1=south&val1=northeast&col2=date&op2=BET&val2=<epoch_start>&val2=<epoch_end>
Note

If the Liveboard or Answer you’re filtering already has one or more filters applied, the runtime filters will act as an AND condition. This means that the data returned must meet the conditions of all filters; those supplied in the runtime filter and those included in the Liveboard or visualization itself.

Apply runtime filters via REST API v2.0 endpoints🔗

The following v2.0 endpoints support runtime filters in REST API requests:

  • POST /api/rest/2.0/report/liveboard

    Allows downloading Liveboard data in PDF, XLSX, CSV, and PNG format

  • POST /api/rest/2.0/report/answer

    Allows downloading Answer data in PDF, XLSX, CSV, and PNG format

The following example shows the request body with runtime filter parameters:

Answer report
curl -X POST \
  --url 'https://{ThoughtSpot-Host}/api/rest/2.0/report/answer' \
  -H 'Authorization: Bearer {access-token} '\
  -H 'Content-Type: application/json' \
  --data-raw '{
  "metadata_identifier": "fa68ae91-7588-4136-bacd-d71fb12dda69",
  "file_format": "XLSX",
  "runtime_filter": {
    "col1": "item type",
    "op1": "EQ",
    "values": "Jackets"
  }
}'
Liveboard report
curl -X POST \
  --url 'https://{ThoughtSpot-Host}/api/rest/2.0/report/liveboard' \
  -H 'Authorization: Bearer {access-token} '\
  -H 'Content-Type: application/json' \
  --data-raw '{
  "metadata_identifier": "fa68ae91-7588-4136-bacd-d71fb12dda69",
  "file_format": "PDF",
  "runtime_filter": {
    "col1": "item type",
    "op1": "EQ",
    "values": "Jackets"
  }
}'

Apply runtime filters on embedded objects🔗

If you are embedding a Liveboard or visualization without using the Visual Embed SDK, you can append the filters in the embedded object URL as shown in these examples:

https://{ThoughtspotHost}/?embedApp=true#/embed/viz/{Liveboard_id}/{visualization_id}
&col1=State&op1=EQ&val1=michigan
https://{ThoughtspotHost}/?embedApp=true#/embed/viz/{Liveboard_id}/{visualization_id}
&col1=State&op1=EQ&val1=michigan&col2=product&op2=BEGINS_WITH&val2=Travel

Apply runtime filters on a Liveboard🔗

The following examples show the runtime filter query string in a Liveboard URL:

https://{ThoughtSpot-Host}/?col1=State&op1=EQ&val1=California#/pinboard/d084c256-e284-4fc4-b80c-111cb606449a

You can apply multiple filters in the same URL as shown in this example:

https://{ThoughtSpot-Host}/?col1=State&op1=EQ&val1=California&col2=product&op2=BEGINS_WITH&val2=Travel#/pinboard/d084c256-e284-4fc4-b80c-111cb606449a

Limitations of runtime filters🔗

  • The DATE and DATE_TIME data type must be specified as EPOCH time (Unix or POSIX time) in runtime filters.

  • Runtime filters do not work directly on top of tables. You must create a Worksheet if you want to use runtime filters. This means that the Liveboard or visualization on which you apply a runtime filter must be created on top of a Worksheet.

  • If the Worksheet was created from an Answer (it is an aggregated Worksheet), runtime filters will only work if the Answer was formed using a single Worksheet. If the Answer from which the Worksheet was created includes raw tables or joins multiple worksheets, you won’t be able to use runtime filters on it. This is because of the join path ambiguity that could result.

  • Runtime filters do not allow you to apply HAVING filters in the URL parameters.

  • You cannot apply a runtime filter on a Liveboard or visualization built from tables and worksheets that have chasm traps.