Overview

Use parameters to design reports that can change their output in response to fields that are easily configurable by end users or a report’s URL. Reports with parameters are a great making your work scale to many users. Parameters are great for reports that aim to answer the same questions across a variety of contexts. For example:

  • The sales team frequently requests reports on the status of individual clients. You design a report that provides an overview of a single client, and you add parameter for client_name in the WHERE clause so that the sales team could enter the appropriate client_name, re-run the report, and see results for the selected client.
  • You create a report to monitor an event stream with a parameter for the column event_type so you can quickly filter the results to show only events of the selected type.
  • Users want to run the same report regularly, but different date ranges, so you add parameters so users can select the start and end date before they run the report.

This example report produces a list of the companies that were acquired in a given year from Crunchbase data. The Parameter allows viewers to switch between years easily.

Getting started

Parameters are added to Mode reports using the Liquid template language. Parameters are defined inside the Query Editor within your SQL code and reports can accept input from multiple parameters at once. There are two primary steps to implementing a parameter:

Define and configure all parameters - The configuration one or more parameters, including it’s type, accepted values, and default value, is placed in one or more of the report’s queries in between {% form %} and {% endform %} tags. For readability, we suggest you place this code below your SQL.

Insert parameter references into your SQL code - This let’s Mode know how you want your SQL code modified in response to the parameter inputs.

Let’s walk through an example. Click here to view the report we use and follow along.

Parameter Example

Here’s the query that produces the parameterized report linked above:

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

{% form %}

acquired_year:
  type: text
  default: 2013

{% endform %}

As you can can see in the above report, this produces a single text box titled “Acquired Year” on the Report view page. Anything entered into this box will be substituted into the query in place of {{ acquired_year }} when a viewer clicks Run.

Parameter Example

This shows the query that was executed in the database, after all Liquid code for the parameter has been processed and converted to SQL. If you’d like to see the query with the form code, click View Details at the top of the report, then click SQL on the left and check the Show Parameter template code box above the query.

Parameter Example

You may have noticed that entering a value that doesn’t look like a 4-digit year breaks the query. This is because it is performing a direct text substitution. If you enter a non-numerical value, for example, you will receive an error. If you’d like to prevent viewers from entering erroneous values into your query Parameters, you can specify a Parameter as a dropdown:

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

{% form %}

acquired_year:
  type: select
  default: 2013
  options: [[2010, 2010],
            [2011, 2011],
            [2012 ,2012],
            [2013, 2013]
           ]

{% endform %}

Click here to view this version of the report.

The above Parameters are both numeric. If you want to add a text Parameter, you need to make sure to add single quotes around the Parameter within the body of the query:

SELECT *
  FROM crunchbase.companies
 WHERE company_country_code = '{{ country_code }}'

{% form %}

country_code:
  type: text
  default: USA

{% endform %}

Click here to view the above report in Mode

Now that you’ve got a general sense for how Parameters work, let’s look at specific rules for implementing text box and dropdown Parameters.

Type of Parameters

In the {% form %} section, you need to be somewhat careful about formatting:

  • For each different Parameter that you specify in your query using {{ }}, you must add an entry to the {% form %} section. The entry must not have spaces in front of it, and should end with a colon :.
  • On the next line, you should identify the type. For text boxes, this should read type: text; for dropdowns, type: select; for dates, type: date. Date Parameters will actually display a date picker for the report user. Note that this line must be indented two spaces or you will get an error.
  • For type: select, you must specify options on the next line. This must also be indented two spaces. For each option, you must specify what the option will show in the dropdown menu as well as how it will be populated into the query. This allows you to make the names look nice in the dropdown (using caps and spaces), while still submitting lower case/underscores to the query. Syntax is as follows:

    options: [['Clean Option 1 Text', 'option1_text'],
              ['Clean Option 2 Text', 'option2_text'],
              ['Clean Option 3 Text', 'option3_text']
             ]
    

Some things to note:

  • The “options” section opens and closes with[ ]
  • Each individual option opens and closes with [ ] as well. options are separated by commas
  • The above set of options is listed in single quotes because the input data types is a string. If your input type is numerical, it does not require single quotes:

    options: [[2001, 2001],
              [2002, 2002],
              [2003, 2003]
             ]
    

Multiselect

