Templated Filters and Parameters

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 automatically provides users with the ability to manipulate their queries by creating filters, which are based on dimensions and measures. While this simple method meets many use cases, it can’t enable every analytical need. Templated Filters and Parameters vastly expand the possible use cases you can support.

From a SQL perspective, dimensions and measures can only alter the outermost WHERE or HAVING clauses in your query. However, you might find that you want to let users manipulate other parts of the SQL. Adjusting part of a derived table, adjusting which database table gets queried, or creating multi-purpose dimensions and filters are just some of the features you can enable with Templated Filters and Parameters.

Examples

Let’s look at a few examples to demonstrate the value of Templated Filters and Parameters.

Making a Dynamic Derived Table with Templated Filters

Consider a derived table that calculates customer’s lifetime spend, within the northeast region:

- view: customer_facts derived_table: sql: | SELECT customer_id, -- Can be made a dimension SUM(sale_price) AS lifetime_spend, -- Can be made a dimension FROM order WHERE region = 'northeast' -- Can NOT be made a dimension
view: customer_facts { derived_table: { sql: SELECT customer_id, -- Can be made a dimension SUM(sale_price) AS lifetime_spend, -- Can be made a dimension FROM order WHERE region = 'northeast' -- Can NOT be made a dimension ;; } }

In this query, you can create dimensions from customer_id and lifetime_spend. However, suppose you wanted the user to be able to specify the region, instead of hard-coding it to “northeast”. The region cannot be exposed as a dimension, and therefore the user cannot filter on it as normal.

One option would be to use a Templated Filter, which would look like this:

- view: customer_facts derived_table: sql: | SELECT customer_id, SUM(sale_price) AS lifetime_spend, FROM order WHERE {% condition order_region %} orders.region {% endcondition %}   fields: - filter: order_region type: string
view: customer_facts { derived_table: { sql: SELECT customer_id, SUM(sale_price) AS lifetime_spend, FROM order WHERE {% condition order_region %} orders.region {% endcondition %} ;; }   filter: order_region { type: string } }

Read more below for step-by-step instructions.

Making a Dynamic Measure with Parameters

Consider a filtered measure that adds up the number of pants sold:

- measure: pants_count type: count filters: category: 'pants'
measure: pants_count { type: count filters: { field: category value: "pants" } }

This is straightforward, but if there were dozens of categories, it would be tedious to create a measure for each. Furthermore, it may clutter the Explore experience for users.

An alternative would be to create a dynamic measure like this:

- measure: category_count type: sum sql: | CASE WHEN ${category} = '{% parameter category_to_count %}' THEN 1 ELSE 0 END   - parameter: category_to_count type: string
measure: category_count { type: sum sql: CASE WHEN ${category} = '{% parameter category_to_count %}' THEN 1 ELSE 0 END ;; }   filter: category_to_count { type: string }

Read more below for step-by-step instructions.

Basic Usage

Step One: Create Something for the User to Interact With

The first step to using a Templated Filter or Parameter is to create a field for the user to interact with.

  • For Templated Filters, add a filter
  • For Parameters, add a parameter

In either case, these fields will appear to the user under the “Filter-Only Fields” section of the Field Picker:

Both filter and parameter fields can accept a series of child parameters, allowing you to customize how they operate. See the Field Reference for a complete list. There are two options that bear special mentioning for parameter fields.

First, parameter fields can have a special type called unquoted:

- parameter: table_name type: unquoted
parameter: table_name { type: unquoted }

This type allows values to be inserted into SQL without being enclosed in quotes, as a string would be. This can be useful when you need to insert SQL values such as table names.

Second, parameter fields have an option called allowed values that let you associate a user-friendly name with the value you want to insert. For example:

- parameter: number_of_results type: string allowed_values: - value: '< 500' label: 'Less than 500' - value: '< 10000' label: 'Less than 10,000' - value: '> 0' label: 'All Results'
parameter: number_of_results { type: string allowed_value: { label: "Less than 500" value: "< 500" } allowed_value: { label: "Less than 10,000" value: "< 10000" } allowed_value: { label: "All Results" value: "> 0" } }

Step Two: Apply the User Input

The second step is to use liquid to add the Templated Filter or Parameter as desired.

Templated Filters

The syntax for Templated Filters breaks down like this:

{% condition filter_name %} sql_or_lookml_reference {% endcondition %}
  • The word condition and endcondition never change
  • Replace filter_name with the filter name you created in the first step
  • Replace sql_or_lookml_reference with the SQL or LookML that should be set “equal” to the user input (read more detail below)

In the example above we used:

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

The interaction between the liquid 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 “2012-01-01 to 2014-12-31” into the order_region filter, Looker would turn these tags into: orders.region BETWEEN '2012-01-01' AND '2014-12-31'. In other words, Looker understands the user input and generates the appropriate logical expression.

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.

Parameters

The syntax for Parameters breaks down like this:

{% parameter parameter_name %}
  • The word parameter never changes
  • Replace parameter_name with the parameter name you created in the first step

In the example above we used:

{% parameter category_to_count %}

Choosing Between Templated Filters and Parameters

Although Templated Filters and Parameters are similar, there is an important difference between them:

  • Parameters insert user input directly (or using the values you define with allowed values)
  • Templated Filters insert values as a logical statement, as described above

So in situations where you want to offer users more flexible input (such as various kinds of date ranges or string searches), you should try to use Templated Filters when possible. Looker can interpret the user input and write the appropriate SQL behind the scenes. This prevents you from having to account for every possible type of user input.

In situations where a logical statement can’t be inserted, or where you know a finite set of options the user might enter, use Parameters.

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