Xcelsius Drill Down Charts: Can it get any easier than this?
By Loren Abdulezer
Fig. #1
In my article last month, you saw that the Xcelsius Accordion Menu component packed a lot of punch. I want to show you another feature of the new Xcelsius that combines elegance and simplicity with powerful drill down capabilities.
Rather than thinking about a detailed setup, I want to show how easy and casual it can be to create a drill down chart.
Those of you who have not yet upgraded to either Xcelsius XL Professional or Xcelsius Enterprise XE will need to download the trial version of one of them. You can follow the links at the bottom of the Sample Xcelsius Drill Down Chart above to obtain the Xcelsius Trial Software, as well as the spreadsheet and the .xlf file for this article.
I want you to start thinking about how data would typically be appearing in your spreadsheets. Now, let's get concrete and talk about something we all can relate to -- flight delays. Just about everyone has had their fair share (or should I say, their unfair share) of sitting and needlessly waiting in airports. What do you think contributes the most to the delays? And do the delays vary seasonally?
Part 1 - Data to Munch on
The airlines have to report all sorts of information to the FAA. This information is publicly available from the Bureau of Transportation Statistics Web site (www.bts.gov).
To illustrate a very simplified Drill Down Chart, let's look at six months of summary data for causes of flight delays (Figure 2). The numbers are easy to interpret because you only have to consider five categories or causes of delay over a six month period.
Fig. #2
The basic concept of a drill down is this; you start with some summary information (such as total number of flight delays) and work your way back to more detailed information (such as how each kind of delay changed month to month).
Rather than concentrating on the spreadsheet application, I want you to pay attention to the process of how the components are set up to utilize the drill down feature. For this reason, the spreadsheet example I chose is essentially static. In a full scale application, the data you'd be using might allow you to select over a range of dates instead of the six months from November to April. You might see further sub-sectioning such as delay by specific airline, or delays within a specific airport. Adding these details to this example would only make this article longer, and wouldn't change how the Drill Down Options are set up.
Part 2 - Drill Down Setup
After you start Xcelsius and import the spreadsheet file, you can immediately start dropping components onto the canvas. Remember, the drill down feature starts from the summary information and works its way to the underlying detail. You might want to represent the summary information, like total delays, in the form of a Pie Chart (Figure 3).
Fig. #3
You can set up the Pie Chart as you normally would. When you examine its properties, you will see that Xcelsius has a Drill Down tab (Figure 4).
Fig. #4
Click on the Drill Down tab, and then click on the Enable Drill Down check box (Figure 5).
Fig. #5
The Chart Drill-Down Options allow you to associate the Pie Chart summary information (total delays or cells in H8:H12 of your spreadsheet) with your detailed information (contained in A8:G12 of your spreadsheet). The completed Chart Drill-Down Options is shown in Figure 6.
Fig. #6
You have to tell Xcelsius what kind of association you want to make between the summary information and the underlying detail. This is handled in the drop down list for Insert Value (Figure 7).
Fig. #7
In the drop down list, select 'rows'. This tells Xcelsius that when you click or move your mouse over the items in your Pie Chart, that you want the rows of your source data (i.e., the detailed underlying information) to be inserted in a special location.
The 'Source Data' where the underlying detail resides is shown in Figure 8.
Fig. #8
Your next step is to tell Xcelsius that you want to 'Insert Value in:' A5:G5 (Figure 9).
Fig. #9
Notice in the original spreadsheet (Figure 2) that I grayed out the cells A5:A12. That was a cosmetic change I introduced in the spreadsheet to remind me not to type in spreadsheet formulas on those cells. Other than that simple and optional change, I did nothing to prepare the spreadsheet for use with Xcelsius. This is important. It means that if you already have spreadsheets where you're thinking of using the drill down feature, you won't need to do anything special to prepare it for Xcelsius.
Part 3 - Connecting the Components
All the preparatory work for drill down is now done. To reap the benefits, have your other components read the data that's inserted in A5:G5. In the example for this article, I have a Column Chart reading that information (Figures 10, 11, and 12).
Fig. #10
Fig. #11
Fig. #12
The key ingredient of drill down components, which is not often stated explicitly, is that the component that enables drill down is separate from the component that uses the drilled down information.
In this case, the Pie Chart enables the drill down. It can make the association between what you click on within the Pie chart, and the rows of detailed data. The Column Chart fetches the detailed data that is prepared by the Pie Chart. Look back at Figures 11 and 12. The cells A5 and B5:G5 which is used by the Column Chart only references data in row 5. The Column Chart has no idea where all the underlying data resides. It is only getting its information from row 5.
I want to keep this article brief, but there is one point I want to leave you with; there is nothing to stop you from taking the Column Chart component and enabling the drill down for it in the same way you did for the Pie Chart. In this manner you can accomplish cascading drill down. Of course, this entails a little more planning and design, and certainly more data in the spreadsheet.
Once you realize how the various components tag team to accomplish drilling down, you will find yourself very quickly and easily putting these features to use.
The files referenced in this article can be found in the Article Reprints section of: http://www.xcelsiusbestpractices.com
Loren Abdulezer, CEO, Evolving Technologies Corporation http://www.xcelsiusbestpractices.com
©2005 Evolving Technologies Corporation - all rights reserved.