Xcelsius 2008 charts are in many ways similar to Excel charts, but there are some differ- ences. One facility worth noting is the ability of Xcelsius charts to recognize mouse events and drill down. In this section, we’ll look at how to set up a chart like a pie chart and drill down to a column chart.
55 Using the Dashboard Design Facilities of Xcelsius
3 Pie charts are good for providing broad summary information (such as sales totals by each of four regions) but not highly granular detail. The finer detail (such as month-by-month sales) might be better left for a column chart or line chart.
You should begin this example with exactly the same dataset used throughout this chapter. Somewhere below the bottom of the data, tabulate the total sales for the 12 months for each of the regions. You could place the totals in cells B24 through E24.
Drop a Pie Chart component on the canvas and map it to the region totals (see Figure 3.9).
Figure 3.9 Pie chart is linked to the North, South, East, and West totals.
Next, you need to enable drill down. In the properties panel for the pie chart, select the Drill Down tab and click the Enable Drill Down check box. When drill down is enabled, you can specify how you want the drill down to work. Five different ways, or insertion types, are available:
■ Position
■ Value
■ Row
■ Column
■ Status list
While your pie chart data stretches across a row (B24:E24), the monthly detail for each region runs down individual columns. That is, the monthly data for the North region is located in column B. The monthly data for the South region is located in column C.
56 Chapter 3 Getting Familiar with Xcelsius 2008
3
Columns D and E hold the East and West sales data, respectively. If you want to drill down to the monthly detail for a given region, you need to look for the data in columns. For the insertion type, select Column (see Figure 3.10). The source data that’s needed for the monthly estimates has to come from columns B, C, D, and E, spanning rows 9 through 20, or cell coordinates B9:E20.
Figure 3.10 Drill down settings within a pie chart.
The objective here is that when the user clicks or moves the mouse over a specific region in the pie chart, an event should be triggered that retrieves one of the columns in the source data and pushes the selected column of data onto a destination. When the needed data has arrived at a destination, it can be used by other charts, such as a column or line chart. The destination can be a range of cells elsewhere on the spreadsheet. In order for the pie chart drill down feature to work correctly, it needs to be told where the destination is. For the time being, you can designate cells B27 through B38—basically one cell for each of the 12 months.
The next step is to drop a Column Chart component onto the canvas, perhaps placing it immediately to the right of the pie chart. This chart doesn’t need to do any fancy footwork. The pie chart has done all the hard work setting up the drill down information. The col- umn chart only needs to read the contents straight off the destination cells (B27:B38). There are just a couple problems here. You have the numeric data for a particular region in the destination cells. How do you know which region the data refers to? All that has been transferred are the 12 monthly sales estimates. It certainly would be nice to have the region name displayed in the column chart. Was column the correct insertion type?
57 Using the Dashboard Design Facilities of Xcelsius
3 It turns out that more than one kind of insertion type can work (for instance, in this exam-
ple, position could also be used). However, column is a perfectly appropriate choice. It’s just that we didn’t do a good enough job of specifying the source data and destination.
Part of the information needed is the region name. The source of this resides on row 8. So, really, the source data should start from row 8 instead of 9 and continue through row 20. (The source data cells should be B8:E20, and the destination cells should be B26:B38.) When pushing the data, there is no reason not to combine non-numeric and numeric data. However, when they are read, they need to be read separately.
Now you can create a completed dashboard with drill down (see Figure 3.11). The monthly data is picked up in the column chart. The chart subtitle is linked to cell B26 so it correctly reads the region name. Still, there’s something possibly wrong with this chart. Can you guess what it is?
Figure 3.11 Drill down is working, but something is wrong. Can you guess what it is?
Although the chart is not super polished, there’s nothing that outwardly appears out of alignment. It would be nice to have commas in the numbers. And the months running along the X-axis are a bit scrunched up, but so what? There is something else that weighs in much more heavily. Look at the vertical bars in the chart. Is it reasonable that in every successive month, the monthly sales is always greater than in the prior month?
The data graphed in the dashboard only reflects the projected sales. It is possible that the person who prepared the projection was overly zealous. Look carefully at all the data in the spreadsheet. There is historical sales data as well. The historical data also has the same anomaly. It turns out that the data you have been looking at is cumulative sales data. Calculating how much cumulative sales jump from one month to the next reveals the cor- rect level of sales and is what you should be using in your dashboards.
There’s an important lesson to be learned from this: The data provided in this example hap- pens to be cumulative sales but is, nonetheless, valid. The dashboard for the drill down is correctly set up to take monthly data, aggregate it for a pie chart, and interactively drill down to the appropriate monthly detail. There is nothing fundamentally wrong with the dashboard design or, for that matter, the data collected and generated. What’s wrong is that that there is a grand disconnect between the world in which the data gets captured and assembled and the world where the data is presented and relied on by interested parties (for example, the CEO, investors, whoever uses the dashboard).
58 Chapter 3 Getting Familiar with Xcelsius 2008
3