Menu Close Get Metabase

Create filter widgets for charts using SQL variables

Aug 12, 2020 by The Metabase Team

This article walks through how to create questions in Metabase using native SQL queries so that viewers of our questions can plug in values and filter the results. While Metabase makes it easy to summarize and visualize data without SQL, data analysts sometimes need to dig into complicated queries, which they can write using Metabase’s SQL editor.

Introduction to SQL variables and filter widgets

For example, using the Sample Dataset included with Metabase, we might write a question in SQL that pulls product information with our orders, but we want viewers of that question to specify the category of products that they want to view. To give people the option to input values on saved SQL questions, we can write SQL queries with variables, and Metabase will automatically create filter widgets that people can use to enter values.

Figure 1. An example question, written in SQL, that uses a basic text variable to power a filter widget. The widget allows people to enter text to filter records for product titles containing that text.
Figure 1. An example question, written in SQL, that uses a basic text variable to power a filter widget. The widget allows people to enter text to filter records for product titles containing that text.

For now, we’ll just be focusing on filters applied to questions written in SQL. We’ll cover dashboard filters in an upcoming guide.

But first: do you want to write your question using SQL, or would a custom question better suit your use case?

SQL questions versus custom questions

Before we dig into adding filter widgets, it’s worth considering how people will use our question. If we just want to give people the option to plug in values to simple filter widgets, writing a question in the native query editor and adding variables to our SQL code makes sense.

If, instead, we compose a simple or custom question using the notebook editor, filter widgets are unnecessary, as viewers of our question will have the full suite of query building primitives available to slice and dice the data however they like – by joining, filtering, and summarizing the data. For more sophisticated questions, they’ll also have custom expressions at their disposal, as well as have the ability to drill-through the data to zoom in on orders, or click on values to view individual records – functionality that does not apply to questions written in SQL. (In Enterprise edition, however, you can customize what happens when people click on a chart.)

Figure 2. The <strong>notebook editor</strong> interface for composing custom questions. You can join, filter, summarize, sort, use custom expressions, and more.
Figure 2. The notebook editor interface for composing custom questions. You can join, filter, summarize, sort, use custom expressions, and more.

If you can answer your question using the functionality in the simple or custom question builders, we recommend you use those. If, however, you need custom SQL commands or functions, and you want your users to be able to filter the results of those questions…read on!

With that said, let’s move on to setting up filter widgets on our SQL questions.

The different types of variables available for native SQL queries

Figure 3. A SQL question with a basic input variable of type Date. The <strong>Variable type</strong> dropdown menu shows the four variable types you can include in your queries: Text, Number, and Date, as well as a special variable type called a <strong>Field Filter</strong>.
Figure 3. A SQL question with a basic input variable of type Date. The Variable type dropdown menu shows the four variable types you can include in your queries: Text, Number, and Date, as well as a special variable type called a Field Filter.

As in Figure 3 above, for questions composed using Metabase’s native sql query editor, there are four types of variables you can select:

  • Text
  • Number
  • Date
  • Field Filter

One of these variable types, Field Filters, is unlike the others. In fact, it’s better to think of the variable types falling into two main categories: basic filter variables and Field Filters.

  • Basic input variables create simple filter widgets where people can plug in values to filter the question’s results. Basic input variables comprise:

  • Field filter variables are special input variables. They are more sophisticated than basic input variables, and they behave differently. Field filters are “wired up” to columns, and can provide dropdown menus for people to select one or more values, like the dropdown shown in Figure 1 at the beginning of this article.

We’ll cover the three basic input variables - Text, Number, and Date – in this article, and Field Filters in another article. But first let’s get a sense of when to reach for one type of variable over the other.

Field filters or basic input variables?

When to use basic input variables

  • For simple text, number, and date filtering. For more flexible date filtering, use a Field Filter.
  • In general, for innumerable values that would not make sense to include in a dropdown (which would require a field filter).
  • For cases where you may need to do some data wrangling/munging on the input variable in your SQL.

When to use Field Filters

  • To provide a dropdown menu for people to select from defined values. See the list of field types available.
  • To give multiple ways to filter by date.
  • To hook up a variable to a dashboard filter (more on dashboard filters in an upcoming guide).

Now let’s move on to how basic input variables work.

Basic input variables

Basic input variables take input like text, number, or dates. Basic input variables are good for when values are not predefined, or range widely, like order subtotals.

Dates, of course, are a special case; everyone knows the range of values, as time is just standard time. Metabase does not support alternative calendars, or at least not yet: Metabase is open source, so if you want to add support for the Mayan calendar or whatever, go for it.

Basic input variable: Number

Let’s use a basic input variable of type Number. Say we want to create a question that returns all of the records from the orders table, but we want to give people the option to filter out orders based on the order subtotal.

Let’s keep it simple and just give people the option to input a number so that the question will return records for orders with a subtotal greater than that number.

