Hi, I’m trying to create a simple parameter to choose between 2 dimensions to breakdown a view. I’ve copied the syntax exactly from the Looker docs, but keep getting an error saying that the “parameter’ needs a field name:
parameter: regiondrilldown {
type: unquoted
allowed_value: {value: "Country"}
allowed_value: {value: "State"}
allowed_value: {value: "City"}
}
dimension: geo_breakdown {
type: string
label_from_parameter: regiondrilldown
sql:
case
when {% parameter ${regiondrilldown} %} = 'Country' Then ${country}
when {% parameter ${regiondrilldown} %} = 'State' then ${state}
when {% parameter ${regiondrilldown} %} = 'City' then ${city}
end ;;
}
Solved! Go to Solution.
I usually do parameters this way @nikitanaresh
parameter: regiondrilldown {
type: unquoted
allowed_value: {value: "country"
label: "Country"}
allowed_value: {value: "state"}
label: "State"}
allowed_value: {value: "city"}
label: "City"}
}
dimension: geo_breakdown {
type: string
label_from_parameter: regiondrilldown
sql:
{% if regiondrilldown._parameter_value == 'country' %} ${country}
{% elsif regiondrilldown._parameter_value == 'state' %} ${state}
{% elsif regiondrilldown._parameter_value == 'city' %} ${city}
{% else %} ${country}
{% endif %};;
}
What if you simplify it this way, provided that your table dimensions are called like that
dimension: geo_breakdown {
type: string
label_from_parameter: regiondrilldown
sql: ${TABLE}.{% parameter regiondrilldown %} ;;
}
That’s because you can’t do
${{% parameter regiondrilldown %}}
Tried that, but I’m still getting the same field name error.
I’m trying this out on the Qwiklabs ecommerce model. Could it just be an issue with the testing environment?
If you keep the code as you have then you can’t have parameter unquoted as it will produce
Country = "Country"
Unquoted means the value is entered as is and not as string literal
I usually do parameters this way @nikitanaresh
parameter: regiondrilldown {
type: unquoted
allowed_value: {value: "country"
label: "Country"}
allowed_value: {value: "state"}
label: "State"}
allowed_value: {value: "city"}
label: "City"}
}
dimension: geo_breakdown {
type: string
label_from_parameter: regiondrilldown
sql:
{% if regiondrilldown._parameter_value == 'country' %} ${country}
{% elsif regiondrilldown._parameter_value == 'state' %} ${state}
{% elsif regiondrilldown._parameter_value == 'city' %} ${city}
{% else %} ${country}
{% endif %};;
}
The above is better because the conditional logic is within Liquid, so parameter can be unquoted but you make the paramter’s output to be used in conditional logic in SQL
This works! I guess I just had my quotes and = vs == mixed up.
I fixed that and it ran with both an if and a case statement.