Menu Close Get Metabase

Adding filters to dashboards with SQL questions

Aug 17, 2020 by The Metabase Team

This article covers how to create dashboard widgets to filter data in SQL questions. Figure 1 shows the dashboard we’ll be building:

Figure 1. What we'll be building: a dashboard with two filters connected to two questions written in SQL.
Figure 1. What we'll be building: a dashboard with two filters connected to two questions written in SQL.

This dashboard features:

  • two questions written in SQL,
  • two filter widgets: one Date filter and one State filter.

Selecting a value in either filter (or both filters) will update the results in both questions.

Our goal, or vision quest, is to understand how to connect dashboard filter widgets to a special variable type, called Field Filters, that we insert into the SQL code of our questions. Here’s our itinerary:

For some background, check out our previous articles on SQL variables and (especially) Field Filters.

Adding a Field Filter variable to a SQL question

Let’s start with a simple question written in SQL that shows the orders per month from the Sample Dataset included with Metabase.

Figure 2. A question written in SQL showing orders per month, visualized as a line chart.
Figure 2. A question written in SQL showing orders per month, visualized as a line chart.

Here’s the SQL code:

SELECT 
    PARSEDATETIME(FORMATDATETIME(orders.created_at, 'yyyyMM'), 'yyyyMM') AS "Created at",
    COUNT(*) AS "Number of orders"
FROM orders
GROUP BY "Created at"
ORDER BY "Created at" ASC

Now, we’re talking about filtering data, and astute readers might already recognize that we don’t have a filter statement in our SQL code. There is no WHERE clause. Meaning: even if we were to add this question to a dashboard, and add a filter widget to that dashboard, that filter would have no effect on our SQL question, because there would be no designated place in our code for the widget to insert its value.

Let’s fix that by adding a special variable to our SQL question known as a Field Filter.

Field Filters are a special type of variable that map the variable to a field (column) in a table.

Here’s the SQL code:

WHERE {{created_at}}

You may notice the lack of an = assignment in the WHERE clause. The reason for this abbreviated syntax is somewhat involved. The short answer is because Field Filters handle some SQL code for you under the hood. For the long answer: refer to our article on Field Filters.

With the WHERE clause in place, our code should now look like this:

SELECT 
    PARSEDATETIME(FORMATDATETIME(orders.created_at, 'yyyyMM'), 'yyyyMM') AS "Created at",
    COUNT(*) AS "Number of orders"
FROM orders
WHERE {{created_at}}
GROUP BY "Created at"
ORDER BY "Created at" ASC

Now that we have our variable situated in our SQL code, we need to tell Metabase how to use the variable. When we add a variable to our code, Metabase will slide out the Variables sidebar. We’ll set the Variable type to Field Filter, then map that variable to a field in our database so Metabase can know what kind of filter widget it should add to the question. In this case, we’ll map the variable to the created_at field of the orders table.

Figure 3. Setting the <strong>Variable type</strong> for our SQL variable, <strong>created_at</strong>, to <strong>Field Filter</strong>, then setting the <strong>Field to map to</strong> option to the <strong>created_at</strong> field (column) of the <strong>orders</strong> table.
Figure 3. Setting the Variable type for our SQL variable, created_at, to Field Filter, then setting the Field to map to option to the created_at field (column) of the orders table.

Note that we can call the SQL variable whatever we want, but we must map the variable to the appropriate field in order for the dashboard filter to work.

For now, we’ll leave the Filter widget label as “Created at”, and leave the Required? toggle alone. This way, if the variable’s filter widget does not supply a value, the question will run as if the WHERE clause did not exist.

We can also select a Filter widget type, though this widget will only apply to our question. Let’s select the Date Filter type, which is the most expressive of the widgets.

Let’s Save our question. We’ll call it Number of orders per month - SQL.

Next, we’ll need to:

  • create a dashboard
  • add our question to a dashboard,
  • add a different filter widget to that dashboard,
  • then connect that dashboard widget to the Field Filter variable in our SQL question.

But first, a detour.

A detour on data types

When we select the created_at field, Metabase knows that the Field type is a Creation timestamp (note the calendar icon). You can learn about the types of fields (columns) each table has by browsing your data.

Figure 4. View information about your data by clicking on <strong>Browse Data</strong> from the top navigation bar, selecting your database - in this case, <strong>Sample Dataset</strong> - clicking on the information icon next to a table, and clicking on the book icon to <strong>Learn about this table</strong>.
Figure 4. View information about your data by clicking on Browse Data from the top navigation bar, selecting your database - in this case, Sample Dataset - clicking on the information icon next to a table, and clicking on the book icon to Learn about this table.
Figure 5. Viewing information on the fields in the <strong>Orders</strong> table of the <strong>Sample Dataset</strong>.
Figure 5. Viewing information on the fields in the Orders table of the Sample Dataset.

Administrators can edit the field type, as well as other metadata settings, in the Data Model tab of the Admin Panel. To learn more, check out our documentation on metadata editing.

Connecting a dashboard filter widget to a Field Filter variable

So we have our SQL question (figure 6) with a Field Filter variable in a WHERE clause, and it’s time to add that question to a dashboard.

Figure 6. Our SQL question with our <strong>Field Filter</strong> variable, showing orders created per month.
Figure 6. Our SQL question with our Field Filter variable, showing orders created per month.

Let’s create a dashboard (we’ll give our dashboard the wildly unimaginative name Dashboard with filter widgets).

Then we’ll add our SQL question to the dashboard.