To include a variable in your query, simply wrap the variable name in double braces, like this: {{ variable }}. In this example, we’ll call our variable, {{subtotal_var}} . Here’s the SQL:

SELECT *
FROM orders
WHERE subtotal > {{subtotal_var}}

When we add a variable to a SQL query, Metabase will add a filter widget at the top of the question, and slide out a sidebar to present options for the variable.

Figure 4. A question using a basic variable of type Number, <strong>subtotal_var</strong>, that will add a filter widget, allowing people to filter for orders greater than the value they plug in.
Figure 4. A question using a basic variable of type Number, subtotal_var, that will add a filter widget, allowing people to filter for orders greater than the value they plug in.

As shown in figure 4, here are the options in the Variables sidebar:

  • Variable type. Types can be Text, Number, Date, or Field filter. The variable type determines the input interface for variable’s widget (e.g., for Date, the widget will present a date picker).
  • Filter widget label. The name of your variable as presented in the widget, which defaults to the variable name in the SQL query.
  • Required? When you make a variable required, Metabase will prompt you for a Default filter widget value to plug into the variable when the question first loads. If you don’t provide a default, Metabase won’t execute the query until a value is supplied.

In the case of subtotal_var, we’d want to:

  • Set the Variable type to be Number (since we’re dealing with subtotals).
  • Change the Filter widget label from subtotal_var to Subtotal (just to make it easier to read).
  • Toggle Required? to be true.
  • Set a Default filter widget value to 0. That way, when a question runs, it will return all results automatically; people can enter a higher subtotal if they wish to filter the results.

Now we’re ready to plug in values into our Subtotal widget, and filter for orders with Subtotals greater than that value:

Figure 5. Minimizing the SQL editor, and entering 100 into the Subtotal filter widget to filter for orders with Subtotals greater than 100.
Figure 5. Minimizing the SQL editor, and entering 100 into the Subtotal filter widget to filter for orders with Subtotals greater than 100.

Making a basic input variable optional

If we want to make the filter widget optional, we can enclose the WHERE clause in double brackets:

SELECT *
FROM orders
[[WHERE subtotal > {{subtotal_var}}]]

With the WHERE clause in brackets, if the viewer does not enter a subtotal, and no default is set, the query would simply return all records from the orders table (i.e., Metabase would only run SELECT * FROM orders).

Adding multiple filters

We can use multiple filters as well. If, for example, we’d prefer people filter the results by inputting a range of subtotal values, we can add two variables for lower and upper bounds:

SELECT *
FROM orders
WHERE subtotal BETWEEN {{subtotal_min}} AND {{subtotal_max}}

In this case, two widgets will appear, one for each variable.

Figure 6. Question with multiple widgets: one for <strong>Subtotal Min</strong>, and one for <strong>Subtotal Max</strong>. Both values are toggled as required, with a default filter widget value set to 0.
Figure 6. Question with multiple widgets: one for Subtotal Min, and one for Subtotal Max. Both values are toggled as required, with a default filter widget value set to 0.

Basic input variable: Text

Let’s try an example using a simple text input variable. In this case, we want to create a question with a filter widget that allows people to search for product titles that contain the text they enter into the widget.

Here’s the code:

SELECT *
FROM products
[[WHERE UPPER(title) LIKE UPPER(CONCAT('%', {{search_term}},'%'))]]

We enclose the WHERE clause in brackets to make the widget input optional. We bookend the variable with the wildcard character % to indicate that the term could have zero or more characters to the left or right of the variable. Additionally, we guard against case sensitivity by using the UPPER function on both the title column and the {{search_term}}.

And here’s our filter:

Figure 7. A basic text filter widget, filtering for titles that contain the word 'computer'. The results contain the rare Lightweight Wool Computer.
Figure 7. A basic text filter widget, filtering for titles that contain the word 'computer'. The results contain the rare Lightweight Wool Computer.

Basic input variable: Date

When you select the Date variable type, the filter widget will present a simple date picker. Here’s a question with two basic date variables so that users can input a start and end date to return orders placed between those dates.

SELECT *
FROM orders
[[WHERE created_at BETWEEN {{start_date}} AND {{end_date}}]]
Figure 8. Basic date widgets, with simple date pickers connected to the <strong>start_date</strong> and <strong>end_date</strong> variables in the optional <strong>WHERE</strong> clause.
Figure 8. Basic date widgets, with simple date pickers connected to the start_date and end_date variables in the optional WHERE clause.

Now, something to note here: people would need to select dates for both widgets in order for the filter to activate, which could lead to unexpected behavior. For example, someone might leave the End date blank and expect the orders to be filtered from the Start date up to today - when in fact no filter would be applied.

For dates, consider instead using a Field Filter, which offers a lot more flexibility.

Up next: Field Filters

In upcoming articles, we’ll cover how you can create more sophisticated filter widgets using Field Filters, including how to hook those Field Filter variables up to dashboard filter widgets, which allow you to filter data across multiple questions. In the meantime, you can check out our documentation on SQL variables.