Drill down table example
"Drilling down" into your data lets you go from a broader or more general view to a greater level of granularity. Although this isn't a native feature of Data Studio, you can create simple drill down 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.
How to create a drill down table.
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 down 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)
Follow these steps to create a drill down 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 down 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 another table.
- On page 2, add a filter control.
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 down 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 down link.
- Click SAVE.
If the field has errors, you must fix these before proceeding.
Step 4: Customize the drill down link
In this step, you'll edit the formula for the Drill down 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 down 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 down link field to the table.
- View the report. You should see your drill down 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.