Next, we’re going to add a filter widget to our dashboard. In Dashboard edit mode (click on the pencil icon to enter edit mode):

  • Click on the filter icon to add a new filter widget to the dashboard.
  • Under What do we want to filter, we’ll select Time.
  • For What kind of filter?, we’ll select All options, which will add a Date filter to our dashboard.
  • Next, we’ll need to connect our widget to the Field Filter variable in our question. Click on the dropdown menu in the center of our question, and select our Created At Field Filter variable.
  • Click the Done button at the top of the screen.
  • Then Save the dashboard.

See the GIF in figure 7.

Figure 7. Adding a <strong>Date filter</strong> (Time -> All options) to a dashboard, and connecting the filter widget to the Field Filter variable, <strong>Created at</strong> in our SQL question. Remember to click on the <strong>Done</strong> button, then <strong>Save</strong> the dashboard.
Figure 7. Adding a Date filter (Time -> All options) to a dashboard, and connecting the filter widget to the Field Filter variable, Created at in our SQL question. Remember to click on the Done button, then Save the dashboard.

Now we’re all wired up, and we’re ready to test out our new Date filter. This particular widget type gives us an abundance of options. Let’s choose the Between option to select a date range. In this case, we’ll select the orders for 2019 (between January 1, 2019, and December 31, 2019).

Figure 8. Using the <strong>Between</strong> option in our <strong>Date filter</strong> widget to select orders from January 1, 2019 to December 31, 2019.
Figure 8. Using the Between option in our Date filter widget to select orders from January 1, 2019 to December 31, 2019.

With our dashboard filter set, our SQL question updates to filter for orders where the created_at column falls in the date range specified by the filter widget on the dashboard.

Figure 9. With the date range set in our filter widget, our SQL question, <strong>Number of orders per month - SQL</strong>, will only show orders from that range.
Figure 9. With the date range set in our filter widget, our SQL question, Number of orders per month - SQL, will only show orders from that range.

Filtering multiple SQL questions on a dashboard

Let’s add an additional SQL question to our dashboard. To keep it simple, let’s create a question that returns the new accounts created per month. The SQL for this question is similar to our first question:

SELECT 
    PARSEDATETIME(FORMATDATETIME(people.created_at, 'yyyyMM'), 'yyyyMM') AS "Created at",
    COUNT(*) AS "Number of new accounts"
FROM people
WHERE {{created_at}}
GROUP BY "Created at"
ORDER BY "Created at" ASC

Once again, for our {{created_at}} variable, we’ll:

  • Set the Variable type to Field Filter.
  • Set the Field to map to option to the Created_At field of the People table (not the Orders table).
  • Leave the Filter widget type as is, or change it to Date Filter (just note that this setting only applies to the filter widget on the question, not the dashboard.)
  • Leave the Required? toggle off (as in: do not require the variable).

Save your question, title it Number of new accounts - SQL, and add your question to the dashboard.

Now, the existing Date filter does not automatically connect to the newly added question, Number of new accounts - SQL. If we update the value(s) in the Date filter widget, only the data in the connected question, Number of orders per month - SQL, will update to show the filtered data.

So let’s wire up the new question to the dashboard filter widget:

  • Click on the pencil icon to enter dashboard edit mode.
  • Select Edit on the Date filter widget.
  • Then select the Created At option (the only option) from the dropdown menu at the center of the Number of new accounts - SQL card (See figure 10).
  • Save the dashboard.
Figure 10. Connecting another question to the same <strong>Date filter</strong> widget.
Figure 10. Connecting another question to the same Date filter widget.

With both Field Filter variables connected to our dashboard filter widget, when we select a value in the widget, both questions will update their charts with the filtered data.

Adding another filter widget to the dashboard

We now have two questions on our dashboard connected to a single filter. Let’s add another filter. And since we’ve already conquered Time, let’s control Space. Let’s add a widget to the dashboard that allows us to filter by state in the United States.

To start, we’ll need to add a {{state}} variable to the WHERE clause of our Number of orders per month - SQL question.

SELECT 
    PARSEDATETIME(FORMATDATETIME(orders.created_at, 'yyyyMM'), 'yyyyMM') AS "Created at",
    COUNT(*) AS "Number of orders"
FROM orders
LEFT JOIN people
ON orders.user_id = people.id
WHERE {{created_at}} AND {{state}}
GROUP BY "Created at"
ORDER BY "Created at" ASC

Next, we’ll specify that our new {{state}} variable is a Field Filter, of type State (see figure 10).

Note: if a widget type is unavailable, and the type of data is compatible with a Field Filter, an administrator may need to explicitly set the field type in the Data Model section. Learn more about editing metadata in our documentation.

Figure 10. Adding a Field Filter variable connected to the <strong>State</strong> field of the <strong>People</strong> table.
Figure 10. Adding a Field Filter variable connected to the State field of the People table.

Now that our question has access to date from the state column in the people table, we can connect the question’s Field Filter variable, {{state}}, to the dashboard filter widget.

Figure 11. Connecting State Field Filters to the dashboard State filter widget.
Figure 11. Connecting State Field Filters to the dashboard State filter widget.

And behold: our dashboard with two SQL questions and two filter widgets: one Date filter and one State filter.

Figure 12. Dashboard with two filter widgets: one Date filter and one State filter. Date filter for orders/accounts created in 2018 in California (CA).
Figure 12. Dashboard with two filter widgets: one Date filter and one State filter. Date filter for orders/accounts created in 2018 in California (CA).

All that’s left to do in order to match the dashboard in figure 1 is to add a text box.

Further reading