I recently had a requirement to show a month over month change by percentage in an Xcelsius chart. As you may have found out, there is no way to customize the tooltip labels within Xcelsius and SAP Crystal Dashboard Design charts. To fill in the requirement, I came up with a interesting workaround to use a second chart, with the new chart labels feature from SP3 to display month over month changes.

Pros- You can get closer than prior versions of Xcelsius to get what customers ask for in charting with all other functionality like ignore end blanks intact

Cons- I have to insert a second chart and you can only display one value.

In a perfect world, I would be granted with 100% control over what data I display in Xcelsius chart tooltips. That way I can show the following for any bar:

• Bar value the
• X-Axis label
• Series name
• Month over month change both as a value and as a %
• Change the color of the % change based on pre-determined alert thresholds

All things considered, if you only have a few charts in your dashboard this workaround is not too bad, and looks great.

Here are a few notes in how to re-produce this functionality.

Configure the first chart

1. Insert the first chart which will be the primary chart that your user will view
2. Bind the data, axis labels, series name, etc
3. In Appearance>Axis tab, hide the minor and major gridlines
4. Delete the title and sub-title. Since you will overlay 2 charts on top of each other, you don’t want to worry about the titles. Instead you can use labels.
5. On Appearance>Layout> Check hide chart background

Configure the second chart

1. Copy and paste the first chart
2. Change the X-Axis labels to the % delta for month over month. If you complete this step and thought I made a typo, I did mean the X Axis labels and not the values. What you want to end up with is a second chart where the bars are identical, but the x axis labels are percentage values. I know it does not sound correct, but the magic will happen in the next few steps.
3. Go to Appearance Tab > Text
4. Hide the horizontal axis lables.
5. Check “Data Labels” to display the data labels.
6. While on the same tab go down an un-check “Value” and check “Category Label”
   
7. Now you should see the percentage values.
8. Align the two charts on top of each other using the lower left vertical and horizontal axis points so they sit right on top of each other
9. Re-size you second chart’s height so the vertical axis labels sit right on top of each other
10. I usually go back and make the second chart’s vertical axis lables white. This way I don’t get any odd shadows if it is off by a few pixels.
11. Move the second chart behind the first and you are done!!!

Xcelsius is limited in chart formatting options which we have to find creative solutions for. Making due with what we have, one recent requirement I had was to display a variety of suffixes for an energy related dashboard.

If you use currency as the chart format, you can easily turn off the pre-fix and type a custom suffix. In my case I was able to enter kWh for an energy dashboard.

Download Source File

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.