As the gas prices at the pump increase, I wanted to see just how much we are paying for gas here in California compared to the rest of the country. To do this, I utilized Xcelsius 2008 to build a dashboard not only to help make information easier to consume, but also to showcase a sample/viral dashboard for my blog.

The resulting dishoard exemplifies the benefits of using interactive data visualization compared to static HTML tables/reports. At a glance, I can quickly asses what I am paying at the pump in relation to the national average and state averages.

Just showing the data wasn't good enough...I wanted to provide additional analysis to transform the $/gallon data into something meaningful. A simple calculator was added to illustrate what we will pay per month and year, given the current price.

View full size example

All data for this dashboard originates from AAA.com. I am not providing source files for this dashboard at this time, but if you have any questions about what I have done, please add them to the comments in this post. If you are interested in using this dashboard in your web site for non-profit, please contact me.

I have several people point out that the gauges are distracting, which is absolutely correct. I have reverted back to the original graph version that can be viewed here:
View Gas Price Dashboard v0.9

I recently worked on a project with Xcelsius 2008 where we used the XML maps connectivity option load data into a dashboard. I have used this feature for a long time in Xcelsius 4.5, but found some challenges that I had to work through so I will share them with you below.

• If you are using Excel 2003, by default you can not get access to the XML maps dialogue. You have two options: Do your XML maps in Excel, and import into Xcelsius, or insert the XML Source menu item. To get the XML Source menu button, right click in your Excel toolbar and click Customize and navigate to the Commands tab. Select Data, and scroll down to the XML Data source option. You will click and drag the XML Source button into your toolbar.

• If you are using Excel 2007, you will need to enable the Developer tab within your toolbar/ribbon. To do this, open Excel 2007 outside of Xcelsius, click on the Office icon (the round button in the upper left corner), and select Excel Options. In the first tab you will see a tab labeled "Show Developer tab in the Ribbon." Check this box and you will now have access to the XML source option, within Xcelsius. From there, the process for configuring XML maps is the same in both Excel 2003 and 2007.

• If you use XML maps inside of Xcelsius, I found some odd behaviors when I try to re-import XML maps. I have not yet pinpointed the cause, but I found that if I re-bound my XML maps after changing the XML structure slightly, that items were miss-matched. The result was me having to create a new Excel tab and re-importing my XML maps there. That said, I would recommend having a separate tab for each XML map source you import.

• Make sure the application server containing your XML file or script has a cross domain policy file. If the XML source is on your desktop PC, you shouldn't have any problems.

Hopefully these tips will get you going in the right direction. If you have any experiences with this feature, please post them as a comment and we can add them to this best practices sheet. Thanks.

After doing a write-up about saving scenarios, I figured I would create a simple application with Xport Wizard showing how to save and load comments from an XML file.

The only issue with an application like this is maintaining the data that people submit. To edit or delete comments, I have to go the XML file to delete records.

This example also illustrates a few workarounds that I often use when I build applications like this.

Download Source Files

Adjusting for browser caching: A browser will cache web service connections that a SWF executes, so you have to compensate using a random variable. In my Xcelsius file, I append a “?” plus a RAND function, which ensures that each time I execute my web service, the URL is different.

Prohibiting user clicks: After you submit a comment, you will notice that the send comment button is disabled. In addition, when you view previous comments, you can not click and modify the input text area. To achieve this, I use background components with the capture mouse events feature checked. This invisible background component is layered over the components I want to display but not allow a user to click on.

If you browse through the example, you will probably find a few other interesting ways that I leverage light Excel logic with component properties to achieve the desired functionality I wanted in my dashboard.

To learn more about Xport Wizard, visit http://centigonsolutions.com/xportwizard

There are several reasons why end users desire the ability to save information from their dashboard for future use. Hopefully with this article, I can sort out what you have available to you in order to solve some of these scenarios below.

1. Save a personal scenario- A dashboard that contains some level of runtime scenario analysis (what-if) users want to save personalized scenarios based on their inputs to share or recall them at a later time.
2. Save a shared scenario- A dashboard that contains some level of runtime scenario analysis (what-if) users want to save scenarios for all other dashboard end users to see.
3. Offline usage- A dashboard that resides on the desktop that connects to live data, where the end user wants to take the SWF offline and still have access to the data.
4. Write-back- A dashboard contains multiple inputs and functions as a form to write back to a database. This write back data is then used as a data source for the dashboard.

The path of least resistance to saving scenarios is achieved through the local scenario button. This Xcelsius component works similar to a cookie in your browser. The local scenario button takes a snapshot of the dashboard’s current state. This snapshot includes both the data and selections and is associated to the SWF if it is on your desktop or hosted on a server. If you access the same SWF in another browser session or after you re-start your computer, the scenarios will remain. This functionality also works using other deployment methods including Power Point and PDF.

Download Source Files

I use a browser cookie to explain this functionality because the local scenario button does have similar limitations:

• Local Scenarios will only work for the local PC end user. Other people who access the SWF on your server will not have access to your scenarios.
• If you re-publish your SWF and overwrite the existing file, your scenarios are cleared. There is no way to transfer the scenarios to your new SWF.
• You cannot re-use the saved scenarios for other applications to access.

In our initial 4 scenarios we have solved #1 and #3. The only thing to remember with saving scenarios for connected dashboards, is the scenario will only grab the data present within the dashboard at that moment. If you are using parameterized queries to retrieve data, you may need to save multiple snapshots to tell your story in an “offline mode.”

For #2 and #4 you have two potential options.
Business Objects customers can potentially use Take action as a Web Service, which is an application that generates web services for writing back to a data base. I have not used this in any projects yet but it looks pretty nice.

Non-BOBJ customers or those who don’t have time or resources to create a database can use Xport Wizard to generate XML files on a server from Xcelsius, then load them back into the dashboard using the XML data connector. I have been using this utility for a while and love it because of its simplicity. Some of the XML data button binding gets a little tricky depending how complicated your dashboard saving is, but I have had tremendous luck so far.

Hopefully this clarifies what options you have available to you.

Xcelsius 2008 introduced a global dual axis chart capability that works quite well. I put together a simple example below, illustrating the technology. A dual axis chart is relevant when you are trying to draw indirect correlations between two different measures. I have never seen an effective use of dual axis charts where same measure is displayed on both y axis. The Y Axis toggle in Xcelsius is locate in the general tab for most vertical oriented charts.

Here are a few best practices that make these charts easier to visually digest in Xcelsius:

1. Always include vertical axis labels for the left and right Y axis.
2. Always show the legend on the top or bottom of the chart to ensure there is enough room for both vertical axis labels and titles.
3. Use colors that make it easy to differentiate between each series.

Download Source Files