Implementing a RANK() function with JR

[tugelblend]

Hello,

I'm currently working on a report that should contain a column, displaying the "rank" of a row in the report, regarding the value of another column. The RANK-function is available in Excel and Oracle SQL but as far as I know not with MySQL (which I'm using as Datasource).

I found some articles about implementing this function with SQL but I do not want to blow up my SQL-query any further. So I'm thinking about implementing a scriptlet that does the Job for me.

I'm quite new to scriptlet development (but I managed to implement some simple ones already). I read a post in this forum, that it is necessary to calculate something like the rank at the beginning of the reporting process because during the filling process of the report, there is no way to calculate a value of a column in dependence to the values of the same column in the other rows.

I have no clue how to access the complete result set of the report query from a scriptlet. Can anyone tell me, how to do that or tell me where I can find samples or an article about this topic? Am I on the right track anyway?

Thanks a lot.

Hauke

[Teodor Danciu]

Hi,

 

The scriptlet is not good for the data processing you need to do. Your processing needs to occur after the data has been fetched from the database and before is fed into the report.

 

You would have to write a custom query executer.

 

In JasperReports, SQL queries placed in the <queryString> tag of JRXML are executed by a JRJdbcQueryExecuter implementation that is registered for the "sql" language. It executes the query through JDBC and produces a JRResultSetDataSource instance that is then passed to the report filling engine.

 

You need to write a custom query executer that is similar to the JRJdbcQueryExecuter, but that after it gets the result set, it iterates through all the records and calculates all the ranks you need. You then return from this custom query executer a JRDataSource instance that has fields for ranks, in addition to the fields coming from the query.

In order to register your custom query executer, you need to invent a language of your own, maybe called "mySqlWithRankings", and associate it with your custom query executer, similary to how we've done it for the build-in languages in the default.jasperreports.properties inside the JR JAR. You'd put your lines in a jasperreports.properties file, in the root package of your application.

 

I hope this helps.

Teodor