How to create a drill-through table

An experimental approach for advanced users.
You can now create drill-down charts in Data Studio. Learn how

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.

You should regard this as an experimental feature. If Data Studio changes the way in which URL parameters are encoded, bookmark links could break. If you adopt this technique, please expect a certain amount of maintenance.

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 HYPERLINK and 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.

Please note: the technique presented here is fragile: future changes to how URL parameters are encoded could break bookmark links. Please expect a certain amount of maintenance if you decide to implement this solution.

The basic formula looks like this:

HYPERLINK(CONCAT("<URL>?config=<config_part_1>",<parameter>,"<config_part_2>"), <link_label>)

 

<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.

 

Example:
HYPERLINK(CONCAT("https://datastudio.google.com/reporting/.../page/...?config=%7B"...":"include0IN', Source ,'"%7D'), Source)

 

Note: bookmark links contain non-printable characters. These must be left in place for the link to work.

Instructions

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).

To complete this example, it's helpful to use a text editor to temporarily store and edit the URLs.

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.

Steps

  1. Create a new report.
  2. 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
  3. Add a table.
  4. Add a new page.
  5. On page 2, add a geo map.
  6. On page 2, add a filter control.
  7. 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.

Steps

  1. View the report.
  2. 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 google.
  3. 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.

Steps

  1. Edit the report.
  2. Select the table (it doesn't matter which one).
  3. In the properties panel on the right, under Data Source, click Edit.
  4. In the Data Source panel, click Open Create menu iconADD A FIELD.
  5. Create a new calculated field with this formula:
    HYPERLINK(CONCAT("<URL>",""), Source)
     
    Replace <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.
  6. Name the field Drill through link.
  7. 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.

Steps

  1. Modify the CONCAT portion of the formula by breaking up the <URL> into <config_part_1>, <parameter>, and <config_part_2> sections, as shown at the top of this sample exercise. You'll replace the hard-coded filter parameter (google, in our example) with the Source dimension. Note carefully the placement of quotes and commas, as shown below:
  2. Before:
    HYPERLINK(CONCAT('https://datastudio.google.com/reporting/.../page/...?config=%7B...%80%80google%22%7D',''), Source)
     
     
    After:
    HYPERLINK(CONCAT("https://datastudio.google.com/reporting/.../page/...?
    config=%7B...%80%80', Source, '%22%7D',''), Source)
  3. Click UPDATE.
  4. 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!

Steps

  1. Select the table in page 1.
  2. Add the Drill through link field to the table.
  3. 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.).
  4. 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.
Adding or removing controls on a report changes the configuration parameters in the URL. If you edit the filter controls or date range controls on a drill-through report, you should update the link in the calculated field to match the new URL.
Was this helpful?
How can we improve it?