Advanced Templated Filters


(Tig Newman) #1

This page assumes that you understand the Templated Filters and Parameters page in Looker Docs. Be sure to check out the parameter discussion on that page too since using a parameter field is a better approach for many use cases. However, the techniques discussed below are useful for a few use cases, such as making a derived table obey top-level filters for performance or aggregation reasons. This article displays new LookML automatically — click Old LookML if you need to see that code.

Referencing a Dimension Instead of a Filter

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

Old LookML
- filter: my_filter_name
New LookML
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:

Old LookML
- dimension: my_dimension_name
New LookML
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.

Adding a Templated Filter to a Dimension

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:

Old LookML
- 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
New LookML
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.

Adding a Templated Filter in 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. There could also be new types added to the data in the future. Instead, consider the following example:

Old LookML
- filter: type_to_count
 
- measure: type_count
  type: sum
  sql: |
    CASE
      WHEN {% condition type_to_count %} item.type {% endcondition %}
      THEN 1
      ELSE NULL
    END
New LookML
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.

Using a sql Parameter in a Templated Filter

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:

Old LookML
- 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 %}
    )
New LookML
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.