As a consultant for iDashboards, I work with customers to help build their dashboard solutions. Along the way, they never cease to surprise me with creative new challenges and ideas for their dashboard designs. During one of my recent trips, I was able to create a new way of drilling down through a client’s hierarchy of data using our “Change Dashboard Parameters” drilldown. In the data, they had pre-calculated aggregate scores assigned to managers throughout the company, with each individual linked to his or her own manager and direct reports through a “parent-child” type data structure. There was a column for each employee’s ID number and a column with his or her manager’s ID number. An organization’s employee structure isn’t always a fixed number of levels from top to bottom, creating a challenge when I tried to use our chart-to-chart drilldown function on this data structure. In this blog, I’ll explain how I overcame this challenge using the drilldown to change dashboard parameters. **If you would like review how a regular drilldown works, check out Jerry’s post about drilldowns: “Getting Down to the Nitty-Gritty”.
During my engagement I had data that looked similar to this:
Because this is not the typical data structure we use for drilldowns, I had to think outside the box. I had to create a drilldown structure that could have a varying number of levels. One person reporting to the CEO could have four levels of management under them, while another individual could have ten. Instead of creating ten charts, I was able to create a dashboard that only uses two charts for infinite drilldown capability (it only stops at the end of the hierarchy).
This solution contains the following elements:
1. One chart that will drilldown the hierarchy
2. One chart that will drill back up
3. A dashboard input parameter for filtering on the “parent” value
Here is an example of a dashboard built with the sample data containing these elements:
In chart #1, I have used the input parameter to filter the information where the “Manager Name” column in my data is equal to the Manager selected in the input parameter. My X-axis uses the “Name” column for its values; therefore, I am showing the scores of the direct reports for the selected manager. I set my x-axis label to “Manager” because the x-axis label must match the name of the input parameter in order for the drilldown work. So if I click on the CFO, I will drilldown to see the positions that report to the CFO.
In chart #2, I used the ViFrame chart type to display text showing who the selected manager reports to. This is the chart that will take us back to the previous manager after we have drilled down. I still use the input parameter as a filter, but this time I filter on the “Name” column instead of the “Manager Name” column in my data. For my X and Y labels, the axis labeled “Manager” is now mapped to the “Manager Name” column.
The chart labeled #4 is not part of the drilldown, but an extra chart I created so that I could still see the information about the manager I selected. It is built exactly like chart #1, but I filtered it the same way as chart #2. You can see my input parameter placed at the top of my dashboard, so I can skip down the list without drilling if I choose to. Whenever I click in chart #1 or #2, it will place their value into the input parameter and change its own filtering, creating a sort-of circular logic. After creating your charts, you only have to set up the drilldown by going to the Chart Properties and selecting the drilldown:
If everything has been set up correctly, then congratulations! You should now be able to drill up and down your data:
This technique works for ANY kind of data hierarchy you have where you varying levels of detail. Maybe instead of a corporate structure, you may be dealing with Regions and varying levels of sub-regions. The possibilities are limitless.
Just remember – the data must be formatted in a “parent-child” format similar to my sample data above. As a general rule, most – if not all – aggregation that your data may need (sums, averages) will have to be done ahead of time at the data source, depending on your desired results.
I hope this has been a helpful and informative article for you. Now go, take this technique and see what kinds of new powerful insights you can find.