Direct Replacement Filters

👤 This documentation is intended for SQL Users.‍

In addition to using filters in the traditional [created_at=daterange] fashion, filter values can be selected using [filter_name]. These types of direct replacement filters are a great way to substitute bits of sql directly into the query while allowing users to choose the value from the dashboard in the classic filter style.

<div><UL>
<LI><a href="#UsingDirect">Using Direct Replacement Filters</a></LI>
<UL>
<LI><a href="#DynamicallySelecting">Dynamically Selecting Columns in the Query</a></LI>
<LI><a href="#PartialPhrase">Partial Phrase Replacement</a></LI>
</UL>
<LI><a href="#DefaultValues">Default Values</a></LI>
<UL>
<LI><a href="#PipeNotation">Pipe Notation</a></LI>
<LI><a href="#UsingDefaultValues">Using Default Values</a></LI>
</UL>
<LI><a href="#SingleValueLimitation">Single Value Limitation</a></LI>
<LI><a href="#FilterCasing">Filter Casing</a></LI>
<LI><a href="#DirectReplacementTitles">Direct Replacement Filters in Chart Titles</a></LI>
</UL></div>

<HR>

Using Direct Replacement Filters

There are a couple different ways a filter can be integrated into the query. Here are a few common examples.

Dynamically Selecting Columns in the Query

To allow users to dynamically choose the columns they want selected in the query, first set up the filter with the available column options.

In the query, reference the filter using square parentheses like so:

Partial Phrase Replacement

Another common case is to use direct replacement filters to change part of a phrase.

This can be achieved by creating a ranged filter:

With the final implementation looking like:

Creating a null filter might look like:

The implementation and the generated sql would look like:

Default Values

Aside from setting a default filter value directly on the dashboard, a default value can be set in a direct replacement filter directly in the query.

This default value will be used when no filter option is selected within the dashboard. This is helpful for direct replacement filters, where the SQL query can produce an error when no filter option is selected.

Pipe Notation

When implementing direct replacement filters in the queries, the pipe symbol '|' can used to define a filter default. The filter default is substituted when no filter value is selected on the dashboard. To specify a default, after the filter name include the pipe followed by the default value:

[filter_name|default_value]

Using Default Values

First, create the filter to be used in the query.

Next, use the pipe in the query to define the default value. When nothing is selected in the filer, the pipe notation takes seniority:

However, when a filter option, such as Network, is selected in the dashboard, the filter option will be replaced into the query:

Single Value Limitation

Many uses of direct replacement notation require only one filter value to be passed for the query to be run and will cause errors if more than one is selected. For filters that allow multiple options to be selected, this can be limited in the select chart by calling for only a single value in the direct replacement notation instance:

[single:filter_name]

When this notation is used, only the first option from multiple options selected will be used. The below query does not return anything because there are no rows where the gender column has the value "f, m':

The below query will return rows because only the first value, 'f' is used in the query which is a valid value that exists in the column:

Single Value Limitation can be combined with Default Value Pipe Notation:

[single:filter_name|default_value]

Filter Casing

Filter casing is case-sensitive for direct replacement filters. A filter within the SQL query with a lower-cased first letter will insert the original casing of the filter value. However, if the first letter is upper-cased, the inserted filter value will have an upper-cased first letter.

Direct Replacement Filters in Chart Titles

Direct replacement filters can be used in chart titles to help users identify the filters currently applied to the chart at the dashboard level. Here, filter casing is helpful to maintain properly cased titles.