Adding filters to dashboards with SQL questions

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

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 is to understand how to connect dashboard filter widgets to a special variable type, called a field filter, that we insert into the SQL code of our questions. Here’s our itinerary:

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

Adding a Field Filter variable to a SQL question

Let’s start with a question written in SQL that shows the orders per month from the Sample Database included with Metabase. From any Metabase page, in the upper right on the navigation bar, click on the console icon to visit the native query editor.

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 (or column) in a table.

Here’s the SQL code we’ll add:

WHERE {{created_at}}

You may notice the lack of an = test in the WHERE clause. This abbreviated syntax exists because Field Filters handle some SQL code for you under the hood. For more on what’s going on here, check out Field Filters.

With the WHERE clause in place, our code looks 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 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.

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.

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.

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 (figure 7).
• Click the Done button at the top of the screen.
• 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).

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.

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.

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

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 tab. Learn more about editing metadata in our documentation.

Now that our question has access to date from the state column in the people table, we can return to our dashboard and add a new filter to the dashboard and call it State. We’ll connect this new dashboard filter to the question’s Field Filter variable, {{state}}.

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

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

Further reading

• Create filter widgets for charts using SQL variables
• Field Filters: create smart filter widgets for SQL questions
• SQL parameters
• Dashboards
• Dashboard filters
• The Data Model page: editing metadata