How to create a drill-through table
Drill-through allows report viewers to see the data in a report from different points of view. For example, you can set up a drill-through table that lets viewers visualize selected campaign data in a bar chart or table.
click a particular record in a table and then visualize that data in new context.
Although this isn't a native feature of Data Studio, you can create simple drill-through behavior by combining custom bookmarks with the HYPERLINK function.
See also: Chart interaction filters provide a way to let your viewers use charts as filter controls.
Try it out
Try clicking a Source value in the table below (this will open a new window in your browser).
How this works
The embedded report below demonstrates drilling through from the table on page one to a geo map on page two. The Drill through link (Source) field in the table is configured as a link that passes the selected Source dimension value to the filter control on the geo map page.
If you are familiar with calculated fields, you can use the
CONCAT functions to create links in a table that include the configuration portion of a custom bookmark. You can then use these links to create a drill-through table.
The basic formula looks like this:
<URL> is the link to your report and page.
<config_part_1> is the first part of the configuration (to the left of the filter parameter).
<parameter> is the value by which the viewer wants to filter the detail table.
<config_part_2> is the second part of the configuration (to the right of the filter parameter).
<link_label> is the text to display in the clickable link in the table.
HYPERLINK(CONCAT("https://datastudio.google.com/reporting/.../page/...?config=%7B"...":"include0IN', Source ,'"%7D'), Source)
Note: bookmark links contain non-printable characters. These must be left in place for the link to work.
Follow these steps to create a drill-through table based on the Google Analytics Source dimension (or adjust this example accordingly to use a different data source).
Step 1: Create the pages
In this step, you'll create a report with 2 pages: page 1 contains a table of traffic sources and a filter control. Page 2 will display the drill-through details.
- Create a new report.
- Add a Google Analytics data source.
Be sure you can edit the data source. Otherwise, you won't be able to create a calculated field
- Add a table.
- Add a new page.
- On page 2, add a geo map.
- On page 2, add a filter control.
- Make sure the filter control dimension is set to Source.
Step 2: Create and test the destination link
In this step, you'll copy the report URLs and edit them to create the destination page URL. This will have a hard-coded filter value so you can test the link.
- View the report.
Use the filter control on page 2 to select a single Source value.It doesn't matter which value you select: you are simply generating a valid filter URL, which you will use in your calculated field. For this example, we'll select
- Copy the entire current report URL and paste it into your editor on a new line.
Step 3: Create the drill-through link
In this step, you'll create a
HYPERLINK field using the navigation link and the Source dimension as the link label.
- Edit the report.
- Select the table (it doesn't matter which one).
- In the properties panel on the right, under Data Source, click .
- In the Data Source panel, click ADD A FIELD.
- Create a new calculated field with this formula:
<URL>with the actual URL you copied in the previous step. You are doing this to make sure your formula doesn't have errors.The calculated field editor may insert closing quotes for you, which you will need to delete in order for the quotes to balance.
- Name the field Drill through link.
- Click SAVE.
If the field has errors, you must fix these before proceeding.
Step 4: Customize the drill-through link
In this step, you'll edit the formula for the Drill through link field so that it uses values from your data to set the filter condition.
- Modify the
CONCATportion of the formula by breaking up the
<config_part_2>sections, as shown at the top of this sample exercise. You'll replace the hard-coded filter parameter (
config=%7B...%80%80', Source, '%22%7D',''), Source)
- Click UPDATE.
- In the upper right, click DONE.
Step 5: Add the drill-through link to your report
In the final step, add the calculated field to your table and take it for a spin!
- Select the table in page 1.
- Add the Drill through link field to the table.
- View the report. You should see your drill-through field populated with links bearing the values from your Source dimension (direct, google, youtube.com, etc.).
- Click a link: it should take you to page 2 of the report, with the table filtered by the value you clicked on page 1.