Parameters with multiple options

Adding parameters to your reports allows your audience to easily interact with the data. With a little SQL magic, you can set up parameters that allow your users to enter in multiple options in the form of comma-separated values.

This example was generated with a parameter field that allows users to enter in as many states as they choose. Click Powered by Mode below to open the report in your browser and change the states shown.

View Mode Analysis

Click Powered by Mode to duplicate this example and apply it to your own data. Learn more about using HTML to customize your reports.

The SQL below allows you to build a parameter that accept multiple comma-separated values:

	SELECT state,
       	   SUM(live_births)as births
  	  FROM modeanalytics.county_health_rankings 
 	 WHERE state IN ('{{ states | replace: ",","','" }}')
  GROUP BY 1
  ORDER BY 2  DESC
	 
{% form %}

states:
  type: text
  default: 'Virginia,California,Ohio,Alaska,Rhode Island'
  
{% endform %}

• The code from {% form %} to {% endform %} create the parameter (called states in this example).
• Rather than referencing this parameter in your query using '{{states}}, reference it as '{{ states | replace: ", ","','" }}'. This will add quotes around each of values entered in the form. In the example above, adding Virginia,California,Ohio to the report form will create this line in the query: WHERE state IN ('Virginia','California','Ohio').