Multiselect Parameters allow report viewers to select more than one option at a time. You can create a multiselect Parameter the same way you would create any other Parameter. Simply add two additional modifications to your Parameter form field:

  1. Set your Parameter type with a simple line of code: type: multiselect.
  2. Add an additional input value called input_type. Options for this are string and number. If nothing is specified, your input type will default to string.
  3. The default: selection can either be a single value or all.

For example:

SELECT *
  FROM table
 WHERE name IN ( {{ parameter }} )

{% form %}

parameter:
  type: multiselect
  input_type: string / number
  default: 'Obama'
  options: ['Obama','Biden',20,12]

{% endform %}

Like standard select Parameters, multi-select Parameters work with both values and labels, and can be generated dynamically.

Default values

You may notice that the ability to change the Parameters is only displayed on the report page and not in Query Editor. The default: attribute tells Mode what values to use when running the query from the Query Editor. For example, if you run this query from the Query Editor:

SELECT *
  FROM tutorial.crunchbase_acquisitions
 WHERE acquired_year = {{ acquired_year }}

{% form %}

acquired_year:
  type: text
  default: 2011

{% endform %}

The results will be the same as if you had run this query:

SELECT *
  FROM tutorial.crunchbase_acquisitions
 WHERE acquired_year = 2011

Using the same Parameter multiple times in one query

In some cases, you may want to specify a Parameter that is used multiple time in a query. In these cases, you only need to specify the Parameter one time in the {% form %} section, but can refer to it as many times as you’d like in query. For example, take this query, which shows companies that received investments from other countries:

SELECT *
  FROM crunchbase.companies
  LEFT JOIN crunchbase.investments
    ON investments.company_permalink = companies.permalink
 WHERE investments.investor_country_code = '{{ country_code }}'
   AND companies.country_code != '{{ country_code }}'

{% form %}

country_code:
  type: text
  default: USA

{% endform %}

Click here to view the above report in Mode

Using Parameters in multi-query reports

If your report has multiple queries, the report page will show each unique parameters across all queries. If queries have identical Parameters, the report will combine non-unique Parameters into one.

For example, if you have three queries in your report and each report has two unique Parameters, your report will have six Parameters. If three queries have two Parameters each and the Parameters are the same across all three, the report will only display two Parameters.

Build dropdown menus that allow report viewers to input more than one option with multi-select Parameters.

Formatting parameters

By default, when you add a Parameter to a report, the Parameter name in the form will match the name of your Parameter. For example, the following Parameter code will create a form labeled “Account Id.”

{% form %}
account_id:
  type: text
  default: 1
{% endform %}

Changing the name

You can rename your Parameter form by adding a label tag to the code that creates your Parameter. For example, the following Parameter code will create a form labeled “Enter Your Account Id!” (Note that Parameter forms are always title-cased.)

{% form %}
account_id:
  label: "Enter your account id!"
  type: text
  default: 1
{% endform %}

Adding a description

You can also add descriptions to Parameter forms by adding a description tag to the the code that creates your Parameters. For example, the following Parameter code will create a form labeled “Account Id.” A description that says “Enter the account ID you want to look up.” will appear below the label.

{% form %}
account_id:
  description: "Enter the account ID you want to look up."
  type: text
  default: 1
{% endform %}

The label and description tags can be used separated or together. To see a live example of both of these tags in Mode, check out this example report.

Use the results of one SQL query to populate the dropdown menu of options for another query. As the first query’s results update, so too does the Parameter dropdown menu.

Using a dynamic Parameter is especially helpful when your dropdown list is too long to create manually, or if you expect the list options to change frequently.

Dynamic Parameters should only be used with the type: select Parameter type where you would otherwise need to define the list of options. They should not be used with type: textor type: date Parameters, since types don’t need to be manually defined.

If you’re just getting started with Parameters, be sure to first check out our help docs explaining Parameters and how to add them to reports.

Advanced Techniques

Dynamic Parameters

Let’s take a look at this example report, which looks at sales performance for sales reps and their accounts.

  1. Add a new query to your report where you will define your Parameter picklist. Unlike standard Parameters, which are written directly into the query you want to customize, a dynamic Parameter needs to be defined separately.
  2. Write a SQL query that’s output is a column that defines the list of values you want to to show as picklist options. If needed, you can also add a second column that defines labels.
  3. Then, below the query, add a Parameter form. For the options in the select Parameter, enter a label and value to set how they will appear in the form field.


