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 that they wish to see. The LookML parameter called parameter takes filter values that are input by the user and inserts them into your LookML, dynamically creating the set of fields that is returned in the UI.

Dynamic dimensions

The following example uses a parameter of type: string in conjunction with CASE WHEN SQL logic. When you're using Liquid conditional statements that use {% if %} instead of CASE WHEN, you may need to use a parameter of type: unquoted instead of type: string. This is because {% if %} logic inserts values directly into a SQL query, whereas CASE WHEN evaluates each row in a table for specified values.

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. You 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" } 
}

You can then apply this parameter to a dimension that ties the parameter's filter values to the different timeframe fields.

dimension: date {
    type: string
    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 ;;
}

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 you can use label_from_parameter to pass the selected value label to the tile.

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 the 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, the 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. When you're using Liquid conditional statements that use {% if %} instead of CASE WHEN, you may need to use a parameter of type: unquoted instead of type: string. This is because {% if %} logic inserts values directly into a SQL query.

Using the date granularity example from above, here is what this alternate solution would look like:

  parameter: date_granularity { 
    type: unquoted 
    allowed_value: { value: "Day" } 
    allowed_value: { value: "Month" } 
    allowed_value: { value: "Quarter" } 
    allowed_value: { value: "Year" } 
  }

  dimension: date {
    type: string
    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 that is specified with parameter_name.

For more examples of creating logical statements with Liquid, see the Liquid variable reference documentation page.