Ready, Set, Go:
Creating Traffic Light components with Crystal Xcelsius

By Michael Alexander, author of the forthcoming "Crystal Xcelsius for Dummies"

Although Crystal Xcelsius comes out of the box with many appealing and versatile components, it's always fun to come up with new and interesting ways to create your own. With just a little know-how, it's easy to create something that is both unique and innovative simply by tweaking a few settings on an existing component. In this article we will do just that by creating what I like to call the Traffic Light component.

A Traffic Light component is a score carding tool that can be used to deliver your traditional red, yellow, green scoring in a novel manner. Figure 1 demonstrates how you can implement several Traffic Light Components to give your audience a delightfully playful visual assessment on your key performance indicators.

It's Interactive!
Figure 1: A practical way to use Traffic Light components.

Tip: Feel free to download the source files for this dashboard here.

What's interesting about this technique is that each traffic light is made up of only one bubble chart. That's right, taking a simple bubble chart and strategically linking it to a few Excel formulas will make something completely new!

Step 1: Getting Data into your Chart

The first step to creating one of these Traffic Light components is by entering four data series into an Excel worksheet. Figure 2 shows the exact values that you will need to use in order to get the appropriate placement of the bubbles in your chart.

Figure 2: Create four data series to feed to your bubble chart.

You will notice that you not only have the Red, Yellow and Green series, but you also have a "dummy" series. This dummy series is used to represent the grayed-out lights when the red, yellow or green lights are not activated.

Once you have your four data series in a worksheet, save the workbook and import that model into Crystal Xcelsius. From here, you will place a Bubble Chart component onto the canvas.

Figure 3: Start a new Crystal Xcelsius model and add a Bubble Chart component.

Start the set up on your bubble chart by clicking on the Series radio button and adding a custom series as demonstrated here in Figure 4. When all is said and done, you will add four series to your chart; one for the red data series, one for the yellow data series, one for the green data series, and one for the dummy series.

Figure 4: Add a custom series to your chart.

Make sure you rename the series for easy identification. As you can see in Figure 5, you will name this series DummyLights. As you can guess from the name, this particular series will be tied to the dummy data series in the Excel model.

Figure 5: Give your data series a useful name.

Note: There is a reason we are plotting the dummy data series first. When two data series share the same data point, the plot order will determine which series will come out on top. That is to say, the series plotted first will always be covered by any subsequent series. Since we want the dummy series (grayed out lights) to be covered by the red, yellow and green colors, we will plot the dummy series first.

After you have created your new data series, it's time to fill it with data. To do this, you will assign the X Values, Y Values, and Sizes separately as illustrated here in Figure 6. Note that you are only referencing the cells in the dummy data series.

Figure 6: Reference the cells in your Excel model to assign the X Values, Y Values and Sizes for the chart.

Do the same thing for each of the data series in your Excel model. When you are done, the Properties window for your bubble chart should look similar to the one shown here in Figure 7.

Figure 7: Plot the rest of the data series in your Excel model.

Step 2: Formatting your Bubble Chart

Now that your bubble chart is filled with data, you will need to do some formatting to make it look more like a traffic light. The first thing you will want to do is change the scale behavior of the chart so that your data points are in the center of the chart. To do this, simply go to the Behavior tab of the Properties window and manually change the Maximum value for both the X and Y axis to 100 as demonstrated in Figure 8.

Figure 8: Change the Scale Behavior for your chart to Manual with a maximum value of 100 for both the X and Y axis.

Next, go to the Appearance Tab and change the Plot Settings to use Custom Colors and to adjust the bubble size to 100. Once you have made these changes, the Plot Settings should look similar to Figure 9.

Figure 9: Adjust the Plot Settings to increase the bubble size and to allow for custom colors.

Once you allow for custom colors, you can scroll down a bit to the Bubble section, shown here in Figure 10. This section contains the properties that control the colors for each of your data series. The idea here is to select each data series from the dropdown box, and change the "Colors" property to the appropriate color. In our case, we want the DummyLights to be gray, the RedLight to be red, the YellowLight to be yellow, and the GreenLight to be green.

Figure 10: Assign the appropriate color to each of your data series.

Finally, format the chart to remove the border, all gridlines, all titles, the X axis and the Y axis. Basically you want to remove any evidence that this is a chart. Once you are done formatting your chart should look like the one shown here in Figure 11.

Figure 11: Your formatted chart will look like this.

Step 3: Set Targets and Trigger Cells

In a bubble chart, the Sizes value is typically used to affect the size of each bubble, showing the relational magnitude of each data point. Interestingly enough, when the size of a data point is set to 0, that data point disappears completely. Figure 12, illustrates how we will leverage this behavior to dynamically change our chart. When the Size value is set to 0, that data series disappears, leaving only the dummy series underneath visible.

Figure 12: Adjusting the Size value to 0 will make that data series disappear; leaving only the Dummy Series visible!

So here's the idea. Instead of manipulating the Size value for each data series, we will use a formula to determine if the data series will be visible or not. We will do this by comparing some value in a trigger cell to a table of targets. In the example shown in Figure 13. H2:I6 contains a table of targets. This table identifies the ranges for each color; 0-49 would be red, 50-74 would be yellow, and 75-100 would green. Below that, we have designated cell I14 as the trigger cell. That is, the cell that will be compared against our table of targets.

Figure 13: Create a table of targets and designate a trigger cell.

Figure 14 shows how replacing the hard-coded Size values with a few simple IF formulas allow us to evaluate the number placed in the trigger cell against the table of targets. Each of these IF formulas will result in one of two values; either a 1 or a 0. If the formula evaluates to a 1, the data point will be visible. Otherwise, the data point will not be visible.

Figure 14: The IF formulas here are designed to ensure that only one data point is visible at a time.

Once your formulas are in place, all you have to do is choose how you will feed your trigger cell. In the interactive example shown in Figure 1, I chose to feed my trigger cells using a List Box component.

If this seems like a lot to go through, rest assured that once you get used to the concepts here, you will be able to create a Traffic Light component in a matter of minutes. All that being said, the fact that you can finagle a traffic light from a bubble chart should not be the only thing you take away from this article. Hopefully, after going through this example, you will start to think about how other components in Crystal Xcelsius can be used in new and original ways. Remember that you are not limited to the built-in functionality of the components you get out-of-the-box. Using a little imagination and creativity, you can create your own signature components that fit both your style and your operational needs.

Michael Alexander is the author of several books including the upcoming Crystal Xcelsius for Dummies. He currently lives in Frisco Texas where he runs DataPigTechnologies.com, providing training to beginning and intermediate users of Excel, Access, and Crystal Xcelsius.

Click here for more information on Crystal Xcelsius for Dummies.