Overview

Use parameters to make reports more scalable, re-usable and interactive by allowing viewers to modify the data returned by a report without modifying it's code.

Parameters are defined and configured using the Liquid template language and a single report can accept input from multiple parameters at once. Mode reports offer four different kinds of parameters:

• Select
• Multiselect
• Text
• Date

When you define a parameter in a report's code and subsequently run the report, a form is automatically added to the top of the report that report viewers can use to modify the parameter's value.

You can include parameter values in a report's URL query string and Mode will return the report's output using those values.

Getting started

There are two primary steps to implementing a parameter:

Define each parameter - Report parameters are defined alongside your SQL code between {% form %} and {% endform %} tags in the Query Editor. For readability, we suggest you place this code below your SQL code.

Reference parameters in your SQL code - Insert one or more references to each parameter into your SQL code using Liquid object tags (e.g., {{ parameter_name }}). At run time, Mode replaces each object reference in your SQL code with the corresponding parameter's input value before sending the code to your database for execution.

Let's walk through a simple example. This report takes input from one parameter and displays companies in the Crunchbase database that were acquired that year:

If you open the report details and check the box next to Show parameter template code, you'll see the following:

SELECT company_name,
        acquirer_name,
        acquired_at
FROM tutorial.crunchbase_acquisitions
WHERE acquired_year = {{ acquired_year }}

{% form %}

acquired_year:
    type: text
    default: 2013

{% endform %}

This code produces a single text box parameter titled "Acquired Year" with a default value of "2013". Whenever the report is run, the exact set of characters entered into this text box will be substituted directly into all report SQL code wherever {{ acquired_year }} is written. Un-check the box next to Show parameter template code and you'll see the code that was actually executed against the database, after Mode evaluated all the Liquid code and the parameter's value was substituted into the WHERE clause for {{ acquired_year }}.

Query as written:

Query as executed against the database:

Defining parameters

Report parameters are defined alongside your SQL code in the Query Editor inside {% form %} and {% endform %} tags. Each definition contains one or more properties which control the type, available options, and display of the parameter in the report. When defining parameters in a report's code, keep the following in mind:

• You can define multiple parameters inside one set of {% form %} and {% endform %} tags.
• You can reference a parameter as many times as you want in across all of the report's queries.
• Define each parameter only once and ensure each parameter in the report has a unique name.

Parameter definition syntax is similar to a YAML dictionary and therefore requires specific formatting. Parameter names must not have leading spaces and properties must be indented by two spaces. The general format for a parameter definition is:

{% form %}

parameter_name:
    property_one: value_one
    property_two: value_two
    property_xxx: value_xxx

{% endform %}

The parameter's name is defined on the first line and its properties are defined on subsequent lines with two spaces of indentation.

Throughout this section, we'll use a more in-depth example report. It contains a stacked bar chart that changes depending on the configuration of a number of parameters.

Select

A Select parameter allows the viewer to choose exactly one option from a pre-defined drop-down list. The example report (check Show parameter template code) defines a parameter named shipment_status with two options ("delivered" and "failure") that the user must choose between. By default, "delivered" is selected.

{% form %}

shipment_status:
    type: select
    default: delivered
    options: [delivered, failure]

{% endform %}

The options property is set to a list of values that will be available to the report viewer in the dropdown. The exact value selected will be substituted into the report's SQL code wherever {{ shipment_status }} is written when the query is executed.

Multiselect

A Multiselect parameter allows the viewer to choose zero, one or many options among a pre-defined list. The example report (check Show parameter template code) defines a parameter named sales_region with four possible options ("Northeast", "Midwest", "West", and "Southeast") that the user can choose from. By default, "Northeast" and "West" are selected.

{% form %}

sales_region:
    type: multiselect
    default: [Northeast, West]
    options: [Northeast, Midwest, West, Southeast]

{% endform %}

The options property is set to a list of values that will be available to the report viewer in the dropdown. In this example, the exact value(s) selected will be substituted into SQL code wherever {{ sales_region }} is written when the query is executed. For example:

