The Looker parameter
field lets you inject a value directly into the SQL.
Various Looker pages describe how to use this feature:
- The Templated Filters and Parameters documentation page, which discusses creating the field and applying the user input
- The
parameter
reference page for this field type and itsallowed_value
subparameter - The Field Parameters page, which includes the
parameter
field type and shows which LookML parameters can be subparameters of theparameter
field - The Liquid Variable Reference documentation page, which mentions the
{% parameter %}
Liquid variable - The Dimension, Filter, and Parameter Types documentation page, which indicates which LookML type parameter values can be used with
parameter
fields
In this article, we discuss some great use cases for this feature.
1. Dynamic Dimensions and Measures
Sometimes we do not want to show all the dimensions and measures in the field picker of an Explore but still would like to provide the functionality behind each of them. This can be achieved by creating a dynamic dimension or measure, which lets the user specify the dimension or measure from the filter UI. To do this, you first define a parameter
field with a set of allowed_value
subparameters:
parameter: date_granularity { type: string allowed_value: { value: "Day" } allowed_value: { value: "Month" } allowed_value: { value: "Quarter" } allowed_value: { value: "Year" } }
The parameter
is surfaced as a filter-only field in the UI, and the user can choose one of the allowed values:
You then define a dimension to dynamically choose the column it retrieves, based on the parameter
value the user chooses:
Note: Because the resulting dynamic dimension is type: string
, dimension fill cannot be applied to the dimension in an Explore.
dimension: date { label_from_parameter: date_granularity sql: CASE WHEN {% parameter date_granularity %} = 'Day' THEN ${created_date}::VARCHAR WHEN {% parameter date_granularity %} = 'Month' THEN ${created_month}::VARCHAR WHEN {% parameter date_granularity %} = 'Quarter' THEN ${created_quarter}::VARCHAR WHEN {% parameter date_granularity %} = 'Year' THEN ${created_year}::VARCHAR ELSE NULL END ;; }
Note: Your SQL dialect may or may not require casting toVARCHAR
. Some dialects require that everything in theCASE
statement be of the same type.
The same approach can be used for dynamic measures. First, create the parameter
with a set of allowed_value
subparameters:
parameter: metric_selector { type: string allowed_value: { label: "Total Order Profit" value: "total_order_profit" } allowed_value: { label: "First-Time Shopper Revenue" value: "total_first_purchase_revenue" } allowed_value: { label: "Returning Shopper Revenue" value: "total_returning_shopper_revenue" } }
This lets the user select the metric:
Then, create a measure that dynamically uses the chosen metric:
measure: metric { label_from_parameter: metric_selector type: number value_format: "$0.0,\"K\"" sql: CASE WHEN {% parameter metric_selector %} = 'total_order_profit' THEN ${total_order_profit} 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 ;; }
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
dimension and metric
measure 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 %};; } measure: metric { label_from_parameter: metric_selector type: number value_format: "$0.0,\"K\"" sql: {% if metric_selector._parameter_value = 'total_order_profit' %} ${total_order_profit} {% elsif metric_selector._parameter_value = 'total_first_purchase_revenue' %} ${total_first_purchase_revenue} {% elsif metric_selector._parameter_value = 'total_returning_shopper_revenue' %} ${total_returning_shopper_revenue} {% else %} NULL {% 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 Liquid Variable Reference documentation page.
2. Multiple Schemas with the Same Data Format
Another use case is schemas that are identical in structure. This is often encountered when working with GA Premium data, where one can see tables such as myproject:00001.ga_sessions, myproject:00002.ga_sessions
, or myproject:00003.ga_sessions
. The prefixes 00001, 00002, 00003
could represent data for different countries or for Web versus iOS, and so on.
Using a parameter
, you can design a dashboard with a filter that lets your users select the country — with a user-friendly name!
view: t2 { sql_table_name: {% parameter tablename %};; parameter: tablename { type: unquoted allowed_value: { label: "UK" value: "00001.ga_sessions" } allowed_value: { label: "Germany" value: "00002.ga_sessions" } } }
If your company has multiple clients, you might store data from each of your clients in its own schema: clienta.users
, clientb.users
, and so on. Using a similar approach to the country example above, you can let an account manager easily choose which client's data to use.
3. Top N Items Versus the Rest of the Population
People often want to find out the top n performing products and compare those with other products.
Without a parameter
, you would have to hard code certain buckets (for example, top 5, 10, 20, and so on). However, with a parameter
, we can let the user determine n, and we can label the remainder as Other.
In the context of an e-commerce store, where we want to find out how well the top n products are doing, we would start with a derived table where we rank each product based on the number of sales:
view: brand_rankings { derived_table: { sql: SELECT brand, count(*) as count, RANK() OVER(ORDER BY COUNT(*) DESC) as rank FROM order_items LEFT JOIN inventory_items on order_items.inventory_item_id=inventory_items.id LEFT JOIN products ON inventory_items.product_id = products.id GROUP BY 1 ;; } dimension: brand { type: string primary_key: yes sql: ${TABLE}.brand ;; } dimension: rank_raw { type: number sql: ${TABLE}.rank ;; } }
Using a parameter
, we let users specify how many of the top brands they're interested in:
parameter: max_brands { type: number }
By referencing the parameter inside the sql
parameter of our rank dimension, we can then create the Other bucket on the fly:
dimension: rank { type: string sql: CASE WHEN ${rank_raw} <= {% parameter max_brands %} THEN RIGHT('00' + CAST(${rank_raw} AS VARCHAR), 2) ELSE 'Other' END;; } dimension: rank_and_brand { type: string sql: CASE WHEN ${rank} = 'Other' THEN 'Other' ELSE ${rank} || '-' || ${brand} END;; }
This results in the user's top n items having their own row and the rest being grouped together as Other:
Additionally, we can create a ranking of countries we have sold to, set that ranking based on a metric not shown in the visualization, and select a date range different from what is shown in the results.
To do this, create a view like this:
view: top_5_countries { label: "Countries Ranking" derived_table: { sql: select country_id as country_code, {% parameter country_name_criteria %} as country_rank from ( select country_code, rank() over(order by count(*) desc) as visitorCount, rank() over(order by sum(orders.gross_revenue) desc) as totalGrossRevenue, rank() over(order by avg(orders.gross_revenue) desc) as averageGrossRevenue FROM orders WHERE {% condition rank_date_range %}created_at {% endcondition %} group by country_code ) AS country_summary ;; } filter: rank_date_range { type: date description: "Select a range within which you are ranking the ordering of countries by metric selection. E.g. the rank of countries who had the top 10 highest revenue in May" } dimension: country_code { primary_key: yes hidden: yes type: string sql: ${TABLE}.country_code ;; } parameter: country_name_criteria { label: "Ranking Criteria" description: "Specify which metric to order the ranking by" type: unquoted default_value: "totalGrossRevenue" allowed_value: { label: "Total Gross Revenue" value: "totalGrossRevenue" } allowed_value: { label: "Average Gross Revenue" value: "averageGrossRevenue" } allowed_value: { label: "Visitor Count" value: "visitorCount" } } #### This parameter will allow a user to select a Top N ranking limit for bucketing the countries, almost like parameterizing the Row Limit in the UI parameter: country_rank_limit { label: "Rank Limit" description: "Specify the cutoff for overall rank" type: unquoted default_value: "5" allowed_value: { label: "Top 5" value: "5" } allowed_value: { label: "Top 10" value: "10" } allowed_value: { label: "Top 20" value: "20" } allowed_value: { label: "Top 50" value: "50" } } dimension: country_rank_top_N { hidden: yes description: "Rank within the range selected and list of countries based on metric selected. Useful for sorting visualization based on ranking." label_from_parameter: country_name_criteria label: "Country Code" type: number sql: case when ${TABLE}.country_rank<={% parameter country_rank_limit %} then ${TABLE}.country_rank else null end ;; } dimension: country_name_top_N { description: "Name of the Country within the rank selection." label: "Country Name (Top N)" type: string sql: case when ${TABLE}.country_rank<={% parameter country_rank_limit %} then ${TABLE}.country_code else 'other' end ;; } }
And then join it to the base Explore:
explore: orders { join: top_5_countries { type: inner sql_on: ${top_5_countries.country_name_top_N} = ${orders.country_code} ;; relationship: many_to_one } }
This allows us to answer such edge-case questions as "Show me the top 10 countries and their Gross Revenue for the last 7 days, based on how many unique visitors those countries had in the 7 days prior, and display them in order of that visitor count."
4. Conditional Display Value Prompts
Another great use case is defining a threshold (say on sale price) and using that to conditionally format the results. Defining a parameter
lets the user set the sensitivity in the UI. Then, by referencing the parameter
in the html
of the measure, we can conditionally format the results.
This is shown below:
parameter: sale_price_sensitivity { type: number } measure: sale_price_sens { type: number sql: ${sale_price} ;; html: {% assign var=_filters['order_items.sale_price_sensitivity'] | plus:0 %} {% if var < order_items.sale_price._value %} <div style="color: black; background-color: red; font-size:100%; text-align:center">{{ rendered_value }}</div> {% else %} {{rendered_value}} {% endif %} ;; }
When a user sets the sale price sensitivity, their query results use conditional color for any sales price at or above that sale price, for example: