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:

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:

Parameter Example

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:

Parameter Example

Query as executed against the database:

Parameter Example

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:

User Chooses Exact value substituted for {{ sales_region }} in query
Nothing ''
Northeast 'Northeast'
Northeast and West 'Northeast','West'

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 input_type property

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 input_type: string OR input_type not specified
User Chooses Exact value substituted for {{ election_district }} in query
Nothing ''
2 '2'
2 and 3 '2','3'
**Property input_type: number
User Chooses Exact value substituted for {{ election_district }} in query
Nothing Nothing
2 2
2 and 3 2,3

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'

IMPORTANT: If you do not include the default property when defining a Date parameter, the parameter will default to a blank (i.e., '') value. Learn more about how to default to a calculated date, including the current date.

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.

NOTE: A parameter value provided via the report’s URL query string will override the default value.

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 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.

TIP: Often, no single column in your query will contain the exact list of options you’ll want to use for a Select or Multiselect parameter. Therefore, most users will create a separate query in their report containing both the parameter definition and SQL code specifically for the purpose of returning that parameter’s labels and values.

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'.

Last updated May 17, 2018