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 of type: date or type: integer. For example, ${created_date} would result in the datatype date. Some SQL dialects require that everything in the CASE statement be of the same type, so make sure that all outcomes of your CASE statement have the same datatype.

Depending on your SQL dialect, you can use CAST(${created_date} AS string/char) for MySQL or BigQuery, or TO_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.