The Problem
You want to create a dimension that cannot be selected in the UI, but you want to be able to filter on it.
The Solution
In Looker, you can do this with a clever application of templated filters, as detailed in the steps below:
1. First, hide the dimension in question using hidden: yes
. This means the dimension will not show up in the Explore.
dimension: foo { type: string hidden: yes sql: ${TABLE}.foo ;; }
2. Now, make a filter field. By itself, a field of type: filter
does nothing, but combined with templated filters, you can link this filter to your foo
dimension.
filter: filter_on_foo { type: string sql: {% condition filter_on_foo %} ${foo} {% endcondition %} ;; }
3. The sql
parameter of a filter field applies SQL directly to the WHERE
clause of the query. In this case, the parameter is saying to take the filter condition specified in the filter_on_foo
filter and apply it to the ${foo}
dimension.
This way, you can just show users filter_on_foo
to let them filter on foo
while still keeping the foo
dimension hidden.