In Feb. 2008, I wrote an article, Overlay 2 Xcelsius Charts. Since then, this technique has been burried in the archives and many people have missed it. Thank's to Jesse Aden's own creation of this solution, he has provided new guidance that is extremely helpful in the article below:

By Jesse Aden
VIA Consultants

I often got frustrated trying to show a bar or column chart with the projected/targeted data next to the actual, as it wasn’t very visually appealing. To fix this, I tried using a stacked column chart, which would look alright, but the figures would always be off.

Fixing this turned out to be very simple. Just overlap (exactly) two of the same bar/column charts.

Download Source File

A few things to watch out for:
1. The top graph should display the actual (current) data, where the bottom should be the projected or targeted.
2. On the top graph, make transparent the background, major & minor markers, and the legend (if using alerts).
3. The default colors for both are the same, so until you either apply alerts, or change the series color, it will look like it doesn’t work.
4. After changing the color, make the back chart’s bars bigger than the front charts’. It shows exactly what is intended, and accounts for when the target is made, or passed.
5. The titles have to be the same. Don’t put in a title on one and delete the title for the second.
6. The scales have to be the same. This means that automatic scaling is out of the question, and the scale must be based off of the highest number of the displayed series. For example, I have excel search through the display values (target and actual) to find the MAX, then round-up and add a certain number over that. It looks better this way, instead of just rounding up.
7. Finally, remember that if you use alerts, you cannot use a legend.

When you have selectors in Xcelsius, there is no good way to create an "all" selection. Here is a technique that I use which requires 1 extra column containing an IF() statement and a second transparent selector. Overall the solution is extremely lightweight without a lot of extra work while remaining completely data driven. I have another technique with 2 dimensions, where you can dynamically roll up the data using Filtered Summary, so I will try to post that one next week.

Download Source Files

Here is a simple trick for re-arranging tabs within an Xcelsius Tab Set component. The issue is once you add tabs, there is not a built in component property to chage tab order. However, if you open the object browser window, you can see that the tabs are organized in as sub-groups.

Before:

Changing the order of these groups will affect the tab ording in the component. With a canvas selected, you can hit the "-" or "+" key on your keyboard to move the tab forward or backward. Otherwise you can leverage the built in down and up arrows within the object browser. I advise aganist clicking and dragging the canvas within the object browser, since it can break the component if you drag it incorrectly.

After:

I heavily advise that you name the canvases to match the tab names to avoid confusion. Hopefully this will help you avoid re-building your container component just to modify tab order.

The Dynamic Sort component is an extremely valuable add-on component that I use regularly on dashboard projects. One of the limitations of this particular component is that it only sorts data ranges that do not contain formulas. This goes back to the standard understanding that you can not use a component to insert into a cell containing a formula. Until we see another version of Dynamic Sort that will enable data insertion to alleviate this problem, here is a simple work around…

UPDATE- Feb. 2010- Since this article was written, Dynamic Sort 2.0 now features multi-column sorting and the flexibility to sort in place or into a destination range. This workaround is no longer necessary.

In this sample, I modified one of the templates off of the CentigonSolutions.com website. When in Xcelsius, I modified the data by including a simple formula that summed values from two adjacent columns. By design, Dynamic Sort will not sort ranges that include formulas.

Download Source Files
*This source file requires the Dynamic Sort component- Click here to view information and get a free unlimited use trial.

To work around this limitation, you can use a transparent label-based menu configured with insert filtered rows. The trick to moving the entire range of data with insert filtered rows, is to add an additional column as shown below highlighted in gray.

The goal is to reference the entire range (with the formulas) as source data, then insert into a new range. This new destination range will be sorted, and then linked to our visualization components.

Hopefully as you look to sort data within your dashboard this component will help you fill in this functionality. I have submitted the enhancement request to ensure that this is built into the next version of dynamic sort.

During my webinar last week Xtreme Makeover, I showed a demo dashboard where a user could drill down from a pie chart down to a bar graph and pass the color from the selected slice to the chart below. This is a simple technique for creating consistency with color, which facilitates intuitive analysis; if it were only as simple to setup in Xcelsius… In essence, I am using chart alerts to dynamically change the color for a single series chart. This method will only work with bar and column charts.

Download Source Files

Inside of my spreadsheet I associate a multiplier for each product line (1,2,3…). When I enable the pie chart drill down feature, I include this multiplier in my source data and data insertion range.

Directly under the insertion range where we link the bar chart’s data, I perform a simple calculation using the multiplier (=[value]/[target multiplier]). These rows highlighted in green within the spreadsheet are the bar chart alert targets.

Inside of the bar chart’s alerts tab, I color coded alert levels for each value in my pie chart.

With everything hooked up, you get a nice effect as someone clicks on the pie chart. What would be a better solution?...Having an ability to bind colors to the spreadsheet… As a user community you should definitely request this functionality on the BOBJ support and forum sites. With an ability to bind colors in the spreadsheet, you could enable this functionality for all components within Xcelsius, and in turn facilitate better analysis for end users.