How Drill Down and Drill Anywhere work at the SQL level

Summary

To best illustrate how Drills work from a SQL perspective, we'll cover happens under the hood of the two main Drill report types when a user interacts with that report.

Drill Anywhere & Drill Down Mechanics

From a SQL perspective, Drill Anywhere and Drill Down work in exactly the same way. When a user clicks to drill into a piece of data, Yellowfin automatically updates the query using two steps:

1. The WHERE Clause: Whichever field or value the user clicks on gets instantly added to the WHERE

   clause as a filter.

2. The SELECT Statement: The original column gets substituted out and replaced by the new target field inside the SELECT DISTINCT

   statement.

The only difference between the two is that Drill Anywhere lets the user choose any field on the fly, while Drill Down limits them to a specific, explicitly defined hierarchy set up by an administrator.

A Quick Note on Drill Through

It is worth noting that Drill Through does not work in the same manner. Instead of manipulating the current query, configuring a Drill Through actually creates a link into an entirely separate report with its own independent SQL structure.

Examples

To best illustrate how Drills work from a SQL perspective, we'll start by exemplifying how Drill Anywhere works. 

Whichever Drill field you click on gets added to the WHERE clause, then, whichever field is clicked in the Drill itself gets substituted out in the SELECT statement:

In using the example above, if we drill on ‘Luxury’ in the Demographic column, into the 'Company Name' field, you’ll see the following result in the SQL:

'Company Name' (‘AGENCYNAME’ in the database) has been swapped into the SELECT DISTINCT statement and 'Demographic: Luxury' has been added to the WHERE clause.

Drill Down works in exactly the same way from a SQL perspective, except instead of providing you the option of going anywhere, the user provides Yellowfin with the list they have defined – you’re limited to a specific, explicitly defined hierarchy.

You can read more about Drill Down and Drill Anywhere reports, including how to set each up, in our Wiki by clicking the respective links. 

You may also reference our Wiki documentation on Drill Through Reports, just be aware that when configuring Drill Throughs, you are actually configuring links into entirely separate reports, so this feature does not work in the same manner as Drill Down and Drill Anywhere.