User Guide

Getting Started

Help Center

Documentation

Community

Training

Articles in this section

Great Use Cases for Parameter Fields

Avatar
Joseph Axisa
  • Updated
Follow

Looker has introduced a parameter field, which lets you inject a parameter directly into the SQL.

There are various materials that show how to use this feature:

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/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:



Great_use_case_for_parameter1.png

You then define a dimension to dynamically choose the column it retrieves, based on the parameter value the user chooses:

 

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 to VARCHAR; some dialects require that everything in the CASE 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 parameters:

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:


great_use_cases_for_paramters.2.png

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 ;;
}

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 00001, 00002, 00003 prefixes could represent data for different countries, or for Web vs. iOS, etc.

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 your clients, each in its own schema: clienta.users, clientb.users, and so one. 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 vs. 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 forth.) However, with a parameter we can let the end 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”:


Great_Use_Cases_for_Parameter_Fields.3.png

Additionally, we can create a ranking of countries we have sold to, and set that ranking based on a metric not shown in the visualization, and over 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 visualisation 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 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:



Great_Use_Cases_for_Parameter_Fields.4.png

Related articles