Winter Olympics Xcelsius Challenge
In March of 2010, an "Xcelsius Challenge" was posted on the SAP community and developer network (SDN) site - www.sdn.sap.com. This challenge had very few rules making it ideal for unique and custom entries. Because of the minimum requirements, dashboard developers from around the world were able to showcase their creativity and demonstrate the flexibility of SAP Crystal Dashboard Design, (previously known as Xcelsius). I enjoyed viewing every entry and quickly realized the wide variety of possibilities from so many different perspectives.
In this post, I'll attempt to explain how I designed my entry, describe how I produced the basic functionality and list the components used in my entry.
Building the Dashboard
The first step is to launch Xcelsius 2008 and add the required data to the spreadsheet. I generally like to begin with 10 or so blank rows in my spreadsheet for holding "destination" values. To make plenty of room for potential formulas, I pasted the required data into cells B10:C28.
One quick observation is that multiple dimension values are associated with non-unique measures, ie. Two events have the event popularity of 3, two events have the event popularity of 2, and two events had the event popularity of 1. Because the results are not unique, we will need to pass either the Position or Row as the Insertion Type if drill down is to be enabled. Passing the value of the selected object is not an option if the results are repeating.
I started by adding a horizontal bar chart and linking it to the data. This type of chart allows the event names to be read easier than they would in a vertical bar chart. Using the concept "emphasize what's important", the focal point of the dashboard is Event Popularity.
Adding Drill Down
To successfully use the Drill Down feature, always begin by selecting the most appropriate "Insertion Type". Refer to the steps below for details on enabling Drill Down in a bar chart.
- Select the bar chart that will then view the chart Properties
- Click the Drill Down tab
- Check Enable Drill Down
- Select the Insertion Type
- Choose the Series item to pass a value on drill
- Bind the series Insertion Type to a Destination cell
Later, you'll be able to use the contents of the Destination cell of the Drill Down selection to search for associated records in your worksheet. This feature allows you to drill by value, row, column or position.
I decided to do a little research and include the gold, silver, and bronze metal counts by country. This data is accessed when an event is selected. The position of the selected event is passed to cell B11, then cell C11 contains the formula lookup the event name, =VLOOKUP(B11,A14:E28,5,FALSE).
The next step uses the event name to search for gold, silver, and bronze winning countries (for both men and women). This formula is: =VLOOKUP($C$11,$B$14:$O$28,7,FALSE). With a single click and just two formulas, the gold metal winning country is displayed for the selected event. Add a few more similar formulas and the silver and bronze metal countries can be identified.
Other Components Used in the Dashboard
- Fisheye Picture Menu component - Used as a component for selecting Olympic events.
- xHarveyBalls component - Charts medal counts. Available as a free download at: http://www.antivia.com/xComponents.html
- Print button - This is a convenient addition to any interactive model.
- Pie chart - Toggle between the xHarveyBalls component and pie chart.
- Radio button - Allows toggling of charts.
- Ticker component - Be careful to use this component sparingly and in a non-distracting way. Can divert business users away from the more important elements in the dashboard.
- Check box - View the country selected on the xYahooMap component. Available as free download at: http://www.antivia.com/xComponents.html
- Image component - Dynamically displays the flag of the selected country.
- XYahooMap - Locate the selected country on the zoomable and pannable xYahooMap component.
- Push Button component - This powerful and convenient component can be used to accomplish a variety of tasks. In my document it was used to display dashboard instructions.
Download the XLF file that created the SWF entry. Please dissect the document and let me know if you have any questions or know of more efficient ways to duplicate the functionality.
- Jim Brogden