Documentation Drilldowns FAQs

Drilldown example

A Drilldown can refer to a variety of operations, but it usually means moving from summary to detailed information. Create Drilldowns in dashboards to look deeper into your data and get down into particular data points; you could look at blog performance for specific blog post or dive into the activity of a specific blog post.

In this example, we’ll use our SaaS Company Demo Data data source to create a chart showing the number of marketing campaigns for each month. We’ll have a Drilldown that selects for the number of marketing campaigns per campaign ID and drills down into each day of that selected month. This is useful because if you see a spike of activity, you can drill down into a daily view of that specific campaign and figure out what happened.

In the Visual SQL interface

1. Create your Dashboard Controls

First, establish which Dashboard Controls you’ll use to create the dashboard. We want to drill down by time and campaign. Create a Calendar to filter by time, and a Dropdown to select the campaign ID.

Want to map a Hidden Variable to your Drilldown chart? Make sure to select Can Drilldown in the Hidden Variable’s settings so it’s available for Step 2.

  • Calendar

    Add Calendar Control

  • Dropdown

    Set up the Dropdown Control by creating a chart in Visual SQL

2. Create your Drilldown chart

Create a chart that shows the number of contacts by month and has four layers for each Campaign ID. This will be the chart that we select a bar to drill down into and display more detail.

  1. From the Marketing table, add the Created Date, Campaign Id, and Id columns to the Query. Change the time bucket for Created Date from Day to Month by selecting this from the column’s aggregation menu. Ensure the aggregation types are Group for Campaign Id and Count of distinct for Id.
  2. Click Run Query.
  3. Make sure the order of columns in your result set is as follows (left to right): Created Date, Campaign Id, Id. If they aren’t in this order, use a Reorder Columns Action to put the columns in the required order. Once your columns are in the correct order, add a Pivot Action, keeping the default settings for the sort order and aggregation (i.e., Ascending and SUM).
  4. Select Bar for the chart type. You can optionally customize the Bar chart by opening the chart settings.

  5. Click the Gear icon in the chart preview pane to open the chart settings.
  6. Navigate to the Drilldown tab.

    For the Linked Dashboard, select the dashboard where your Dashboard Controls reside.

    Map the Calendar to the x-axis of the chart (since the dates are displayed on the x-axis) and the Dropdown to the series.

    Map your Controls to the Drilldown chart

  7. Click Done to save the chart settings.

3. Create another chart that shows the Drilldown results

Create another chart that shows the number of campaigns created by day, filtered by the Campaign ID and the date. This is the chart that will show the Drilldown results after selecting from the first chart. This chart won’t display any data yet since we haven’t selected a value for the Controls.

  1. Again, from the Marketing table, add the Created Date and Id columns to the Columns section of the Query.
  2. Add the Created Date column to the Filters section, select between and including as the filter operator, then use {CALENDAR.START} and {CALENDAR.END} as the start and end dates, respectively. Replace CALENDAR with the name of your Calendar Control if you gave it a different name.
  3. Also add the Campaign Id column to the Filters section, select is one of as the filter operator, then type the name of your Dropdown wrapped in curly brackets as the filter value. In this example, we used {CAMPAIGN_ID}.
  4. Click Run Query.
  5. Even though it’s not showing any data yet, you can still choose your chart type. Select Bar for the chart type.

Create the chart connected to your Dashboard Controls

4. Test your Drilldown

Because the chart from Step 3 is already connected to the Calendar and Dropdown Controls, it’ll update automatically when those Control values are updated upon clicking an area of the Drilldown chart. Click on a bar from the Drilldown chart to make sure everything works!


In the Data Explorer interface

Check out our step-by-step video showing another example of setting up a Drilldown between two charts:

1. Create your Dashboard Controls

First, establish which Dashboard Controls you’ll use to create the dashboard. We want to drill down by time and campaign. Create a Calendar to filter by time, and a Dropdown to select the campaign ID.

Want to map a Hidden Variable to your Drilldown chart? Make sure to select Can Drilldown in the Hidden Variable’s settings so it’s available for Step 2.

  • Calendar

    Add Calendar Control

  • Dropdown

    We created a multi-select Dropdown with Show None for the Empty state and no initial or default values.

    Set up the Dropdown Control by creating a chart in Data Explorer

2. Create your Drilldown chart

Create a chart that shows the number of contacts by month and has four layers for each Campaign ID. This will be the chart that we select a bar to drill down into and display more detail.

  1. From the Marketing table, drag Id into the Measures section.
  2. Drag Created Date and Campaign Id into the Dimensions section. Change Create Date’s time bucket to Month of.
  3. In the Pipeline, click on +Add Transformation and choose Pivot Data. Keep the default settings for the sort order and aggregation (i.e., Ascending and SUM). Click Apply & Close.

    Add a Pivot Data step in the Pipeline

  4. Click Settings by the chart title to open the chart settings.
  5. Navigate to the Drilldown tab.

    For the Linked Dashboard, select the dashboard where your Dashboard Controls reside.

    Map the Calendar to the x-axis of the chart (since the dates are displayed on the x-axis) and the Dropdown to the series.

    Map your Controls to the Drilldown chart

  6. Click Done to save the chart settings.

3. Create another chart that shows the Drilldown results

Create another chart that shows the number of campaigns created by day, filtered by the Campaign ID and the date. This is the chart that will show the Drilldown results after selecting from the first chart. This chart won’t display any data yet since we haven’t selected a value for the Controls.

  1. From the Marketing table, drag the Id column into the Measures section and Created Date into the Dimensions section.

  2. Add Campaign Id and Created Date into the Filters section. For Campaign Id, select is one of for the filter operator, then select your Dropdown for the filter value. For Created Date, change the range to between and including and use the dropdown menu to choose {CALENDAR.START} and {CALENDAR.END}.
  3. Click Run Query.
  4. Even though it’s not showing any data yet, you can still choose your chart type. Select Bar for the chart type.

    Create another chart to dislay the drilldown

4. Test your Drilldown

Because the chart from Step 3 is already connected to the Calendar and Dropdown Controls, it’ll update automatically when those Control values are updated upon clicking an area of the Drilldown chart. Click on a bar from the Drilldown chart to make sure everything works!