- How to create charts you can drill through
- What is drill-through and how does it work?
- The Action Menu
You can create charts that people can drill through in Metabase. Like this:
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:
- Use SQL to create a model, then use the query builder. Or…
- Add the SQL question to a dashboard and set a custom destination
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.
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:
- 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”).
- Save that question and turn it into a model.
- 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.
The visualization has been set to a stacked bar chart (figure 4).
The Action Menu
The Action Menu presents a few different drill-through options that you can choose from when exploring your data.
We’ll step through each of the options in the popup menu you see above:
Select to zoom
You can click and drag to select an area of a chart to zoom in on.
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.
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.
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.
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.
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.
Did this article help you?
Thanks for your feedback!