Use Cases
You may want the fields displayed in a dashboard or Look to be dynamic, based on a user's selection of the metrics or data granularity they wish to see. There is a LookML parameter called parameter
that takes filter values input by the user and inserts them into your LookML, dynamically creating the set of fields returned in the UI.
Dynamic Dimensions
Let's say you have multiple dimensions or dimension group timeframes that represent different date granularities (like day, month, quarter, year), and you would like users to be able to select by which timeframe the data is grouped on a dashboard. We can define the following parameter, which will be displayed as a filter on your dashboard:
parameter: date_granularity {
type: string
allowed_value: { value: "Day" }
allowed_value: { value: "Month" }
allowed_value: { value: "Quarter" }
allowed_value: { value: "Year" }
}
We will then apply this parameter to a dimension that ties the parameter's filter values to our different timeframe fields.
dimension: date { label_from_parameter: date_granularity sql: CASE WHEN {% parameter date_granularity %} = 'Day' THEN ${created_date} WHEN {% parameter date_granularity %} = 'Month' THEN ${created_month} WHEN {% parameter date_granularity %} = 'Quarter' THEN ${created_quarter} WHEN {% parameter date_granularity %} = 'Year' THEN ${created_year} ELSE NULL END ;; }
Note: Looker converts certain timeframes to string values, while others remain oftype: date
ortype: integer
. For example,${created_date}
would result in the datatypedate
. Some SQL dialects require that everything in theCASE
statement be of the same type, so make sure that all outcomes of yourCASE
statement have the same datatype.
Depending on your SQL dialect, you can useCAST(${created_date} AS string/char)
for MySQL or BigQuery, orTO_CHAR(${created_date}, 'YYYY-MM-DD')
for Redshift or Postgres.
The specified timeframe field will now be returned based on whichever parameter value the user selects on the dashboard. Note that we can use label_from_parameter
to pass the selected value label to our tile.
Note: Because the resulting dynamic dimension is type: string
, dimension fill cannot be applied to the dimension in an Explore.
Dynamic Measures
A similar approach can be applied to measures. This is especially useful when you have a group of related metrics that you may not want to display individually in the field picker, or that you wish to display dynamically on a Look or dashboard, based on user selection.
The following pattern is used to map parameter values to the different metrics that will be available on our dashboard.
parameter: metric_selector { type: string allowed_value: { label: "Total Revenue" value: "total revenue" } allowed_value: { label: "First-Time Shopper Revenue" value: "total_first_purchase_revenue" } allowed_value: { label: "Returning Shopper Revenue" value: "total_returning_shopper_revenue" } }
measure: metric { label_from_parameter: metric_selector type: number value_format: "$0.0,\"K\"" sql: CASE WHEN {% parameter metric_selector %} = 'total_revenue' THEN ${total_revenue} WHEN {% parameter metric_selector %} = 'total_first_purchase_revenue' THEN ${total_first_purchase_revenue} WHEN {% parameter metric_selector %} = 'total_returning_shopper_revenue' THEN ${total_returning_shopper_revenue} ELSE NULL END ;; }
In the UI, our dashboard will look like this:
Note: As an alternative to a CASE
statement, you can write the condition with a Liquid {% if %}
logical comparison statement. This solution can be used to simplify the SQL query output to improve performance. A CASE
statement is evaluated for each row in a table, whereas a Liquid {% if %}
statement inserts the correct value directly into the query. Using the date granularity example from above, here is what this alternate solution would look like:
dimension: date { label_from_parameter: date_granularity sql: {% if date_granularity._parameter_value == 'day' %} ${created_date} {% elsif date_granularity._parameter_value == 'month' %} ${created_month} {% else %} ${created_date} {% endif %};; }
Here the Liquid variable parameter_name._parameter_value
is used to insert the value of the parameter specified with parameter_name
.
For more examples of creating logical statements with Liquid, see the documentation page for Liquid Variable Reference.