Saga of sorting dynamically

[blipblop]

Here is the scenario -

For my company I implemented JasperReports as the reporting software since we wanted to move away from fee based software. so far so good. We use a custom datasource to fill up our report. In other words Jasper does not connect to db at all. For a report that I am currently working on my db team has written a stored procedure. One of the things that a client can do is choose the sort by col. They would do this by checking a radio button. They have a choice of three cols. eg. radio buttons have col1, col2 and col3 as choice. The user could choose col3 as the sort button. As a result the report should first be sorted by col3 and then by col2 and col1 in any order. Always the first col will be the user's choice. My db team says that they cannot parametrize the order by clause in the sql. They have asked me to see if I can pass in the user's choice to Jasper and Jasper can take care of changing the sort order. In the past I have implemented a cross tabs report using Jasper and I know that cross tabs can sort. But as far as I know I am not sure if I can change the sorting order runtime. Would anyone know if this can be done in Jasper and if so than how.

[rcracel]

We do something similitar to what you are describing, and we do sort the values in the query, but then I guess that would depend on which db you are using.

 

We use oracle. Here is an example...

Code:

SELECT
  .
  .
  .
(CASE
    WHEN (:mySortField = 1) THEN nameLast || ' ' || nameFirst
    WHEN (:mySortField = 2) THEN id
    WHEN (:mySortField = 3) THEN ssn
    WHEN (:mySortField = 4) THEN .....
) as sort_me
FROM myTableName
ORDER BY sort_me

We pre-process the query and sub :mySort with what we want to sort it by. Might not work on your database, talk to your db people.

 

Alternatively, you could sort your collection when your data source is created, although that can slow down things a bit. If your data source is receiving a result set then you would have to copy and sort your elements to a different collection.

 

Hope that helps,

RC

[blipblop]

thanks roger

i have sent you an email, if possible please reply to me directly on that.

rahul

[blipblop]

Just an addendum

We use both Oracle and Sybase, my dbteam used the example you provided and it worked

thanks

Like I said they if you would like to stay in touch with my dbteam for any future communication, let me know through email and I will forward it to them.

[Teodor Danciu]

Hi,

 

Work is in progress in JR for having a list of fields in JRXML to sort the data source.

There'll be a new tag called <sortField> and several of them can appear in JRXML just after the normal report field declaration.

If such a list of sort fields is present, a JRSortableDataSource implementation will be used to wrap whatever data source the report initially gets and perform in-memory sorting of the records before passing the data to the report filling engine.

Note that the sort order would still be static, but would allow forcing the sort order in cases the queries return unsorted data. The sort order cannot be specified dynamically because sorting is usually tied to grouping and if you dynamically change sort order you might ruin your data groups that are already in JRXML and are fixed themselves.

But we might consider making sorting dynamic if you can provide us with a strong case for that.

 

Thank you,

Teodor

[blipblop]

Hi Teodor

Thanks for your reply. I have forwarded your reply to my dbteam and manager and am working with them to see if we can come up with a strong case for sorting in Jasper.

[aeid]

Hi,

 

I would appreciate if you could describe how you pre-process the query to substitute :mySortField at runtime while executing the .jasper file.

 

Regards,

 

Ashraf Eid

[acarbs12]

I have notice that jasper report 1.3.4 talks about sorting in the release notes, does this mean that dynamic sorting has finally arrived? If so how do I do it?

[Teodor Danciu]

Hi,

 

The <softField> tag was introduced in JR 1.2.8.

Not sure about which release notes are you talking about, but if you could give more details about what you mean by "dynamic sorting", I could come up with a clarifying answer.

 

Thank you,

Teodor

[davutgurbuz]

 You may use CASE(WHEN bla bla) structure but none of them would work.

Because when you use it you can just add  ORDER BY 'ORDER_FIELD,ORDER_FIELDNO'

it means INSTEAD OF  

ORDER BY 1,2,3

ORDER BY key,value,depart

YOU JUST SAY

ORDER BY '1','2','3' 

ORDER BY 'key','value','depart'

You need to produce complate sql from criteria first, Its a - but jasper have many +

JRSortableDataSource help dynamic sortin maybe I thing not a complate solution.