Troubleshooting Tips for Xcelsius: Using Snapshot

by Ryan Goodman

Here is the situation…You build your Excel logic, import into Xcelsius, bind all of your components, hit Preview for the instant gratification Xcelsius brings, and your model does not work right. With many dependencies between Xcelsius component bindings and underlying Excel logic, it could be a nightmare to reverse engineer your model just to find that a VLOOKUP is formatted wrong, or your combo box is inserting into the wrong cell.

Here is a little trick of the trade for troubleshooting your model using a feature inside of Xcelsius called snapshot. When you hit preview, navigate to the portion of your dashboard where your error occurs. While in preview mode go to File>Snapshot>Back to Excel. You will then name your Excel file (do not overwrite your original) and save it to your local hard drive. This Excel file will show the state of your model at the given point in time while you were in preview mode. This means all of your insert-in rows, logic, connectors, etc will have executed, and the Excel snapshot will show you what the underlying spreadsheet looks like. Since you should know your spreadsheet, you can quickly look to see if you inserted into an incorrect cell (this is where color coding would have saved you time), or if a formula is generating an error.
As you may or may not know, when you are using Xcelsius you are binding to a copy of your original spreadsheet that you imported. In other words, your XLF is a combination of Xcelsius configured components and your imported spreadsheet snapshot. This is why making changes to the original XLS will not be reflected in your XLF automatically.
Hope this helps!

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

Flash Variables in Xcelsius

by Ryan Goodman

Within a Adobe Flash application, or in this case Xcelsius, you can bind variables that are utilized through out the application during runtime. The benefit of Flash variables within an Xcelsius generated SWF is an ability to push values into the model when embedded inside of a HTML page. In most use cases, you would want to use Flash Variables to push global session variables that you may want to consume and re-use. Examples include: Session tokens, user IDs, and parameters passed from other embedded scripts or controls.

First we will define a variable within Xcelsius.

1. Once Xcelsius is loaded, go to File>Export Settings.
2. Click on the “Define Variables” button. In this case, I named my value sessionID.
3. Define your variable name and bind it to a cell
4. Generate your SWF.
   Now your SWF file is configured to consume a Flash variable

There are two ways to push the variable into the SWF.

1. The Adobe Flash player enables a SWF to consume data directly from the HTML for which it is loaded. In a scenario where you are dynamically generating your HTML page, you can pass variables into the SWF on load. You can also use Javascript to dynamically change the variables during runtime, enabling other HTML embedded controls to pass data into the SWF without reloading the HTML page. An HTML nested flash variable would look something like this within an OBJECT tag:

2. The Adobe Flash player also enables a SWF to consume data directly from defined parameters within a browser or path for which it is loaded. This works great for passing parameters from one SWF to another if they are loaded on separate HTML pages.

You can try the example below by entering a value, then launching another HMTL page where we consume the variable. You will be launching a URL that looks something like this:
http://ryangoodman.net/blog/flashvar.swf?sessionID=test

Planning for a Successful Dashboard Project

Over the years I have seen a many dashboard projects succeed, but some have failed for many different reasons. We learn the most from things that do not succeed because it is only then that the appropriate time and energy is truly expended to understand what was accomplished and what went wrong.

Starting on the right foot
There is no perfect formula for an optimal design that will work 100% of the time for all cases because requirements are always a moving target that revolves around delivering and meeting expectations of users who may not know themselves exactly what they want to accomplish. With that said, there are a few commonalities for the user experience that have helped propel me down a successful path:

• Create a user interface paradigm that is easy to grasp
• Provide enough visual direction to how the end user will navigate the application:
  This is achieved through effective layout design and positioning of elements on the screen. If it is not evident how the application should be used by an end user, you need to re-evaluate your design.
• Present information in an order of magnitude creating an intuitive workflow
• Be prepared to be flexible and committed to evolving the application even after deployment
• Avoid scope creep by starting with a simple deliverable in a phased approac
• Use technology for the UI that lends itself to minimal screen refresh and loading time.

Measuring Success
The measurement of success can vary, but at the end of the day, it is user adoption that will drive other success measures like ROI. You can build the perfect dashboard or application, but if it is not adopted for what ever reason, your project is a failure. The user experience design itself is one of several elements that could impact the user adoption rate. There are other contributing factors to any dashboard application’s success that are just as important as the visual interface itself. The coolest and sexiest dashboard won’t save you if you have any major problems with the following.

