Drill Downs for Table Calculations

Looker Q&A Looker
1

Hi all,

I have a simple table calculation on a report and I would like to control the drill down. If it was a measure, I know the view file could be altered and drill fields added. However, the table calculation does not seem to have any obvious place to select drill fields.

Is this possible somewhere in the LookML or elsewhere?

Thanks all,

Kyle

2 Likes
2

I am currently trying to do something similar and would love to see a reply from Looker on this! Or anyone in the community who has solved it.

3

@c_cannon-165937

So far I have not found a way to achieve this. I am considering changing the code coming into Looker so I can build a measure with the ability to drill down.

1 Like
4

One hack that I’m using is using “buttons”.

  1. Create a single/multiple value tile involving table calculations.
  2. Create a look that you want to show as drill downs and save it.
  3. Copy the link for the above look and insert in the URL field for the button in the dashboard.
  4. When a user clicks on below buttons, look will load in new tab with the fields you want in it and there you go.

Screenshot 2023-09-29 at 5.58.00 PM.png
Screenshot 2023-09-29 at 5.58.00 PM.png2284Ă—194 12.9 KB

5

Hi, nice workaround. But how do you maintain the field from which the drill started? For example, assuming you have a graph with revenue and countries and you want to drill into the product category on revenue by country, selecting a specific one. Do you think it can be done by inserting code into the URL?

6

Hi Kyle,

Table calcs do not support drill downs currently - if you want to be able to drill into a measure or a dimension you’ll need to make it as a real measure or dimension in LookML.

1 Like
7

You won’t be able to do that unfortunately. The best way to accomplish what you want is to create a proper measure/dimension in LookML and build a drill set.

8

If it’s a dimension you can add drill fields that will carry over as a filter. One easy way to visualize this is geography.

I have my different geography dimensions as drill_fields in a hierarchy. For example:

dimension: country {
    label: "Country"
    type: string
    group_label: " Geography"
    sql:  ${TABLE}.country ;;
    drill_fields: [region, state, city]
  }

  dimension: region {
    label: "Region"
    type: string
    group_label: " Geography"
    sql:  case
            when ${state} in ('QC', 'ON', 'BC', 'NS', 'MB', 'AB', 'NB', 'PE', 'SK', 'NL', 'YT', 'NT', 'NU') then 'Canada'
            when ${state} in ('AK') then 'Alaska'
            when ${state} in ('CO', 'ID', 'KS', 'MT', 'NE', 'ND', 'SD', 'UT', 'WY') then 'Frontier'
            when ${state} in ('HI') then 'Hawaii'
            when ${state} in ('IL', 'IN', 'IA', 'KY', 'MI', 'MN', 'MO', 'OH', 'WI') then 'Midwest'
            when ${state} in ('CT', 'DE', 'ME', 'MD', 'MA', 'NH', 'NJ', 'NY', 'PA', 'RI', 'VT', 'VA', 'WV') then 'Northeast'
            when ${state} in ('OR','WA') then 'Pacific Northwest'
            when ${state} in ('AL', 'AZ', 'AR', 'CA', 'FL', 'GA', 'LA', 'MS', 'NV', 'NM', 'NC', 'OK', 'SC', 'TN', 'TX') then 'Sunbelt'
            else 'Other'
          end ;;
    drill_fields: [state, city]
  }

  dimension: state {
    label: "State"
    group_label: " Geography"
    map_layer_name: us_states_canada_provinces
    type: string
    sql: upper(${TABLE}."state") ;;
    drill_fields: [city]
  }

This way if I have a dashboard showing revenue by country, I can click in and narrow that country by state, etc. You could do that with a drill_fields: [product.category]

1 Like
9

You can use drilldowns on custom measures that you create within the explore (https://cloud.google.com/looker/docs/custom-fields). If you can recreate your table calc logic with a custom measure I would suggest trying that. Otherwise you need to update the LookML to have a corresponding measure.

1 Like
10

I didn’t even know you could do that with custom measures but that’s great!