Dynamically drill within a chart on a dashboard

lookml
dashboards
(Will Savage) #1

Dashboards can take advantage of hierarchal drilling using the drill_fields parameter. This will show results in a drill preview window. However, sometimes it is desirable to drill within the context of an existing chart. For these use cases, a small bit of LookML can help!

Dynamically updated fields in action:

Imagine a dashboard that shows sales information by department (in this example, the Men’s vs Women’s clothing department):

When we filter for the Men’s department, we can automatically update our charts to show all the clothing categories within the Mens’ department:

Behind the scenes:

To make this work, we’ll use the following LookML, leveraging templated filters:

New LookML
filter: selected_department {
    suggest_dimension: department
}

dimension: product_type {
sql: 
CASE 
WHEN {% condition selected_department %} '' {% endcondition %} 
THEN ${department}

WHEN {% condition product %} ${reporting_office} {% endcondition %} 
THEN ${category}

END;; 
} 
Old LookML
  - filter: selected_department
    suggest_dimension: department

  - dimension: product_type
    sql: |
      CASE
      WHEN {% condition selected_department %} '' {% endcondition %}
      THEN ${department}
      
      WHEN {% condition selected_department %} ${department} {% endcondition %}
      THEN ${category}
      
      END

In this example, selected_department is our dashboard filter, and product_type is the dimension we use to build our charts.

If the user has not selected a value for the filter, then product_type will use the department dimension to show Men or Women.

However, if the user has selected a department, then product_type will use the category dimension, and only include values for the selected department.

4 Likes