Xcelsius Templates

Here is the full collection of Xcelsius templates from this blog. You can always access the templates using the top navigation bar.

Xcelsius Connectivity

Passing Data from a Parent to Child SWF -Passing data from a parent Xcelsius file to a nested SWF.
Source Files

Connect Xcelsius to RSS/XML- Point and click connection to XML using XML maps.
Source Files

Xcelsius Logic Examples

Dynamic Xcelsius Ranking Logic- Dynamically rank information in your dashboard using logic.
Source Files

Dynamic Excel Lookup -An Excel logic-based approach to looking up multiple rows from a selection.
Source Files

Calculating the Current Quarter in Xcelsius- Use basic logic to calculate the current quarter within your SWF.
Source Files

Xcelsius Workarounds and Best Practices

List Builder Best Practices- Making the best of the Xcelsius list builder component.
Source Files

Multi-Series Graph Drill Down- Workaround for using drilldown feature with a multi-series chart.
Source Files

Special Characters in a Table- Using trend arrows and other special characters in a table or scorecard view.
Source Files

Calendar Component- How to leverage the calendar component dates.
Source Files

Create a Reset Button - A simple work around for re-setting a range of cells.
Source Files

Embed a SWF in Excel - Step by step instructions for embedding an Xcelsius model back into Excel.
Source Files

Xcelsius Graph Drilldown

When using the chart drill down capability inside of Xcelsius, there is a limitation prohibiting the configuration of a multi-series chart to insert into the same range. In other words… Xcelsius will not allow a user to click on bars from 2 different series to trigger 1 action, which can be a problem.

To invoke this problem, enable drilldown, select an insert-in cell, change the series dropdown, and select the same insert in cell. You will get an error message that reads the following:

“Insert In Range overlaps with other series’ Insert In Rage/ Please select non-overlapping Ranges for each series.” There is a workaround to get this working using the “Insert Series In:” option. By capturing the series that is currently selected, we can perform a lookup to retrieve the correct data.

Download Source Files

Special Characters in a Xcelsius Table View

by Ryan Goodman

When binding components to cells, specifically the table view component, it will inherit the cell formatting from Excel. This includes the font, color, borders, alignment, and values. Once those cells are bound there is no way to dynamically change that format during runtime. This is unfortunate, specifically for conditional color formatting and value format. In many cases users want to display some type of visual indication within a table view. There is however a way to inject special characters into a table view. If you tried this before you may know that those characters do not display by default. There is a modification to your XLF that will enable these characters to show up.

In Xcelsius, go to File>Font>and click Unicode Font Options. This will allow you to use device fonts instead of embedding the font in the SWF. By default, Xcelsius will actually embed the Verdana font in your SWF which adds about 60K to the SWF file size. By using device fonts, you will now have the opportunity to leverage special characters within a table view. You will also notice the fonts will look sharper since the Flash player will not have to render the anti-alias fonts. There is only 1 pitfall to using non-embedded fonts… an inability to render text vertically. This will affect the X axis in some of your charts when labels are long, or chart y-axis labels. Otherwise you can leverage characters like the arrows I demonstrate below.
Download Source Files

Xcelsius Formulas: Calculating Current Quarter

by Ryan Goodman

Many times, you want your dashboard to query data based on the current quarter, month, day or year. Within Xcelsius you can easily calculate these values. The trickiest part is calculating the quarter since that is not a natively supported function.

Download Source Files

1. First you will declare the current date using the function: NOW().


2. Next you will pull the month and year from this date using the MONTH() and YEAR() functions.


3. Now is the trickiest part dictating the current quarter, which is not an available function. ROUNDUP(4*MONTH()/12,0)


4. With the current quarter, you can now decide if you want to populate other quarters. In my case I want to populate a selector with the current quarter, last quarter, and the three previous quarters. In my example you can see how I took this idea a few steps further.

Calendar Date Formatting in Xcelsius

by Ryan Goodman

If you are working with dates within a connected Xcelsius dashboard, you can run into potential issues related to formatting. In most cases, you will require a specific format when you want to pass a date as a parameter to a web service.

Using the calendar component, you can insert and visualize a user defined date. Though you can set the cell format to modify the display of that date, Excel actually stores it using the 1900 date system. This simply means that the date Jan 1, 1900 has a true numeric value of 1, and Jan 1, 2007 has the true numeric value of 39083. The problem at hand is if you bind a web service connector component to a cell that is formatted, it will still use the true numeric value and not the formatted value that you see during runtime. Here is how you can remedy this:

Download the Source Files

1. Bind a calendar component to an insert in cell
2. Use the day, month, and year formulas to extract the individual values into their respective cells
3. Use the concatenate to sting together the day, month, and year in your desired format.
   

Xcelsius Dynamic Lookup Logic: Selecting a range

by Ryan Goodman

Here is a scenario that many people have…. You have a connected model and the web service returns tabular data, but Xcelsius does not have the ability to lookup multiple rows of data with their selectors. Unfortunately, with Xcelsius 4.5 you have to rely on some Excel skills to assemble a layer of logic that will scale. The good news is I have put together a lightweight solution with the available supported Excel functions for Xcelsius. The bad news is that is painful to explain…

I did my best to point out the key components to make this model work. Your best bet is to download the Excel file and beat it up and look at the comments within the bold titles. I didn't feel that it was extremely important to build an Xcelsius model for this one, but if it would help, shoot me an email and I can put something up.
Click here to download the Excel source file

Embed a SWF in Excel

by Ryan Goodman

Often I get questions about placing an Xcelsius generated SWF inside of an Excel file. If you go through this tutorial, you will truly appreciate the single click export option in Xcelsius. In covering how to import a SWF into Excel it is important to understand that there is no way to connect the SWF to the Excel data. When you generate a SWF from Xcelsius, it is completed self contained as is not tied back to the originated Excel file in any way. All of the data, logic (Excel formulas converted to Action Script), and connectivity mapping is contained inside of that SWF. With that said, here is how you can take a SWF file and import it into Excel, though the process is the exact same for PPT, or Word.
Download Source Files

1. Open the Control Toolbox by clicking on View>Toolbars>Control Toolbox
2. In the control toolbars menu, you have an icon called More Controls
3. Scroll down and click on the menu item called Shockwave Flash Object. This will insert a container where you will load and resize your SWF
   
4. Right click on this container and click on the menu item: Properties. Note that sometimes you may get a weird behavior with this container in order to get the correct right click menu. Reference the You may need to click in the empty space on your document before right clicking on the container.
   
5. Click on the top field, (Custom), then click on the ellipse button (…).
   
6. In the property page popup, you will need to fill in the absolute or relative path to your SWF file.
7. Before embedding the SWF you want to make sure that the path you entered worked. Click OK to temporarily exit the properties popup window.
8. Resize your container and your SWF should load inside, though it will not work quite yet. As long as you see the dashboard, it worked. Now you need to embed the SWF so you can move or email the Excel file.
9. Go back to the properties popup box and check Embed Movie.
   
10. Now you need to activate your SWF so you can use it. Back in your Control Toolbox, there is a design mode button. You will need to click on it to Exit Design mode, and now you can use your SWF.