This page assumes you understand the Templated Filters and Parameters page in Looker Docs. Be sure to check out the parameter discussion on that page, because using a parameter field is a better approach for many use cases.
The advanced templated filter applications discussed below are useful for a few use cases, such as making a derived table obey top-level filters for performance or aggregation reasons, creating dynamic filtered dimensions and measures, and creating dynamic WHERE
clauses in queries.
Referencing a Dimension Instead of a Filter in Templated Filter Tags
In this example, we have a filter
field to accept user input:
filter: my_filter_name { ... }
This filter dynamically modifies the derived table's SQL, like so:
{% condition my_filter_name %} ...
Alternatively, a dimension filter can also be referenced in a templated filter tag. For example, you can define a dimension:
dimension: my_dimension_name { ... }
And apply it to the derived table query the same way:
{% condition my_dimension_name %} ...
In this case, the normal dimension behavior occurs; an end-user applies a filter condition on the dimension from an Explore, dashboard or Look, and the underlying SQL is modified accordingly. The dimension's filter will also be applied anywhere the {% condition my_dimension_name %}
templated filter tags are applied.
Applying a Templated Filter to a Dimension
In the example above, templated filters were used to dynamically modify the SQL of a derived table. Templated filters can also be used in the sql
parameter of a dimension. For example:
filter: brand_select { ... }
dimension: brand_comparitor {
sql:
CASE
WHEN {% condition brand_select %} ${products.brand_name} {% endcondition %}
THEN ${products.brand_name}
ELSE "All Other Brands"
END ;;
}
The result of this application is dynamic grouping, which is particularly useful for comparisons between an individual or set of items, compared to the rest of a population. Here the user leverages the brand_select
filter to specify the brand they are interested in. The brand_comparitor
dimension then groups the data into rows for that brand, versus rows for everything else.
Applying a Templated Filter to a Measure
You can also use Templated Filters in the sql
of a measure. For example, you might have many item types and want to enable the user to retrieve a count for a specific type. You could create a count measure for each item type, but this would become unwieldy for more than a few types. It might also be the case that new types could potentially be added to the data in the future.
Using templated filters in a measure is a simple way to resolve this issue. Consider the following example:
filter: type_to_count { ... }
measure: type_count {
type: sum
sql:
CASE
WHEN {% condition type_to_count %} item.type {% endcondition %}
THEN 1
ELSE NULL
END ;;
}
This application enables the user to select the desired type_to_count
value, and then see the resulting count of that value in the type_count
measure.
Using a
sql
Parameter in a Templated Filter
Templated Filters can also be used with a sql
parameter. This applies secified conditions directly to the WHERE
clause, whenever the filter has a value.
For example, suppose there is a customer
table and a corresponding addresses
table, where there can be more than one address per customer. If you only want to look at customers who have an address from a specific state, there are a few approaches to take:
- Join
addresses
tocustomer
, then filter on the address state.- However, doing so would result in a fanout, which is typically discouraged.
- Create an address fact table, which has something like a list of states the customer lived in.
- This might be a heavyweight solution for this one specific problem.
- Use a correlated sub-query to determine customers who live in certain states, if your database dialect supports sub-queries. This would require a dimension for each specific state you were interested in. If the data for the dimension had more than a few possible values, this technique would quickly become unwieldy.
The simplest approach in this case, given the options, would be to use a templated filter. For example, below, we enable users to specify which state they are interested in:
filter: has_address_from_state {
label: "FILTER Has Address from State"
sql:
EXISTS (
SELECT *
FROM addresses
WHERE addresses.user_id = user.id AND
{% condition %} state {% endcondition %}
) ;;
}
Note that in this case we have not specified a filter name in the {% condition %}
tag. When you do this, Looker will assume you are referring to the filter where the tag is used.