Adding Custom Sort Order in BIRT

One of the best ways to clarify information on a BIRT report is to group the information on a key field. This field could the status of the record, who made the request, or the last person to change the record. Normally when you create a group in BIRT, the report will be sorted on the values of the group field value in alphabetical order. For example, you create a report to show a set of open work orders and group them by the STATUS field, you would get your work orders grouped in this order:

  • APPR
  • COMP
  • INPRG
  • W-PLAN
  • W-SUPV
  • WAPPR
  • WMATL
  • WSCH

But most organizations don't want the list in alphabetical order, but in functional order. They want to see the work orders that are still waiting to be issued - Wxxx status - before work that's already been issued. In this case the order they'd want to see the results in is:

  • WAPPR
  • WSCH
  • W-PLAN
  • W-SUPV
  • WMATL
  • APPR
  • INPRG
  • COMP

But BIRT doesn't have a magical sort button to organize the records based on function. To do this we need to add a custom sort filter on the group.

Custom Sort Order

Once your report is created and the group is set, open the group details page:

At the bottom of the details page, click in the Sorting button and then Add... a new sort key. This will open a dialog box similar to creating a new Data element.

Click the function button, f(x), to open the expression builder for the sort key. In the expression builder enter a if/else statement:

var pattern = /^[W]/gi; 
if (dataSetRow["status"] == 'WAPPR') {1}  
else if (dataSetRow["status"] == 'WSCH') {2}  
else if (pattern.test(dataSetRow["status"]) == true) {3}   
else if (dataSetRow["status"] == 'APPR') {4}  
else if (dataSetRow["status"] == 'INPRG') {5}  
else if (dataSetRow["status"] == 'COMP') {6} 
else {99}

Now BIRT will sort records based on the value passed by the if/else statement and not by the alphabetically listing of the group header.

Breaking it down

The expression used for the sort order is not your typical if/else statement. I added a regular expression (regex) to handle groups with multiple statuses with the same first letter. Here is a break down of each line in the if/else sorting key statement.

  • var pattern = /^[W]/gi;: This line declares the regex pattern that will be used later on in the sorting expression. The pattern is looking for any vale that has the letter W at the beginning of the field.
  • if (dataSetRow["status"] == 'WAPPR') {1}: This is the first line that is used to start sorting the group. The statement looks to see if the value of the status is equal to the string WAPPR. If it is, pass a value of 1. If not, move onto the next line.
  • else if (dataSetRow["status"] == 'WSCH') {2}: This is the next iteration to set the sort order. If the status value is equal to the string WSCH, pass a value of 2. If not, move onto the next line. BIRT will start using these values getting passed back by the sorting key expression to sort the groups.
  • else if (pattern.test(dataSetRow["status"]) == true) {3}: This is the line that uses the variable pattern that we declared on the first line. The line is testing to see if the status record begins with a W. If it does, the .test() function passes a true value, if not it passes a false. The line is checking to see if the pattern test is passing a true value with comparison of == true. If the line is equal to true, then it passes a value of 3 back to BIRT for sorting the group. If not, it moves onto the next line. However, this line will pass a value of 3 to multiple groups - W-MGR, W-SUP, and WMATL. When this happens, BIRT will roll back to sorting the groups alphabetically.
  • else if (dataSetRow["status"] == 'APPR') {4}: Sorts records with APPR status 4th.
  • else if (dataSetRow["status"] == 'INPRG') {5}: Sorts records with INPRG status 5th.
  • else if (dataSetRow["status"] == 'COMP') {6}: Sorts records with COMP status 6th.
  • else {99}: Sorts any other work order status in alphabetical order, just like the 4th line would do.

Now when a Maximo user generates the report, the work order list will be sorted in the defined sort order.

Comments

Top