Applying dynamic filters on Links as Drilldowns

[spolo]

Using the [drill_fields] feature to apply a Drill Down into the data is great but it’s missing some key features that my company needed that I thought I resolved that using [link] instead.  

2 features that we needed to resolve was A) the ability to sort the explore data and B) the ability to have different sets of drilldown fields depending on what the end user would like to pull out to investigate.

The later is extremely important to us especially when we have several different types of Dashboards hitting the same LookML code.  Instead of creating one massively long set field that captures a very wide extract, we needed to break them down and label them with their distinct tasks.

Again, I thought I solved this with the following code in which I actually supplied in the following post as well to share with others
 

https://community.looker.com/dashboards-looks-7/drill-down-with-multiple-choice-5557

measure: count {
    type: count
    value_format: "#,##0"
    link: {
      label: "COMBO 1 DRILLDOWN"
      url: "/explore/SPOKE_NAME/VIEW_NAME?fields=VIEW_NAME.DRILL_FIELD_COMBO_1*"
    }
    link: {
      label: "COMBO 2 DRILLDOWN"
      url: "/explore/SPOKE_NAME/VIEW_NAME?fields=VIEW_NAME.DRILL_FIELD_COMBO_2*"
    }
    view_label: "Base Data"
    label: "Count"
  }

  set: DRILL_FIELD_COMBO_1{
    fields: [
      FIELD_1
      ,FIELD_2
      ,FIELD_3
    ]
  }

  set: DRILL_FIELD_COMBO_2{
    fields: [
      FIELD_1
      ,FIELD_4
      ,FIELD_5
    ]
  }

Unfortunately we just discovered a massive flaw with this approach.  Using the link feature, it does not scope in the filters that are being applied on the table when it’s invoked.  Instead it lifts the entire unfiltered dataset from the LookML code unlike how [drill_fields] work.  After some googling, it seems the only solution would be to explicitly write in filter functionality for each and every filter like so

&f[view_name.field_name]={{ _filters['view_name.field_name'] | url_encode }}

This doesn’t work for us for several reasons.  First anytime a new filter is added to a dash, you would to go through all these links to make sure that the filter is being applied there as well.  Secondly and most important, each Dashboard and each table within each Dashboard may have a complete different set of filters applied that [drill_fields] would be smart enough to scope out.  

So this simply does not work for us.  Is there a way to add some logic that will apply filtering based on the scope of where it was invoked dynamically like [drill_fields] do or is that not possible?  Does this need to be an enhancement?  

[Naomi_Johnson]

Hi Spolo,

There is a way around that should work for you. There are some ways to do more powerful drilling with the drill fields you already have in place, such as changing the visualisation or limiting the rows in the drilldown.

In the same way you can overwrite the row limit or ordering of data, you can overwrite the fields used in the explore. Using the {{ link }} liquid variable parses the existing filters when drilling.

The following code should work. Depending on whether the measure has a default drill link or not, you may need to add a default drill_fields parameter as well with some common fields users will want to drill into. 

measure: number_of_xxx {
    type: count
    value_format_name: decimal_0
    link: {
      label: "Combo 1 Drilldown"
      url: "{{ link }}&fields=view_name.drill_set_combo_1*"
    }
    link: {
      label: "Combo 1 Drilldown"
      url: "{{ link }}&fields=view_name.drill_set_combo_2*"
    }
  }


  set: drill_set_combo_1 {
    fields: [
      field_1
      , field_2
      , field_3
    ]
  }


  set: drill_set_combo_2 {
    fields: [
      field_1
      , field_4
      , field_5
    ]
  }

Hope this helps!

Naomi

[spolo]

Hi Spolo,

There is a way around that should work for you. There are some ways to do more powerful drilling with the drill fields you already have in place, such as changing the visualisation or limiting the rows in the drilldown.

In the same way you can overwrite the row limit or ordering of data, you can overwrite the fields used in the explore. Using the {{ link }} liquid variable parses the existing filters when drilling.

The following code should work. Depending on whether the measure has a default drill link or not, you may need to add a default drill_fields parameter as well with some common fields users will want to drill into. 

measure: number_of_xxx {
    type: count
    value_format_name: decimal_0
    link: {
      label: "Combo 1 Drilldown"
      url: "{{ link }}&fields=view_name.drill_set_combo_1*"
    }
    link: {
      label: "Combo 1 Drilldown"
      url: "{{ link }}&fields=view_name.drill_set_combo_2*"
    }
  }


  set: drill_set_combo_1 {
    fields: [
      field_1
      , field_2
      , field_3
    ]
  }


  set: drill_set_combo_2 {
    fields: [
      field_1
      , field_4
      , field_5
    ]
  }

Hope this helps!

Naomi

Hi @Naomi_Johnson .  This actually doesn’t work because that liquid {{ link }} reference will make it an actually link to an explore and not a ‘drill down’ experience.  We want to stay within the dashboard with a pop-up to the underlying data, similar to how traditional drill downs work.  This is what I actually described in the other post as to why using liquid link doesn’t do what we’re trying to do.

We want the flexibility with the drill_down experience.  The liquid link doesn’t do that.

[Naomi_Johnson]

Hi Spolo,

Thanks for responding.

I’ve tested it in our instance and the {{ link }} liquid parameter makes it a pop-up in the same way drill_fields does.

Please do test the code I’ve sent; hopefully it will work in the same way it has done for me by creating pop-ups within the dashboard.

Best,

Naomi

[spolo]

Hi Spolo,

Thanks for responding.

I’ve tested it in our instance and the {{ link }} liquid parameter makes it a pop-up in the same way drill_fields does.

Please do test the code I’ve sent; hopefully it will work in the same way it has done for me by creating pop-ups within the dashboard.

Best,

Naomi

I stand corrected!  I’m not sure why this didn’t work last time when I had to remove the liquid link for the explore/spoke/view url instead to invoke the drill_down experience.

thanks again @nadeem_asghar !

[Naomi_Johnson]

That’s great to hear 🙂 I’m glad I could solve your issue

Have a great day