Skip to content

Adding Drill down in query - based visualizations

Pre-requisite

Drill Down allows users to click on a data point (like a bar in a chart) and see more detailed data underneath it — typically moving from a high-level summary to more granular insights.


To build on our example from the filter components with query based visuals here's what a drilldown will look like:

Note

Notice how when the country "USA" is clicked, users can view the number of customers in each state within "USA". Similarly, users can click on any country, and "drill down" into its granular details.

alt text


To set this up in query based visuals, following is required:

  • Add the query to fetch data associated with drilldown
  • Update the drilldown query to accept filters (HINTs), which fetches only the required granular details.
  • Save and refresh the visual.
  • Finishing up.


STEP 1: Add query for drill down.

  • Open the query panel for the visual

  • Click on '+ drill-down' to add query for fetching data for drill down. alt text

  • Write the query to fetch state wise data at 'Drill Down Level 1' alt text

Warning

However, the query still does not know what set of states to fetch - as that will depend on which country the user interacts with. Lets fix that next!


STEP 2: Update the drilldown query to accept filters.

If the user click on "USA", the WHERE clause in the query should have country='USA'.
Similarly, if the user click on "Canada", the WHERE clause in the query should have country='Canada'.

Hence, we need to pass a hint for PersivX to be able to correctly update the query. The hint for drill down is passed using :_harbour_drill_:<CONCERNED_COLUMN>:undefined:

Note

Both :_harbour_drill_: in the start and :undefined: are important for the drilldown to work!

Before

SELECT state, COUNT(customerNumber) as numberOfCustomers
FROM [customers.xlsx]

GROUP BY state
ORDER BY COUNT(customerNumber) DESC

After

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


Last step is to add all columns used for drilldown in the SELECT part as well. This allows PersivX to correctly place the new rows in the current visual. In our example, we are using country column to drill down, hence we add the same to SELECT and GROUP BY clause as well.

Before

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

After

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




STEP 3: Save and refresh the visual.

Now users will be able to interact with the visual to view state wise details alt text


STEP 4: Finishing up.

Move the state attribute from "Show Me" to "For every" section to frame out story - "For every country, then by every state Show me number of users".

Users will now be able to drilldown into all countries at once. alt text

Warning

Complete drill down buttons may not show up sometimes, this bug will be fixed soon. For now, you will have to save and reload the report once. Or simply ignore and go ahead with next visuals.



Important points to remember

If the drill down does not work as expected, ensure that:

  • Drill down query has been added
  • Drill down HINTs have been passed in the WHERE clause
  • Columns used in drilldown are added to the SELECT clause as well. Most databases will require you to add them to GROUP BY clause as well to make sure query is correct.



Next, lets look at drilling down from / into multiple columns at once.