• Communication breakdown between end user stakeholders, IT, and designer
• Data quality
• Query response time
• Stability
• Security

Securing the Necessary Skill Sets
There is several skill sets required to create a compelling, useful, and easy to use dashboard. At the end of the day, a dashboard should connect business information and present it in a medium that enables an end user to drive action. To date I have never worked on a dashboard project where less than 2 individuals were needed because of the business, technical, and design expertise required. A dashboard project should be a collaborative process combining business performance measurements, data access, and visual presentation. If you can’t assemble a group to collectively execute on of all of these points, or have a significant weakness in any area, you could be putting your project at risk. You will need to secure resources who:

• Understand the business process, strategy, problems, and end users needs
• Define and prioritize performance measures and metrics aligned to the business
• Know the IT infrastructure and location of required data to populate the dashboard
• Have technical competency to collect aggregate and provide data to data visualization
• Have experience in information design and visual design

Please feel free to share your experiences.

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.

Design Principle 5. Consistency

by Ryan Goodman

5. Consistency: Consistency encapsulates all of the previous 4 principles into a single concept of creating uniformity and coherence across the entire dashboard implementation. Consistency is applicable in many forms not only in the dashboard aesthetics, but through information design itself.

Information design- The measure names, titles, and analysis is should be aligned with the business process and intended dashboard purpose. In many organizations, there are standard tools used for communicating and presenting quantitative information. Upon implementing a new dashboard project, you will obviously want to improve the medium for information delivery, but also use consistent charting, tabular views, and methodologies.

Navigation- In an interactive dashboard, you will need to use consistent navigation paradigms and controls for user familiarity and ease of use.

Layout- A large scale dashboard project should incorporate a standard layout, which consists of the placement and proximity of elements to enable user familiarity and adoption.

Color- The consistency of color is apparent not only for general aesthetic purposes, but more importantly for designating rules for color. For example, if you designate a tan color to represent regional (let’s say Asia) data within a bar chart, all other graphics representations of Asia should utilize the same tan color.

Text- Just like the navigation controls and graphs used to present data, text properties must also remain consistent throughout the design. You should always use the same typeface and style throughout your design.

Design Principle 4. Text

by Ryan Goodman

4. Text: The application of correct text usage will provide a dashboard user experience where quantitative and qualitative information are easily consumable. There are several considerations when inserting text within your dashboard design.

Text Aliasing- When applying text to interactive dashboard applications you may have the choice to use Anti-Aliased text with Flash based applications like Xcelsius. Anti-aliasing is applied to larger fonts, provides smoothly rendered curves and angles . When using small typefaces you always want to use device fonts, which will render the text as Aliased. As you will see in the example below, the aliased text is much easier to read with a smaller font size.


Typeface (Serif vs San-Serif)- In choosing typefaces for dashboards, you will always want to use San-Serif fonts, that is fonts without the serif accents. Arial and Verdana are san-serif fonts, and enable an end user to read text on the computer screen much easier than serif fonts (example: times roman). Serif fonts are best applied in large bodies of printed text for readability. There is no value in using multiple fonts in a dashboard, so pick 1 san-serif font that works well for you.



Font Styles (bold, italics, underline, color)- Font styles provide a means to highlight titles or values that require specific attention. That said, you want to avoid using underlined or italicized fonts as a method to stylize your dashboard. For interactive dashboards, underlined text would represent a URL hyperlink, which has become the standard for web design. If you underline values, the end user will precieve it as clickable. Bold typeface can be used to highlight values but should not be applied to large bodies of text.

Though we have thoroughly covered color as an element of contrast, you will need to choose font colors that provide easy readability. Though black is a popular choice by many dashboard designers, it may eliminate an ability to use black as a highlight color. Assuming the background is light enough, you may want to try using darker shades of gray, and see how much easier it is on the eyes. For large bodies of text in tables or paragraphs, you want to use light background with darker text for easier readability. Using a dark background with light text makes the information a bit harder to visually digest and puts a strain on the end user’s eyes.