Click to learn more about author Mike Brody.

Thought leaders typically discuss business intelligence in the abstract or in terms of business benefits, but I want to take a moment to drill in (pun most definitely intended) on drilldowns. They’re one of the most useful and beloved BI features. Who doesn’t appreciate being able to click on a summary or aggregation and view the underlying detail? You can kind of create drilldowns in spreadsheet applications using expand/collapse arrows and manual hierarchies, but BI has these relationships baked in and oh is it convenient.

But we tend to talk about drilldowns as though there were only one kind. That may have once been the case, but BI tools have grown in sophistication to meet ever-evolving market demands and increasingly complex data structures. Today, there are at least six ways to drill from one piece of data to another – and there may be more! New and exciting features are being developed every day, so let us know in the comments if we failed to mention any. For now, here are six ways to drill into your data.

1. Standard Drilldown: This is your basic, prototypical drilldown from a total into the more granular data that gave rise to it. It’s drilling from an annual sales total into a list of sales for the year; from an average unit price into all unit prices for a type of product; or from a minimum dollar contribution into a catalog of all contributions. Maybe that summary is in a table, or maybe it’s a bar in a bar chart. Either way, the goal is to see how the total arose in the first place. But what if we want to view different types of related information?
2. Lateral Drilldown: Maybe drilldown is a misnomer here since we’re drilling across more than down – but the semantics aren’t important. If you’re drilling laterally, you’re usually drilling from a record instead of an aggregate or summary. You could drill from a single record and land in a related report featuring information about that same record. So perhaps you’re starting from an order ID in an orders report and drilling into that brings you to a report showing which salesperson booked that order, when, and with whom. This time, the goal is to get additional related information about the value, not see how it was calculated.
3. Custom Drilldown: But what if you want to drill between two seemingly unrelated data fields? This is common for companies that use a variety of SaaS applications in their daily work. Even if they have a central BI solution aggregating all that data and joining it together, the same values may go by different names in different systems. Maybe “sales representative” in one system is equivalent to “account owner” in another, and you want to drill laterally between the two. Or perhaps you want to be able to drill from a date field in your upcoming events report to a different date field in your past events report to view likely weather conditions and staffing numbers. Custom drilldowns make that possible by letting report authors specify which fields to drill from and to, even if the application doesn’t recognize them as being related by default.
4. Conditional Drilldown: What if you only want a drilldown to activate if a condition is met? Maybe the orders drilldown in the example above only spawns if the order is running behind schedule, giving workers the information that they need to notify the customer. Drilling directly from an orders report automatically filters the customer contact report so that you don’t have to go digging for the right email address. Or maybe you want drilldowns to function differently depending on who is accessing the report. For example, an employee roster drills down into payroll information for HR representatives but spawns contact information for anyone else. Conditional drilldowns are a clever way to customize the report-viewing experience and facilitate insight-to-action.
5. Dashboard Drilldown: That’s right, some tools let you drill from a simple report, chart, or dashboard into a dashboard populated with other visualizations on the topic of interest. If that dashboard ordinarily shows more information, drilling into it will filter away all irrelevant records. So perhaps clicking on a salesperson’s latest deal in one report brings you to a dashboard overview of that salesperson’s performance with everything from call and meeting statistics to quota requirements and contact information. Drilling into dashboards is great when you want to drill into multiple charts and/or tables at once.
6. Continuous Drilldown: Why drill just one level down when you could keep on going? It’s possible to daisy-chain any of the above drilldowns together to create a hierarchy any number of layers deep. That could mean drilling down from one group to the next – such as from university campus, to college, to department, to professor, to course, and so on –or it could look more like a lateral narrative or “data story” of related visualization. Continuous drilldowns allow you to surf your data the way you surf the web.

I hope this glimpse into the wonderful world of drilldowns opens your eyes to new possibilities as you craft reports and dashboards, be they for yourself or for others. Those of you in the market for a BI solution might even explore these during product evaluations. If there’s one thing you take away from this article, though, let it be the knowledge that not all drilldowns are alike.