Templated Filters

LookML
Version

On this Page
Docs Menu

Please note: this is an advanced topic that assumes a good, pre-existing knowledge of SQL and LookML

Looker enables users to restrict Looker queries by applying filters to dimension and measure fields. When these filters are applied to the underlying SQL that Looker generates, they are only added to the outermost WHERE or HAVING clause. While this works in most cases, there may be times when you want to apply filters to other parts of the SQL, such as in a sub-query. In these situations, you can use "Templated Filters".

These templated filters can be used in derived tables, as well as dimensions, measures, and filter fields. This page explains the concept starting with derived tables. Afterwards, check out the other options in the Advanced Usage of Templated Filters section.

Example

To make this concept more clear, consider a derived table that has the following query. This query generates a list of total sales, by date, for orders sold in the northeast. It then generates a one week rolling average of those daily sales:

SELECT order_date, -- Can be made a dimension total_sales, -- Can be made a dimension AVG(total_sales) OVER( ORDER BY order_date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW ) AS one_week_rolling_average -- Can be made a dimension FROM ( SELECT order_date, SUM(order_amount) AS total_sales FROM orders WHERE region = 'northeast' -- Cannot be made a dimension GROUP BY order_date ) AS northeast_daily_sales

In this query, you can create dimensions from order_date, total_sales, and one_week_rolling_average. If a user added filters to these dimensions, they'd be added to the outermost WHERE clause. However, suppose you wanted the user to be able to specify the region that is contained within the northeast_daily_sales sub-query. The region cannot be exposed as a dimension, and therefore the user cannot filter on it as normal. Instead, you'll need to use Templated Filters. Here is the query again showing where the different types of filters would be applied:

SELECT order_date, total_sales, AVG(total_sales) OVER( ORDER BY order_date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW ) AS one_week_rolling_average FROM ( SELECT order_date, SUM(order_amount) AS total_sales FROM orders --------------------------------------- WHERE -- Templated Filters region = 'northeast' -- needed here --------------------------------------- GROUP BY order_date ) AS northeast_daily_sales --------------------------------------- WHERE -- order_date > '2012-01-01' AND -- Dimension filters total_sales > 1000.00 AND -- applied here one_week_rolling_average > 1000.00 -- ---------------------------------------

Basic Usage of Templated Filters

Step One: Create a Filter

The first step to using a Templated Filter is to create a filter field. A filter field will typically be added to a view file as a child of fields, in the same way as a dimension or measure. It has this syntax:

- filter: filter_name label: 'Optional Label Name' type: number | datetime | date | string
filter: filter_name { label: "Optional Label Name" type: number | datetime | date | string }

In our specific example we want the user to specify a region. Filters default to a type of string, so we don't need to explicilty state a type. Therefore, our filter would be:

- filter: order_region label: 'FILTER Order Region'
filter: order_region { label: "FILTER Order Region" }

If desired, you can also have a filter provide suggestions to the user in the same way that a dimension makes suggestions. To do so, you'll need to make use of the suggestions or suggest_dimension parameter. If you want to hardcode a list of suggestions, use suggestions like this:

- filter: order_region label: 'FILTER Order Region' suggestions: [northeast, southeast, midwest, southwest, northwest]
filter: order_region { label: "FILTER Order Region" suggestions: ["northeast", "southeast", "midwest", "southwest", "northwest"] }

If you want the suggestions to be based on another dimension, use suggest_dimension (and possibly suggest_explore) like this:

- filter: order_region label: 'FILTER Order Region' suggest_explore: order suggest_dimension: region
filter: order_region { label: "FILTER Order Region" suggest_explore: order suggest_dimension: region }

Finally, you may also define a default value, using the default_value parameter. This is differentiated from a suggestion in that it will automatically be applied when the user selects the filter, rather than requiring the user to begin typing.

- filter: order_region label: 'FILTER Order Region' default_value: northeast
filter: order_region { label: "FILTER Order Region" default_value: "northeast" }

