Xcelsius’ Excel paradigm for binding data inherits Excel’s absence of dynamic cell formatting. In Excel, you define a cell format as text, percentage, value, date, etc... When you bind a component to a cell or range, it will automatically inherit the formatting. If your hoal is to filter data by measures, you may have found that there is no way to dynamically change number formats on the fly. For example, if you have a table that is filtered by sales, volume, and growth %, dynamic formatting becomes a problem. While there is a good workaround for displaying values in a table, there is no good option for graphs.

The solution for displaying dynamically formatted tabular data is converting values to text using the TEXT() formula. The beauty of the text formula is the option to define number formatting. TEXT(value,text_format)

TEXT(A1,$#,##)
TEXT(A1,##%)
TEXT(A1,#,###)

The following example illustrates how this text formatting can be utilized to present tabular data in the correct format without using multiple components. Hopefully at some point we will see this dynamic formatting built into Xcelsius as standard functionality for charting. One of the few drawbacks of Xcelsius’ Excel paradigm is the borrowing of Excel’s lack of dynamic cell formats. In Excel, you define a cell format as text, percentage, value, date, etc… When you bind a component to a cell or range, it will automatically inherit the formatting. In most cases this is a good feature. In others where you are filtering data by measures, it creates a problem. For example, if you have a table that is filtered by sales, volume, and growth %, dynamic formatting becomes a problem. While there is a good workaround for displaying values in a table, there is no good option for graphs.

The solution for displaying dynamically formatted tabular data is converting values to text using the TEXT() formula. The beauty of the text formula is the option to define number formatting. TEXT(value,text_format)

TEXT(A1,$#,##)
TEXT(A1,##%)
TEXT(A1,#,###)

The following example illustrates how this text formatting can be utilized to present tabular data in the correct format without using multiple components. Hopefully at some point we will see this dynamic formatting built into Xcelsius as standard functionality for charting.

Download Source Files

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.

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.

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…

***Since I originally wrote this article, Xcelsius 2008 has now streamlined this cumbersome workaround using the insert filtered rows option. You can read more abot this here:
http://ryangoodman.net/blog/index.php/2008/04/15/using_insert_filtered_rows_in_xcelsius_2008
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

by Ryan Goodman

Since I wrote this article, Centigon Solutions has launche a new component called Dynamic Sort, which allows you to rank data dynamically without a single line of logic. You can get this component for 2008 at http://centigonsolutions.com

I often get questions about dynamic raking within Crystal Xcelsius models so I have put together some lightweight logic that will enable you to rank up to several hundred rows of data during runtime. First we will look at the basic functions needed; then we will combine them into a simple model. Finally, I have provided a more complex model to show you how to generate some more compelling real time analysis using these functions.

Here is a list of functions that I used for my logic within the model. Click on any of the links to see detailed descriptions:
LARGE and SMALL
MATCH
VLOOKUP

With an understanding of how these functions work, let’s combine them to create our dynamic ranking logic.

1. Index cell: This index cell will be used eventually by a vlookup function once the rank is identified
2. Source Data: This is the original source data that we will rank
3. Unique Identifier. The unique identifier addresses the issue of duplicate values. By carrying the value to the 100 thousandth place, this identifier will ensure that all values in your rank order are unique without affecting the values themselves. This is important because the logic will break down if there are duplicate values.
4. Adjusted: The adjusted values will be the range that we will actually perform the LARGE function. Because we have summed the original value and the unique identifier, we know that there are no duplicate values.

5. Rank Number: We will use this rank number as our “K” within the LARGE function.
6. Rank Lookup: This range uses the LARGE function to find the Nth value (#5) within our Lookup Row (#4).
7. Match Row: Now that we know the Nth value we need to find the absolute row for which this value is located in the source data (#2).
8. Finally, with the absolute row identified (#7), we can perform a VLOOKUP of this absolute row to return any other information we desire. In this case, it was the name.

Now that you have the basics down you can check out this more advanced example and reverse engineer the source file. Let me know how this works for you or if you are able to build on this.