Drill down table example

Learn how to use custom bookmark links to create a drill down table.

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

This article is provided as a proof of concept. Note that if Data Studio changes the way in which URL parameters are encoded, bookmark links could break. For this reason, you should regard implementing drill down tables in this way as experimental. Please expect a certain amount of maintenance.

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

Instructions

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

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 down 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 another table.
  6. On page 2, add a filter control.
The dimensions for both of the tables and filter controls should default to Source. If not, select this dimension manually.

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

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 down 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 down link field to the table.
  3. 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.).
  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 down report, you should update the link in the calculated field to match the new URL.
Was this article helpful?
How can we improve it?