If you have experience with Xcelsius, you may have faced some limitations when using the built in components. Fortunately with the SDK you can extend the capabilities of Xcelsius by developing new components.

Flex and the importance of sorting

As the basics of setting up a development environment are already covered quite well in the Everything Xcelsius SDK FAQ blog post, this post will focus on the source code of OrderBy as an example. The resulting component will address one of the general principals of good data visualization: sorting data.

Displaying raw data in random order or even in alphabetical order forces the user to do the sorting of values by him/herself. In most cases – other than a time series – the user will be interested in the order of items by value and in locating the highs and lows. Ordering should be one of the first steps in “converting raw data into easily accessible information”.

The latest version, Xcelsius 2008 SP3, filled an old gap of sorting basic charts, and as you will see below, I have a component for the more complex cases that are not covered by this latest version. In addition, understanding the OrderBy component will help you build your own component so you can solve your specific challenges.

Requirements and generalities about components

The specification for the OrderBy component is to handle ascending/descending alphabetical/numerical ordering of an array, based on a specified column.

Inputs:
- source array
- column to sort
- direction of sorting

Output:
- sorted array

There are two major parts of an Xcelsius component: the actual component child, and a property sheet that contains its configuration. The properties of the component child can be hardcoded directly in the property sheet (i.e. color) or set dynamically, by binding their value to values contained in an excel sheet (i.e. source or visibility). The latter gives more flexibility, since the properties (i.e. a label bound to a cell value) will change automatically during runtime depending on the user interaction. In Xcelsius I prefer flexibility over simplicity.

The solution design in five steps

Step 1: Binding

So let’s see how to bind a region of our Excel to the source property of the component child. Below I listed the important parts of binding.

Component child (OrderBy\OrderBy\com\clariba\controls\OrderBy.as):

// Important for bound arrays! This allows detecting if the
array changed.
[CxInspectableList("source")]
…
private var _source:Array = new Array();
private var _sourceChanged:Boolean = true;
…
//----------------------------------
//  source Property
//----------------------------------
[Inspectable(defaultValue="undefined", type="Array")]
public function get source():Array
{
      return _source;
}

public function set source(value:Array):void
{
      _source = value;
      _sourceChanged = true; // Changing the source array.
      invalidateProperties();
}

Property sheet (OrderBy\OrderByPropertySheet\OrderByPropertySheet.mxml):

applicationComplete="init();"
…
protected function init():void
{
      // Sets the callback to the "continueBind" method when the
user is picking a cell to bind to.   
      proxy.addCallback(PropertySheetFunctionNamesSDK.RESPONSE_
BINDING_ID, this.continueBind);

      // Notify Xcelsius that we have finished loading this
      Property Sheet.
      proxy.callContainer(PropertySheetFunctionNamesSDK.
      INIT_COMPLETE_FUNCTION); initValues();
}
…
var propertyValues:Array = proxy.getProperties(["source", "target",
"columnToSort", "directionOfSort"]);
…
case "source":
      bindingText = getPropertyBindDisplayName(propertyName);
      if (bindingText != null)
      {
      sourceTextBox.enabled = false;// When bound the user
      cannot edit the value.
      sourceTextBox.text = bindingText;// Show the address we
      bound to. 
      }
break;
…
case "source":
      if ((bindingID == null) || (bindingID == ""))
      {
            sourceTextBox.enabled = true;
            propertyValues = proxy.getProperties([propertyName]);
            propertyObject = propertyValues[0];
            sourceTextBox.text = String(propertyObject.value);
            proxy.setProperty(propertyName, propertyObject.value);
            return;
      }
      sourceTextBox.enabled = false;
      sourceTextBox.text = proxy.getBindingDisplayName(bindingID);
      proxy.bind("source", null, bindingID, BindingDirection.OUTPUT,
"", OutputBindings.ARRAY2D);      
break;
…
<mx:Button y="32" right="25"  width="24" click="initiateBind
('source');"icon="@Embed('resources/bind to cell.png')"
id="sourceBindButton"/>

