Tuesday, February 12, 2013

BIRT Sorting



BIRT provides sorting capabilities on many report items, including Tables, Lists, Cross Tabs, Charts and Groups.  Sorting is configured using a sort key expression.  The expression is usually tied to data that is returned in a BIRT data set.  In addition to supporting sort direction, BIRT also supports locale specific sorting and sort strength.



 The expression is entered at various locations in the designer and depends on what report item is being sorted.  For Cross Tabs, Lists and Tables the sorting expression can be entered in the property view, under the sorting tab.


The sorting expression for Charts is defined in the Select Data tab of the chart wizard, next to the expression for the category or optional grouping expression.


Group sorting is defined in the Group Editor.



If your BIRT report uses a grouped table, by default the group will be sorted by your key expression.  In some cases this may not be desirable.  Specifically when the data is pre-sorted in the order you want it to appear.  BIRT provides an advanced property to turn this behavior off.  You can access this property (“Sort by groups”) in the advanced table of the property editor after selecting the table.  


When sorting groups, Tables, Lists and Crosstabs, BIRT supports multiple expressions and the data will be sorted using the first expression, followed by subsequent expressions.  While most sort expressions are quite simple, it is possible to create a custom sort order using a more complicated expression.  Expressions can multi-lined, use report parameters and even call out to external classes.  The following multi-line sort expression returns the third order line number first in the custom sort.


var ol = row["ORDERLINENUMBER"];
if( ol == 3 ){
1
}else if( ol == 1 ){
2
}else if( ol == 2 ){
3
}else if( ol == 4 ){
4
}
 


Sort expressions can also be created and applied in a BIRT JavaScript event handler.  For example the following beforeFactory script adds a sort expression to a table group.


importPackage(Packages.org.eclipse.birt.report.model.api);
importPackage(Packages.org.eclipse.birt.report.model.api.elements);
importPackage(Packages.java.util);
importPackage(Packages.com.ibm.icu.util);
//Name Your Table
tbl = reportContext.getDesignHandle().findElement("mytable");
var lc = new ULocale(reportContext.getLocale());
sc = StructureFactory.createSortKey();
sc.setKey("row[\""+params["SortOn"].value+"\"]");
sc.setDirection(params["SortOrder"].value);
sc.setLocale(lc);
var tblGroup = tbl.getGroups().get(0);
//to put the sort on the table use the table handle
//instead of the group handle
ph = tblGroup.getPropertyHandle(TableHandle.SORT_PROP);
ph.addItem(sc);

 The examples shown in this post are available at BIRT-Exchange.