Step Two: Apply the Filter to the SQL

The second step is to add the Templated Filter tags into the SQL. The syntax for these tags breaks down like this:

{% condition filter_name %} sql_or_lookml_reference {% endcondition %}

In our specific case, we would use:

{% condition order_region %} orders.region {% endcondition %}

The full SQL for our derived table, including the Templated Filter tags, would look like this:

SELECT order_date, total_sales, AVG(total_sales) OVER( ORDER BY order_date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW ) AS one_week_rolling_average FROM ( SELECT order_date, SUM(order_amount) AS total_sales FROM orders WHERE {% condition order_region %} orders.region {% endcondition %} GROUP BY order_date ) AS northeast_daily_sales -- Since this query now works for all regions, -- you may want to change the name of the -- "northeast_daily_sales" sub-query to -- "regional_daily_sales"

Understanding the Effect of the Filter Tags

The interaction between the filter tags and the SQL you write in between them is important to understand. The Templated Filter tags are always transformed into a logical expression. For example, if the user entered "x" into the order_region filter example above, Looker would turn the tags into: orders.region = 'x'

This is often a point of confusion with developers. Templated Filters always result in a logical expression of some kind, and not the individual value entered by a user. This means that if the user enters an "x", you cannot simply insert an "x" all by itself into your SQL.

As another example, if the user entered "2012-01-01 to 2014-12-31" into the order_region filter, Looker would turn the tags into: orders.region BETWEEN '2012-01-01' AND '2014-12-31'. Thus, Looker understands the user input and generates the appropriate logical expression.

The filter definition above showed that you can provide a default value for the filter, such as default_value: "northeast". Refer to Looker's filter notation for details on how to write valid filter expressions for the default_value parameter.

If the filter does not have a default value, and the user does not provide a value, Looker inserts 1=1 in its place so that the WHERE clause is syntactically valid but has no effect on the query.

Advanced Usage of Templated Filters

A Templated Filter Tag Can Reference a Dimension Instead of a Filter

In the example above, we created a filter field to accept user input:

- filter: my_filter_name
filter: my_filter_name { ... }

And then used it in the derived table query like this:

{% condition my_filter_name %} ...

However, you can also use the input from a normal dimension filter in a Templated Filter tag. For example, you can define a dimension:

- dimension: my_dimension_name
dimension: my_dimension_name { ... }

And then apply it to the derived table query the same way:

{% condition my_dimension_name %} ...

If you do this, the normal dimension behavior happens; the user can apply a filter on the dimension and the query is modified accordingly. However, the dimension's filter will also be applied to anywhere you used the {% condition my_dimension_name %} Templated Filter tags.

A Templated Filter Can Go in a Dimension Instead of a Derived Table

The initial example on this page used a Templated Filter in the sql of a derived table. You can also use Templated Filter's in the sql 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
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 ;; }

This example enables dynamic grouping, which is particularly useful for comparisons between an individual or set of items, compared to the rest of the population. Here the user specifies a brand they are interested in, using the brand_select filter. The brand_comparitor dimension then groups the data into rows for that brand versus rows for everything else.

A Templated Filter Can Go in a Measure Instead of a Derived Table

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. There could also be new types added to the data in the future. Instead, 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
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 example would enable the user to specify the desired type to count, then receive the result in type count.

Templated Filters Can Take a sql Parameter

Templated Filters can also be used with a sql parameter. This enables you to apply conditions directly to the WHERE clause, in ways where a normal dimension filter may not be desired.

For example, suppose you had a customer table, and a corresponding addresses table where there can be more than one address per customer. If you only wanted to look at customers who had an address from a specific state, there are a few approaches you could take:

  • Join addresses to customer, 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 that 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 best approach would be to use a Templated Filter. For example, here 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 %} )
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're referring to the filter where the tag is used.

Still have questions?
Go to Discourse - or - Email Support
Top