A Multiselect parameter may contain a maximum of 1000 options. Because Multiselect parameters can return zero, one or many values you'll want to evaluate them using the IN operator or equivalent syntax.

The optional

When you define a Multiselect parameter, you can use the optional input_type property to tell Mode how to format the expression list that the parameter returns. Acceptable values are input_type: string and input_type: number. If the input_type property is not explicitly defined, the default value is string.

This property is most commonly used when the Multiselect parameter's acceptable inputs are numbers, rather than strings, and you want Mode to render the parameter's input as a list of numbers that are not wrapped in single quotes.

For example, suppose the parameter election_district is defined as follows:

{% form %}

election_district:
    type: multiselect
    default: 1
    options: [1,2,3,4]

{% endform %}

Adding the input_type property to the definition influences how Mode evaluates {{ election_district }} references in code in the following ways:

Property

**Property

Text

A Text parameter presents the report viewer with a free-form text box that accepts any combination of characters as an input. The example report (check Show parameter template code) defines a parameter named order_min with a default value of "0":

{% form %}

order_min:
    type: text
    default: 0

{% endform %}

The exact string of characters entered into the parameter will be substituted into the report's SQL code wherever {{ order_min }} is written when the query is executed. If you want the value returned by the parameter to be interpreted as a string, you must wrap the reference to the parameter in single quotes. For example:

--SQL as written

SELECT * FROM tutorial.us_flights WHERE origin = '{{ flight_origin }}'

--SQL as rendered upon execution

SELECT * FROM tutorial.us_flights WHERE origin = 'LAX'

If you know the parameter value will always be a number and you want it evaluated in your SQL as a number (i.e., not as string), do not include single quotes. For example:

--SQL as written

SELECT * FROM demo.orders WHERE occurred_at >= {{ order_min }}

--SQL as rendered upon execution

SELECT * FROM demo.orders WHERE occurred_at >= 0

Date

A Date parameter presents the report viewer with a date picker which they can use to choose one date. The example report (check Show parameter template code) defines a parameter named

{% form %}

order_start_date:
    type: date
    default: 2016-01-01

{% endform %}

A string in the format of YYYY-MM-DD containing the chosen date will be substituted into the report's SQL code wherever {{ order_start_date }} is written when the query is executed. Therefore, make sure to wrap any reference to a date parameter in single quotes. For example:

--SQL as written

SELECT * FROM demo.orders WHERE occurred_at >= '{{ order_start_date }}'

--SQL as rendered upon execution

SELECT * FROM demo.orders WHERE occurred_at >= '2016-01-01'

Optional properties

You can define the following optional properties for any of the above parameters types to customize the user experience. Each optional property is used in the example report we have been referencing so you can see each one in action.

default

The default value that Mode will substitute into your SQL code for the parameter if no value is explicitly provided. When a user opens your report for the first time, it will run with the parameter set to this value. This is also what Mode will use for the parameter's value while you are editing the report and running queries in the Query Editor.

For Multiselect parameters, set default: all and all available options will be selected by default.

label

Allows you to customize the label placed above the parameter in the report view that users will see. If you do not define the label property for a parameter, Mode will automatically humanize the parameter's name and display that in the report view.

description

If defined, adds a tooltip to the parameter's name. When a report viewer hovers over this tooltip, the text you define for this property will be displayed. Use this property to provide additional context for the parameter to report viewers.

Common techniques

Different display labels and values

When defining the options list for a Select or Multiselect parameter, you may want the values actually presented to the user in the parameter form to differ from what is substituted into your SQL code. To do this, set the options property of the parameter equal to a list of two value lists like so:

{% form %}

param_name:
    options: [[label_1, value_1],
              [label_2, value_2],
              [label_X, value_X]
             ]

{% endform %}

The labels are the values displayed in the parameter's form to the report viewer. The values are what is substituted into the SQL code. For example, you may want to define a parameter that allows the user to choose among a list of sales rep names but have the selected sales rep's ID substituted into your SQL code instead of their name:

