Report Sort By

[irnewbie]

Hi

 

I wonder if anyone knows how to use order by in jasper reports. Our old system (Crystal Reports) used a drop down list of order by fields, pulled from DB, and then this would be used in the report application viewer to sort by. I am unsure how to do the same thing in iReports. In CR there is a formula field menu and a sort by option where a case statement would be placed such as :

SELECT (@SortField)
CASE 'Name' : {Report.Name}
CASE 'City' : {Report.Town}
Default : {Report.State}

Our new system can support a similar DB set up with a table with a list of values to sort by and dropdown functionality built-in to the application. How do I achieve a similar sort of thing in iReport as i've described above or is there another way of doing it?

Thanks

A

[szaharia]

Hi,

The <sortField/> element handles data sorting in JR.
You can set a <sortField/> in iReport opening the Report Query dialog, and clicking on the 'Sort Options...' button there.

Hope this helps,
sanda

[irnewbie]

Hi

 

Thanks for your help. I am not sure if this will do what we need it to do; we need to be able to pass a parameter value that the user picks from a drop down list in the application then use this in the report to sort. I am thinking along the lines of maybe a parameter in the query that this value the user picks is then sent to.

 

It would only sort by one field at a time.

 

thanks

 

 

[szaharia]

Hi,

The builtin parameter SORT_FIELDS could be used to handle dynamic data sorting. This parameter should contain a list of dynamic JRSortField elements, based on user's input at runtime.
At the moment, there's no support in iReport to set this multivalue parameter, it only can be hardcoded at report filling time. This issue is already taken into consideration and will be solved as soon as possible. Until then, the below piece of code could be useful.

One should be careful with sortfields, because they are used only for in-memory sorting, ie data sorting is performed after retrieving unsorted data from the DB, and this could be both time and memory consuming. To perform optimized dynamic data sorting, is recommended to parametrize the query string instead, using the $P!{} syntax. To find out how to do it, please consult the query JR sample.

HTH,
sanda

Code:

Map parameters = new HashMap();List<JRSortField> sortList = new ArrayList<JRSortField>();JRDesignSortField sortField = new JRDesignSortField();sortField.setName(<the_field_name>);sortField.setOrder(SortOrderEnum.ASCENDING);sortField.setType(SortFieldTypeEnum.FIELD);sortList.add(sortField);//add other sortfields hereparameters.put(JRParameter.SORT_FIELDS, sortList);JasperFillManager.fillReportToFile(<the_jasperReport>, parameters, <the_data_source>);

[irnewbie]

Thank you fo the reply and information.