Overview

Parameters are a powerful way to make your Mode reports scalable and interactive. Parameters are great for reports that aim to answer the same questions across a variety of contexts, such as:

  • An individual sales performance dashboard usable by all Account Executives.
  • An event stream monitor that allows the viewer to choose the type of events shown.
  • A KPI dashboard that allows the user to adjust the data range across which the KPIs are calculated.

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:

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, 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:

parameter_name:
  property_one: value_one
  property_two: value_two
  property_xxx: value_xxx

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.

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

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.

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

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:

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

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”:

order_min:
  type: text
  default: 0

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.

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

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

A date string of the format YYYY-MM-DD will be substituted into the report’s SQL code wherever {{ order_start_date }} is written when the query is executed.

If you do not include the default property when defining a Date parameter, the parameter will default to 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

NOTE
A parameter value provided via the report URL will override the default value.

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.

Control parameter values via report URL

You can use a Mode report’s URL query string to provide the parameter values you want Mode to use to run the report when that URL is accessed. The values for one or more parameters may be appended to the report URL as a series of & separated field-value pairs with the field names in the form of param_xxx=some_value, where xxx is the parameter name you define in your report.

Using the example report, you might want to construct a URL that overrides the defined defaults for the sales_region and order_max parameters as follows:

https://modeanalytics.com/modeanalytics/reports/a9e1fe690f93?param_sales_region[]=Midwest&param_sales_region[]=Northeast&param_order_max=2000

Tip:
Append the field-value pair run=now to your report’s query string to force Mode to run the report when that URL is accessed. Otherwise Mode will return cached, if the report had been run previously with the exact same set of parameter values, Mode will return the cached results from that run.

Some best practices to keep in mind when constructing a report URL with parameters:

  • For Multiselect parameters, each distinct value must be provided as a separate field-value pair in the format param_parameter_name[]=value.
  • Parameter names and values in report URLs are case sensitive.
  • If no value or an invalid value is provided for a parameter via the URL, the value of the default property will be used.
  • Remember to encode any special characters used in your parameters.

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:

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

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:

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

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

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.

The options list for a Select or Multiselect parameter can be defined dynamically from the results of a SQL query within a report. Instead of 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.

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:

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

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:

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

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:

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

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