SELECT DISTINCT name,
  FROM demo.accounts
  WHERE acquired_year = {{ acquired_year }}

{% form %}

account_name:
  type: select
  default: Western Digital
  options:
    labels: name
    values: name

{% endform %}

You can then call the dynamic Parameter in the other queries in your report by inserting a Parameter tag using double brackets. As a syntax reminder, Mode uses the Liquid template language for Parameters.

When you view your report, you’ll see that the form field has a dropdown list showing the dynamic picklist. To update the dropdown list in your form field, simply refresh the report. The query powering the dynamic Parameter will rerun simultaneously to populate the picklist with the most current results.

Note: You can insert Parameter tags into any query within a report, regardless of whether the Parameter form is in that specific query, or if it is a dynamic Parameter. For organizational purposes, some users prefer to always keep all of their Parameter forms in a separate query, rather than have multiple forms spread out across various queries within a report.

Adding ‘all’ as an option

You’ll need to modify both your Parameter code and the query. Here’s an example query you can explore alongside the explanation below.

Let’s start with the Parameter code. You’ll need to add an option to the options list that the report viewer can use to run the query and return all results. In the example below, this is all, but you can use whatever text you like.

{% form %}

event_name:
  type: select
  default: all
  options: [[all,all],
            [login,login],
            [like_message,like_message],
            [send_message,send_message]]

{% endform %}

Next, go to the place in your query where you filter by this input. It probably looks something like this:

WHERE event_name = '{{event_name}}'

Add an OR clause to this statement. The 'all' at the end of the second statement should match whatever text you put in the options list for the Parameter code.

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

This works because if the selected Parameter is all, the WHERE statement will always be true, and therefore return every row. For every other Parameter input, it will only return true when the event name matches that input value.

Text input that’s ignored when empty

If you want to have a textbox Parameter that’s ignored if empty, you can do that as well. This query shows a simple example.

Starting with the Parameter code, set the default to ''.

{% form %}

event_name:
  type: text
  default: ''

{% endform %}

And then modify the WHERE clause:

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

Don’t forget to include the parentheses. If you have a WHERE statement with more than one clause and no parentheses, like the one below, the entire WHERE statement will return true if the final statement is true.

Statement A, which includes no parentheses, is equivalent to B. If event_name = 'all', both statements will return true for every row. Statement C will return true for all rows where year = 2015.

A
---
WHERE year = 2015
  AND 'event_name' = '{{event_name}}'
   OR '{{event_name}}' = 'all'

B
---
WHERE (year = 2015
  AND 'event_name' = '{{event_name}}')
   OR '{{event_name}}' = 'all'

C
---
WHERE year = 2015
  AND ('event_name' = '{{event_name}}'
   OR '{{event_name}}' = 'all')

Date that defaults to today’s date

Most reports that include a date filter look like the example below. The input from the start_date field is injected directly into the query as a date.

SELECT *
  FROM events
 WHERE occurred_at <= '{{start_date}}'

{% form %}

start_date:
  type: text
  default: 2015-01-01

{% endform %}

This example creates a static default—if you want to run the query with today as the start_date, you have to change the input form each time.

Instead, you may want the form to default to today while still providing the option to add a custom date. Adding an if statement to your query makes this possible.

SELECT *
  FROM events
{% if start_date == 'today' %}
 WHERE occurred_at <= NOW()
{% else %}
 WHERE occurred_at <= '{{start_date}}'
{% endif %}

{% form %}

start_date:
  type: text
  default: today

{% endform %}

If you enter today into the Parameter form, Mode will add the first WHERE clause to your query. This WHERE clause can include whatever conditions you would like. In this example, it filtered to events that occurred before the current timestamp (note that NOW() isn’t a function in all versions of SQL; some use functions like SYSDATE, CURDATE(), and GETDATE().)

If you enter a value other than today into the Parameter form, Mode will add the second WHERE clause to your query. This clause matches the WHERE clause from the first query—it injects the Parameter input directly into your query as a date.

You can add additional variables like yesterday by using if/else statements in your query. This report shows a live example of this method (to see the Parameter code, go to the Query Editor and check the box next to Show Parameter template code).

Last updated April 18, 2018