Great Use Cases for Parameter Fields


(Joseph Axisa) #1

In Looker release 4.22 we 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 don’t want to show all the dimensions and measures in the field picker of an explore but still provide the functionality behind each of them.

This can be achieved by creating a dynamic dimension/measure which lets the user specify what the dimension or measure is 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:

Then you 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 everything in the CASE statement to 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:

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 format of data

Another use case is if we have 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_, 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 their own schema: clienta.users, clientb.users, etc. 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 population
Often people want to find out the top N performing products and compare those with the rest of the products.

Without a parameter, you would have to hardcode certain buckets (e.g. 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 ecommerce 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;;
 }

Which results in the user’s top N items having their own row and the rest are grouped together as “Other”:

##4. Conditional Display Value Prompts
Another great use case is if you want to define a threshold (say on sale price) and use 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:


Using HTML and Liquid to create 'progress' bars inside a Table Vis
Interesting Ways to use Liquid in Labels
Dropdown in explore to change column in a derived table
[Retired] Masking Sensitive Fields for Some Users
(Fabiana Tamburrini) #2

great article! thanks a lot


(Joshua Moskovitz) #3

Love these examples - needed them a few times over the past few days. thanks @jax


(Fabiana Tamburrini) #4

I have a situation that is not working for me.

In my case, I need to define dynamically which table from the database to use that I’m doing like shown on the example “2. Multiple schemas with the same format of data”.
Both tables have almost the same fields, the only difference is one field where it indicates the day (dt) or month (mon) of the transaction.
So i need to be able to define dynamically which field to consume depending on the table used.

On the example “1. Dynamic Dimensions and Measures”, it shows how to choose between one field or other but when both fields exits on the same table from the database (as the case clase is resolved by the database, if one of the fields does not exits it fails). This is not my case where on each table I have one field or the other (day or month).

I was trying to so something similar to the “if clause” shown on the 4th example (but to use it on the sql section not on html), but it does not works. Getting error “Cannot use “_filters” in SQL.”
I tried also to omit the step of assigning to a var an use the <% if my_parameter._value == ‘month’ %} but it always go to the else clause independently of the parameter value selected.

Any thoughts about how to resolve this?


(jeffrey.martinez) #5

@Fabiana_Tamburrini, without seeing what you have already tried, we came up with this. Does this logic accomplish what you’re looking for?

So the LookML is coded to point to either column, month or date whether or not it exists in the table.

But dynamic_dim only uses the correct dimension if the table falls under certain conditions. Extending the Example 2:

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"
        }
 }
dimension: date_field {
hidden: yes
type: date
sql: ${TABLE}.date_field ;;
}
dimension: month_field {
hidden: yes
type: date_month
sql: ${TABLE}.month_field ;;
}
dimension: dynamic_dim {
type: date
sql: CASE WHEN {% parameter tablename %} = "00001.ga_sessions" THEN 
${date_field}
      WHEN {% parameter tablename %} = "00002.ga_sessions" THEN 
${month_field} END ;;
}

Let us know if that’s helpful!


(AJ) #6

I was curious if you could set a filter in this parameterized way?
I’ve tried creating a parameter that was applied to the filter but it didnt work (Essentially the ‘Suggestions’ parameter but in the dropdown-filter format). The following may loosely demonstrate what I’m looking for:

    ...
    ...
    WHERE
        {% condition date_range %} date_table."date" {% endcondition %}
    AND {% condition day_of_week %} TO_CHAR(date_table."date", 'Day') {% endcondition %}
    ;;

}
extends: [_parameters_for_filters]

filter: date_range {
type: date
convert_tz: no
default_value: “4 weeks”
}

