Oracle BI Publisher Blog

If you worked through the last post on Flex Templates, you may have noticed that sorting doesn't work properly on numeric data.  This is the classic "sort a number as a number" rather than "sort a number as a string" problem.  This is not surprising if you think about how the data is getting to the flex template.  BIP issues a query for the data, converts the data extracted to XML and hands that off to the flex template.   In the process, the fact that the numbers in your data are actually a numeric data type gets lost.  Actually, this problem is nothing new to BIP developers: whenever you add a sort in a Word Template, you have to state whether the data being sorted is a String/Date or Number.  The same sort of thing needs to happen in your Flex template.  You need to tell flex that your numbers are really numbers and need to be sorted that way.  Unfortunately, it's not as simple as a radio button like it is in the Word Template.  In this post, I'll walk you through setting up the proper sorting.  I'll build off the template that was in my previous post.

To get a column in a data grid to sort properly, you have to add a sortCompareFunction to the definition of the column.  Here's an example:

<mx:DataGridColumn headerText="Monthly Salary" dataField="SALARY" sortCompareFunction="sortSalary"/>

So what is this function called sortSalary?  You have to write that.  The sortCompareFunction must have the following signature (takes in two objects and returns an integer):

sortSalary(obj1:Object, obj2:Object):int

The objects in this case are your rows of data.  Here's an example sortCompareFunction:

private function sortSalary(obj1:Object, obj2:Object):int {
            var value1:Number = (obj1.SALARY == "" || obj1.SALARY == null) ? null : new Number(obj1.SALARY);
            var value2:Number = (obj2.SALARY == "" || obj2.SALARY == null) ? null : new Number(obj2.SALARY);

            if (value1 < value2) {
                return -1;
            } else if (value1 > value2) {
                return 1;
            } else {
                return 0;
            }
        }

It's fairly straight forward.  Cast the column as a number and compare the two values.  The annoying part of this is that the function is specific to the column that you are sorting.  That means that you will have to create a sortCompareFunction for each column that you need to sort by.  Adobe actually recommends that you create a sortCompareFunction for every column where you do any formatting (topic for another post).  You'll notice in the source code below that I create two sortCompareFunctions: one for monthly salary and one for annual salary.  Here's the source code.  Here's the finished BIP report.  Thanks to Adobe Flex 2 - tips and tricks and my brother-in-law (you can just image how much fun the family conversations are) for helping me figure this out.