Passing constants at runtime

Looker Q&A Looker
1

We have a lookml which uses a constant name for a project ..

lookml sql code -

sel * from @project.table

just trying to see how I can parameterize/override the default with user input?
I want to keep the default as is, but enhance the dashboard to accept user project value at runtime and run the sql for new project ..

Appreciate your help!

2

You can do this by creating a Looker Parameter that has each project listed as an option (this will avoid user typo’s). Below is an example where I created a view that allows users to select the table at runtime. The Parameter will be displayed in the Explore as a Filter only Field, and the options available in the pull down will be those you specify.

view: filter_test {
derived_table: {
sql:
SELECT
m_number
FROM
{{ _user_attributes['datalake_project'] }}.xxxx_consumption_layer.{% parameter table %}
GROUP BY 1
;;
}

parameter: table {
type: unquoted
allowed_value: {
value: “vw_xxxx_monitor”
}
allowed_value: {
value: “vw_xxxx_dashboard”
}
}

dimension: m_id {
label: “M ID”
type: number
value_format_name: id
sql: ${TABLE}.m_number ;;
suggest_persist_for: “0 seconds”
}

}

1 Like
3

Thanks @ben_davis-16359 !

appreciate your suggestion!

I already tested this - however below are the issues -

  1. my string has “-“ .. And unquoted type does not support this character.
  2. if I instead use string, it appends some special quotes and I am not able to replace them .

if you can help with one of the above, that will serve my need.

Appreciate your help!!

4

You can add Liquid conditional logic to force the dash name.

{% if table_name._parameter_value == 'tablenamethathasthedash' %}
      xxxx_consumption_layer.table-name-with-dash
    {% else %}
     {% parameter table_name %} 
    {% endif %};;
1 Like
5

I have thoughts of trying this out, but this will be a dimension. Can I use this dimension as a parameter?
I want to pass the parameter as input upon which rest of the code will run

6

I’m not sure what you mean by it being a dimension. Can you provide some example code of what you are doing now?

Parameters manifest like filters in the UI. So you can provide them values to define what they should be (e.g., what table) and then they’ll change how your code works. If you set this as a dimension, you have no way of changing the value for the query that is run. In the explore UI, you can check the SQL that Looker generates before a query is run to watch how Looker interprets your coding.