Skip to content

Drill down with multiple attributes in query-based visualization

Pre-requisite

1. Drill down to show multiple columns

Often times, when user requests drill down, details to be shown span across multiple columns.
Building on the example from Adding drilldowns in query based visuals, lets say instead of just state we also want to fetch the city and customer name - all 3 columns once.

  • Add new columns to drill down queries


STEP 1: Update query to fetch more columns when user requests drill down.

Before

SELECT country, state, COUNT(customerNumber) as numberOfCustomers
FROM [customers.xlsx]
WHERE country=:_harbour_drill_:country:undefined:
GROUP BY country, state
ORDER BY COUNT(customerNumber) DESC

After

SELECT country, state, city, customerName, COUNT(customerNumber) as numberOfCustomers
FROM [customers.xlsx]
WHERE country=:_harbour_drill_:country:undefined:
GROUP BY country, state, city, customerName
ORDER BY COUNT(customerNumber) DESC

Update the query in drill down mode and click save. alt text

Now, when the user clicks on country, all three columns - state, city as well as the customerName will be fetched and displayed on the UI. alt text

2. Drill down with filter on multiple columns

Let's say the table initially displays country, state and number of customers in each state of the country.

And when the user clicks on the row, drill down to show details by city. Only applying the drilldown filter on country column won't work this time. The filter should be applied on both country as well as state columns - to ensure only the data relevant to the row is fetched.

In this case, our chart is prepared with query - 

    SELECT country, state, COUNT(customerNumber) as numberOfCustomers
    FROM [customers.xlsx]
    GROUP BY country, state
    ORDER BY COUNT(customerNumber) DESC
alt text

Hence, the drill down query will be 

    SELECT country, state, city, COUNT(customerNumber) as numberOfCustomers
    FROM [customers.xlsx]
    WHERE country=:_harbour_drill_:country:undefined: AND state=:_harbour_drill_:state:undefined:
    GROUP BY country, state, city
    ORDER BY COUNT(customerNumber) DESC

Note

  • There are 2 drill down hints now, one for country and another for state. They are seperated by a AND clause
  • Columns used in HINTs are added to SELECT as well as GROUP BY clause

alt text

Note

  • The city attribute will show up in the panel only when you drilldown atleast once.