BIRT: Resort Columns After Report Has Been Run

BIRT strong suit is in the realm of online reporting. One of the features that is common with online applications that display information in a grid format is the ability to click on a column header and have the information resort based on that column. In this article I will show you how to do just that with BIRT.

The report that we will do this on is a report that lists customers and their payment information. It will be based off of the Classic Cars databases. These instructions are for BIRT 2.2 and will not work on previous versions.

1. Create a new report in an BIRT reporting project. Call this new report “customerPayment.rptdesign”.
2. Create a new Data Source from the Classic Cars Inc. Sample Database.
3. Create a new dataset using the following query:

select
 *
from
 CUSTOMERS,
 PAYMENTS
where
 CUSTOMERS.CUSTOMERNUMBER = PAYMENTS.CUSTOMERNUMBER
and customers.customernumber = ?

• a. Name the parameter dsprmCustomerID
• b. Under the dialog for the data set parameter, create and link to a report parameter called rptprmCustomerID. Set it as a text box entry.

4. Drag and drop the newly created data set over to the report design pane. Delete all columns except for the following

• a. Customer Number
• b. Customer Name
• c. Payment Date
• d. Amount

Figure 1. Report will Look Like after step 4

5. Create a new report parameter called rptprmSortOrder. Set it to allow null values and hidden.
6. Add the following script to the OnPrepare event of the table.

//We only want to add this into our code when the value is not null for the
//parameter sort
if ( params["paramSortOrder"].value != null )
{
 //Bring in the BIRT Report Model API and for CONSTANTS
 importPackage( Packages.org.eclipse.birt.report.engine.api.script.element );

 //Create a dynamic sort condition
 var sortCondition = StructureScriptAPIFactory.createSortCondition();

 //Based on the value of the sort parameter, set the appropriate key value for sorting 
 switch (params["paramSortOrder"].value)
 {
     //Remember that for the key, we need to use the fully qualified row and field name as a string, not as a value
     case "date" :
         sortCondition.setKey("row[\"PAYMENTDATE\"]");
         break;
     case "price" :
         sortCondition.setKey("row[\"AMOUNT\"]");
         break;
 }

 //set condition to ascending order
 sortCondition.setDirection("asc");

 //Add to the table
 this.addSortCondition(sortCondition);
}     

7. In the header row, we need to create hyperlinks that will call this report, and pass in parameters to tell which column to sort by. So save the report as it is, othehrwise the parameters will not show up in the drill down dialog. Select the PaymentDate column header, and create a hyperlink like so:

• a. Select drill down
• b. Link to the customerPayment.rptdesign file
• c. Select the rptprmCustomerID field, and set the value to params["rptprmCustomerID"]
• d. Select the rptprmSortOrder parameter, and set the value to “date” with the quotation marks.
• e. Set to open in the same window.
• 8. Do the same thing for the Amount column, except set the value of rptprmSortorder to “price”.

With that done, now you can reorder the report in view time when the user clicks on the date or the amount columns. With a little more logic developed in you can have the report do both ascending and descending sorts, and even have it refresh the report without having to refresh the viewing page.