Lesson

Create interactive charts

Build charts that people can explore by using the query builder, building models, or adding custom destinations.

You can create charts that people can drill through in Metabase. Like this:

<em>Fig. 1</em>. Zooming in on a particular category and timeframe, then viewing the orders that make up one of the bars on the chart.
Fig. 1. Zooming in on a particular category and timeframe, then viewing the orders that make up one of the bars on the chart.

If you’ve only ever written questions in SQL, you may have missed the fact that Metabase could even make your charts explorable. Or maybe you’ve clicked around on a dashboard and noticed that you can drill down through some charts, but not others. We’ll cover how to set up drill-through on your charts (sometimes called drill-down), and the different ways you can drill through those charts.

How to create charts you can drill through

If you’re using the query builder:

If you’re using SQL, you can either:

Create a question using the query builder

You get the Action Menu on charts automatically when you create a question using the query builder. That’s it. That’s all you need to do. And there’s no lock-in here; you can always convert a query-builder question to SQL.

<em>Fig. 2</em>. You can convert a question composed in the Notebook Editor to a SQL question at any time.
Fig. 2. You can convert a question composed in the Notebook Editor to a SQL question at any time.

But you probably won’t need to reach for SQL. The query builder is mighty. The query builder makes it straightforward to ask the vast majority of analytical questions, and for trickier queries, you have the more surgical custom expressions.

Use SQL to create a model

If you write a question using SQL, you won’t get drill-through out of the box. But you can still get the Action Menu if you:

  1. Write a question in SQL that brings together the starting data you need, like you’re creating a view for people to query. So don’t pre-filter or pre-summarize the data (aside from filtering out rows and columns you wish to exclude from the “view”).
  2. Save that question and turn it into a model.
  3. Edit the model’s metadata to specify each column’s type. If Metabase knows which type of data each column contains, it can work its drill-through sorcery.

From there, you can either let people use the model as the starting point for people to ask questions with the query builder, or you can create query builder questions based on that model for people to play around with.

The other option for SQL-based questions is to…

Add the question to a dashboard and set a custom destination

Custom destinations aren’t the same thing as providing people with the Action Menu. That is, people won’t be able to slice and dice the question’s data if you add a custom destination.

But custom destinations do give you more control over what happens when people click on a chart, and custom destinations are in some ways more powerful than the Action Menu (despite what our inconsistent capitalization might imply). You can send people to another question, dashboard, or external URL, and you can even parameterize those destinations based on values in the chart.

Custom destinations work for both SQL and query builder questions, as custom destinations override the default click behavior. You can also set up crossing-filtering on a dashboard, so that people can click on a chart to update a filter.

What is drill-through and how does it work?

Let’s tour the drill-through functionality available in the Action Menu. If you’d like to follow along, see figure 3 for a screenshot of the question in the query builder.

<em>Fig. 3</em>. The question, composed in the Notebook Editor, use in the following drill-through examples.
Fig. 3. The question, composed in the Notebook Editor, use in the following drill-through examples.

The visualization has been set to a stacked bar chart (figure 4).

<em>Fig. 4</em>. Setting the visualization to a stacked bar chart.
Fig. 4. Setting the visualization to a stacked bar chart.

The Action Menu

The Action Menu presents a few different drill-through options that you can choose from when exploring your data.

<em>Fig. 5</em>. Clicking on a value of a chart will bring up the action menu, presenting options to zoom in, view records, breakout, and X-ray.
Fig. 5. Clicking on a value of a chart will bring up the action menu, presenting options to zoom in, view records, breakout, and X-ray.

We’ll step through each of the options in the popup menu you see above:

Zoom in

There are two ways to zoom in on orders, Select-to-zoom and Auto-zoom.

Select to zoom

You can click and drag to select an area of a chart to zoom in on.

<em>Fig. 6</em>. You can click and drag to select an area of a chart, and Metabase will zoom in on the values in that selected area.
Fig. 6. You can click and drag to select an area of a chart, and Metabase will zoom in on the values in that selected area.

Auto-zoom

You can left-click to bring up the Action Menu > Zoom in and Metabase will create a close-up of the data surrounding the value you selected.

<em>Fig. 7</em>. Left-click on a chart to bring up the action menu and select <strong>Zoom in</strong>. Metabase will automatically create a close-up of the data surrounding the value.
Fig. 7. Left-click on a chart to bring up the action menu and select Zoom in. Metabase will automatically create a close-up of the data surrounding the value.

The Zoom in option will choose an appropriate range of values based on the full range of values in the chart. As shown in figure 7, Zoom in also focuses on the clicked category, so only orders with products in the Widget category are shown. If you’d prefer a different range, or wish to include additional groups, you have the Notebook Editor available to fine tune your chart (and save it as a new question).

View these records

You can click on a value on a chart and select View these orders to bring up a table with the individual records that compose the value.

<em>Fig. 8</em>. Clicking on a value and selecting <strong>View these orders</strong> will bring up a table of records that compose the value.
Fig. 8. Clicking on a value and selecting View these orders will bring up a table of records that compose the value.

Breakouts

Breaking out by a category lets us do things like see the banana cream pie orders in June 2017 broken out by the status of the customer (e.g., new or VIP, etc.) or other different aspects of the order. Different charts will have different breakout options, such as Location and Time.

Figure 9 shows an example breakout looking at orders from Product.vendor from the Sample Database.

<em>Fig. 9</em>. Clicking on a value will present options to breakout the data. Different options will appear based on the data in the chart. The GIF shows a breakout by Category: <strong>Product Vendor</strong>.
Fig. 9. Clicking on a value will present options to breakout the data. Different options will appear based on the data in the chart. The GIF shows a breakout by Category: Product Vendor.

X-rays

X-rays are automatically-generated explorations of your data. You can click anywhere on a chart to perform an X-ray, and Metabase will generate a dashboard full of different questions about the data. You’ll have an option to save that X-ray as a dashboard, which you can then edit to your liking, by removing irrelevant questions, or adding new questions or text boxes to fill in the story you want to tell.

<em>Fig. 10</em>. X-rays will create (a lot) of different charts based on data in your table(s).
Fig. 10. X-rays will create (a lot) of different charts based on data in your table(s).

Clicking on a point or a bar additionally gives you the option to compare data, which will give you another dashboard with auto-generated charts.

If X-rays don’t make sense for your data, you can disable X-rays. Learn more about X-rays in our documentation.

Thanks for your feedback!

Get articles like this one in your inbox every month