The logical way to analyse data is to start by looking at summarised data before looking at the detail. This is referred to as drilling down. In this post I demonstrate how to define drill down functionality between two Drupal Views. This is a continuation on my series of posts showing how Drupal can be used as a BI or data analysis front end.
In a previous post, using the Chinook sample data set, I created a database view that I imported as a Drupal View. The view displays sales per annum of the different genres of music sold by Chinook. I am going to use this view as the detailed data.
I created a summarised database view by removing the year from the list of fields. The summary view shows total sales per genre for all years. I wanted to drill down on each genre to see the details sales per year. The summarised view SQL is shown below.
CREATE VIEW vw_genre_total AS
SELECT
c.Name AS Genre_Name
,SUM(a.Quantity) AS Total_Quantity
,SUM(a.Quantity*a.UnitPrice) AS Total_Amount
FROM
InvoiceLine a
JOIN Track b ON a.TrackId = b.TrackId
JOIN Genre c ON b.GenreId = c.GenreId
JOIN Invoice d ON a.InvoiceId = d.InvoiceId
GROUP BY
c.Name
I started implementation of drill down in detail view. I defined a contextual filter on the genre field.
Then in the summary view I rewrote the genre field as a link to the detail view. The two views must have a common field that can be passed from the summary view as a filter to the detail view. Below is Genre field configuration form showing how link is defined.
The summary view is shown below.
You can define many levels of drill downs. Staying with the Chinook example, you could define a drill down on the yearly genre total to the monthly totals. You can also define drill downs to different views giving the end user the option to see different detail data. The drill downs defined in the sample are on an external database table which could be your data warehouse. This functionality is a powerful way for users to access and analyse their data.