{% form %}

sales_rep:
    type: select
    default: 2
    options: [[Joey Smith, 1],
              [Fran Jones, 2],
              [Molly Lane, 3]
             ]

{% endform %}

The above code defines a Select parameter named sales_rep with three options visible to the user: "Joe Smith", "Fran Jones" and "Molly Lane". When the user selects one of those options and runs the report, the selected sales rep's ID will be substituted into the SQL code wherever {{ sale_rep }} is written (e.g., if "Fran Jones" is selected, the value 2 will be substituted into the code).

Dynamic options list

The options list for a Select or Multiselect parameter can be defined dynamically from the results of a SQL query within a report. Instead setting the options property in the parameter definition equal to a static list, you set the options property equal to two sub-properties (indented an additional two spaces from options:

• labels: The name of a column in the SQL query containing the values that will be shown to the report viewer in the drop-down list.
• values: The name of a column in the SQL query containing the values that will be substituted into your SQL code when the parameter choice(s) are selected.

In the example report, the sales_rep parameter is referenced in the WHERE clause of the Main Query, but its definition and the SQL that populates its options are defined in a separate query called Sales Reps Param:

SELECT id, name
FROM demo.sales_reps

{% form %}

sales_rep:
    type: multiselect
    default: all
    options:
        labels: name
        values: id

{% endform %}

This SQL query returns a list of sales reps and their corresponding unique IDs from the database. In the sales_rep parameter definition, labels and values reference column names in the query's results. Because labels and values point to different columns in this example, when one or more of the rep names from the "name" column is selected, Mode will substitute the corresponding value(s) from the "id" column into the query anywhere {{ sales_rep }} is written.

If you would like the parameter options displayed to the user to be the same as the values substituted into your SQL code, simply set labels and values equal to the same column name.

Ignore empty input

This query (check Show parameter template code) shows how to modify the WHERE clause of a SQL query to handle an empty Text parameter input by effectively ignoring it.

Start by setting the parameter default to '':

{% form %}

event_name:
    type: text
    default: ''

{% endform %}

And modify the SQL WHERE clause as follows:

WHERE (event_name = '{{event_name}}' OR '{{event_name}}' = '')

Because the OR operator is used and both statements are wrapped in parentheses, the statement will evaluate to true both if a parameter option is selected and if no option is selected.

Default to a calculated date

Use Liquid date filters to define an object that returns the date you want in the format 'YYYY-MM-DD' to create a calculated default date. Use STRFTIME syntax to control the output of Liquid's date filter. Some examples:

Default to today's date:

{% form %}

date_param:
    type: date
    default: {{ 'now' | date: '%Y-%m-%d' }}

{% endform %}

This method takes the current UTC date and time ('now') and converts it to a string formatted as YYYY-MM-DD (date: '%Y-%m-%d').

Default to the date 7 days after today:

{% form %}

date_param:
    type: date
    default: {{ 'now' | date: '%s' | plus: 604800 | date: '%Y-%m-%d' }}

{% endform %}

This method takes the current UTC date and time ('now'), converts it to UNIX epoch time (date: '%s'), adds the number of seconds in seven days to that time (plus: 604800), and converts that to a string formatted as YYYY-MM-DD (date: '%Y-%m-%d').

Default to the date one year ago:

{% form %}

date_param:
    type: date
    default: {{ 'now' | date: '%Y' | minus: 1 }}-{{ 'now' | date: '%m-%d' }}

{% endform %}

This method takes the current UTC date and time ('now'), removes everything except the current year (date: '%Y'), subtracts one from that year (minus: 1') and concatenates that with - and the current month and day 'now' | date: '%m-%d'.

FAQs

Q: How to create a dynamic Parameter defined by another Parameter?

You may want to add a parameter to a report that updates based on the value of another parameter. For example, a regions dropdown that has an option of "northeast" and that leads to a nested dropdown of maine, vermont, etc.

We have an example report that shows how to create a dynamic parameter defined by another dynamic parameter. It does come with a drawback however - with the current infrastructure of how parameters work, it's not possible to have the second dropdown update dynamically after just pressing the run button once. You will need to update the first dropdown, hit run and then update the second dropdown, hit run. This is a workaround, so there are limitations. 

Alternatively, you can combine parameters and report filters. In this way, the report filters act as the second set of dynamic parameters. You can view this example report that shows how running the parameter value, the filter adjusts accordingly and allows the user to filter the data as desired

Q: How to pass parameters into the notebook?

To pass parameters to your notebook, you must add them as a column in your SQL query. You can then access those column(s) in the dataset object in your notebook:

SELECT
 '{{team}}' AS param
FROM
 benn.nfl_touchdowns

This is an example report (check Show Parameter Code) showing how this can be done. You can view the Python code by clicking Notebook on the left side panel.

Q: How can I reorder the appearance of Parameters in the Report View?

The order of parameters are defined in two ways and in this order:

• Query creation date/time
  • If Query A was created Nov 15 and Query B was created on Nov 14 then the parameters in Query B will appear before those in Query A. The parameters within Query B will then appear in the same order they appear as written in the query.
• Order of appearance within the query
  • You can control the order of Parameters by defining them all in the same parameter {% form %} in a query. The order in the report view would respect the order in the query form.

Q: How to set up a schedule with relative parameter dates (like "yesterday")

When creating schedules in Mode, you may want to set up a date parameter that's relative to the time the schedule runs. For example, rather than setting a date parameter to show data from January 1, 2022 to today, you may want the set up the parameter to show data over the last week. 

You can do this using an if statement in your query. The query below shows an example. 

There are two parameters: start_date and previous_week. If previous_week is true, the if statement will return the line in the query that only includes orders in the last week. If the previous week value is false, the query will use the line that includes orders that occurred after the chosen parameter start date.  

SELECT DATE_TRUNC('day',occurred_at) AS day,
       COUNT(*) AS orders
  FROM demo.orders
 WHERE occurred_at <= NOW()
{% if previous_week == 'true' %}
   AND occurred_at >= NOW() - INTERVAL '7 DAY'
{% else %}
   AND occurred_at >= '{{start_date}}'
{% endif %}
 GROUP BY 1
 ORDER BY 1

{% form %}

start_date:
  type: date
  default: 2022-01-01

previous_week:
  type: select
  default: 'true'
  options: ['true','false']

{% endform %}

You can see a live version of this report here.

You could do this with a single parameter as well. For instance, instead of using the previous_week parameter in the if statement, you could use the start_date parameter:

{% if start_date == '2022-01-01' %}
   AND occurred_at >= NOW() - INTERVAL '7 DAY'
{% else %}
   AND occurred_at >= '{{start_date}}'
{% endif %}

In this case, if you picked that specific date (Jan 1, 2022), the parameter would use the first AND statement. This way, you can use this relative date option for a report schedule without having to add an additional parameter that might lead to confusion for people running the report manually.

You can do things like this with if statements in Mode because it uses the Liquid template language to create parameters. You can read more about what you can do in Mode with this language and common techniques here.

Troubleshooting

1. How to debug Parameter and Liquid code?

When you're using parameter logic in your query, it can often become difficult to debug issues. It is extremely helpful to be able to see the parameter values that are being passed to the query at run time. This can be done by following these steps:

1. To view the history of a query that uses a parameter, click the View History tab at the top of the SQL editor.
2. On the Query History screen, select the first query run and then click the Rendered tab on the right side of the screen.

This will show you the exact parameter values sent to your query.

Another method would be to view the Source tab in the dataview after a successful query run.

2. I'm unable to interact with Parameters on the report builder page

You cannot view the parameter forms in the Report Builder. To add them to your report, you will need to include the parameter code in one or more queries and then select "View" in the report header. If the parameter code is valid, you should see the form appear at the top of your report view page.