filter: day_of_week {
allowed_value: {
label: “Sunday”
value: “Sunday”
}
allowed_value: {
label: “Monday”
value: “Monday”
}
allowed_value: {
label: “Tuesday”
value: “Tuesday”
}
allowed_value: {
label: “Wednesday”
value: “Wednesday”
}
allowed_value: {
label: “Thursday”
value: “Thursday”
}
allowed_value: {
label: “Friday”
value: “Friday”
}
allowed_value: {
label: “Saturday”
value: “Saturday”
}
allowed_value: {
label: “Any time”
value: “”
}


(quinn.morrison) #7

Hi @ajhong91,

Thanks for reaching out. The filter parameter accepts the following suggestion parameters:

  • suggestions (which allows you to hardcode your suggestions like you would with allowed values)
  • suggest_dimension (which allows you to specify a set of field values from an existing dimension, for example, if you have a dimension group with a day_of_week timeframe)
  • suggest_explore (if your suggest_dimension comes from a different explore)

The allowed values parameter is specific to parameters. If you would like to use a parameter in your derived table WHERE clause, you can do so with the syntax {% parameter day_of_week %} = day_of_week_field. A parameter would work here for day of week, as we can set this to equal a specific value, but for a date range we will want to use a templated filter, as these insert filter values as a logical statement.

If you would like to dive deeper into your specific use case, please email support@looker.com with more details of what you’re trying to set up, and we’d be happy to help with that!

Thanks,

Quinn


(Menashe Hamm) #8

I found another great use for parameter and figured I should share the knowledge.

I created a retention explore that allows users to see who retained on days m through n. To allow the user to pick the days, I created a view that looks like this. (All code is heavily simplified from actual.)

view: retained {
  parameter: first_day {type: number}
  parameter: last_day {type: number}
}

Then, the explore looks like this.

explore: signups
join: retained {
  relationship: many_to_many # This line doesn't do anything but, if it's not here, Looker issues a LookML warning.
  sql: ;;
}
join: events {
  sql_on: ${signups.user}=${events.user}
    and dateadd(day, ifnull({% parameter retained.first_day %},1), ${signups.date}) <= ${events.date}
    and dateadd(day, ifnull({% parameter retained.last_day  %},1), ${signups.date}) >= ${events.date}
  ;;
  type: left_outer
  relationship: many_to_many
}

The ability to use parameter in sql_on in a join is undocumented as far as I can see, so maybe it will go away, but for now it works fine.


(brettg) #9

Use case 1 for the dynamic date granularity can improved as of looker 5.10 to avoid the case statement using the _parameter_value liquid reference. Going off the example in the article we can now do something like this:

dimension: date {
     label_from_parameter: date_granularity
     sql:
       {% if date_granularity._parameter_value == "'Day'" %}
           ${created_date}::VARCHAR
       {% elsif date_granularity._parameter_value == "'Month'" %}
           ${created_month}::VARCHAR
        {% elsif date_granularity._parameter_value == "'Quarter'" %}
           ${created_quarter}::VARCHAR
        {% elsif date_granularity._parameter_value == "'Year'" %}
          ${created_year}::VARCHAR
      {% else %}
           NULL
       {% endif %};;
 }

(Sean Higgins) #10

As an extension of the Top N results, what I wanted to do was create a ranking of countries we sold to and set that ranking based on a metric that’s not shown in the visualisation over a date range that is also different from what’s shown in the results.

To do this I created this View:

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
    )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 joined it onto 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”.


(Sackermann) #11

For your dynamic date code, is there any way to pull only this year 2018? I’ve implemented your code into my LookML and it seems to return all years in the column.

  parameter: date_filter {
    type: string
    allowed_value: { value: "1 day ago" }
    allowed_value: { value: "this month" }
    allowed_value: { value: "this quarter" }
    allowed_value: { value: "this year" }
  }

  dimension: date_test {
    label_from_parameter: date_filter
    sql:
       CASE
         WHEN {% parameter date_filter %} = '1 day ago' THEN ${date_date}::VARCHAR
         WHEN {% parameter date_filter %} = 'this month' THEN ${date_month}::VARCHAR
         WHEN {% parameter date_filter %} = 'this quarter' THEN ${date_quarter}::VARCHAR
         WHEN {% parameter date_filter %} = 'this year' THEN ${date_year}::VARCHAR
       ELSE
           NULL
       END ;;
  }

Please let me know if any one has solve this. Thanks.


(Department of Customer Love) #12

Hey @sackermann,

One thing that could be done to restrict to only the current year is to use a sql_always_where parameter on the explore(s) this parameter is part of. The sql_always_where will generate a WHERE clause in the SQL that limits the query to only the current year. More information on this parameter can be found here.