Step 2: Defining which column to sort the array in

Unlike the built in Xcelsius sort, I would prefer this to be dynamic and bound to a cell in Excel. If the column numbering starts at 1, this will help us when it comes time to integrate with a radio button or drop down selector, as we will be able to choose insertion type “Position” on the selector, which will also start at 1. The only differences (compared to the source property explained in Step 1) are casting as a Number and binding as a Singleton. See the differences below:

Property sheet (OrderBy\OrderByPropertySheet\OrderByPropertySheet.mxml):

proxy.setProperty(propertyName, Number(propertyObject.value));
…
proxy.bind("columnToSort", null, bindingID, BindingDirection.OUTPUT,
"", OutputBindings.SINGLETON);

Step 3: Setting direction of sort binding

Similarly, we need to set the direction of sort binding to a cell with possible values of 1 (ascending) and 2 (descending).  After this, bind the output array too.

Step 4: Finding a built in sort function and applying it

After all these bindings the Xcelsius specific Flex part is over. The rest is pure Flex. All we have to do is to find a built in sort function and apply it to our source array. The good thing is that ArrayCollection type of variable in Flex has a Sort function, but has a side effect too:  the ArrayCollection type of variable is not flexible regarding the number of columns; because each column has to have its predefined column name.

The solution is to use a horizontal Array inside an ArrayCollection variable.
Our ArrayCollection variable will primarily consist of two columns (i.e. objects):

1. The values of the column that we are sorting in (values copied from the original array)
2. The rows (vectors) of our original array.

Component child helper class (OrderBy\OrderBy\com\clariba\utils\RowObject.as):

package com.clariba.utils
{ 
      [Bindable]
      public class RowObject
      {
            public var sortColumnStringType:String; 
            public var sortColumnNumberType:Number; 
            public var sourceArrayRow:Array;
      }
}

Component child (OrderBy\OrderBy\com\clariba\controls\OrderBy.as):

public function createArrayCollection(s:Array):ArrayCollection
      {
         …
      }

Step 5: Populating the target Array

After performing the sort operation on an ArrayCollection variable using the sortArrayCollection function, we need to populate our target Array with the convertToArray function. The final action is to execute the sort if any of the properties change.

Component child (OrderBy\OrderBy\com\clariba\controls\OrderBy.as):

override protected function commitProperties():void
{
      super.commitProperties();

      if (_sourceChanged || _columnToSortChanged ||
      _directionOfSortChanged)
      {
            // source -> createArrayCollection ->
            sortArrayCollection -> convertToArray -> target
            if (_directionOfSort == 1)
            {
                  descendingSort = false;
                  }
                  else
                  {
                        descendingSort = true;
                  }

                  this.target = convertToArray
(sortArrayCollection createArrayCollection(_source)));

                  _sourceChanged          = false;
                  _columnToSortChanged    = false;
                  _directionOfSortChanged = false;
                        }

                        invalidateDisplayList();
                  }

Conclusion

With this article I have shown the power of FleXcelsius to provide new components that add extra value on top of the standard Xcelsius component portfolio. Feel free to re-use the source code at the end of this article in your projects. And good luck with FleXcelsius! If you have any questions or suggestions, please leave a comment below or email info@clariba.com.

If your company is using SAP BusinessObjects Xcelsius and you or your colleagues need hands-on training, we offer beginner and advanced Xcelsius courses as part of Clariba Education Services.

And if you are looking for other interesting components, we recommend also checking out Centigon Solutions‘ plugin components for Xcelsius.

Source Code

OrderBy Component Source Code
Add-on Component and Example XLF
OrderBy Example Flash SWF

This entry was posted on Wednesday, January 20th, 2010 at 8:42 am and is filed under Flex, SAP BusinessObjects, SDK, Xcelsius. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.