Display the top 10 highest occurances of a field in a IReport

[jgoddard]

Hi,

 

I've researched this pretty extensively online, and perhaps I'm just not putting in the correct search phrases or looking in the wrong places.  However, I can't seem to find an equivelant to the "TopN" summary function that exists in Crystal Reports in Jasper IReport. 

I am trying to create a summary at the end of my report of the Top 10 occurances of specific test results.  I have a chart in the summary band, but would also like to display these top 10 results in a table summary as well (below the chart, similar to a table of contents from highest to lowest).  For example if the letters in the example below represent the possible choices of each test question and are displayed with the Testresult_resulttext field, I would want the report to display all of them except E) as the rest would be in the Top 10 highest results.  I hope this makes sense- I have the report code set to Groovy so maybe there is a function that would operate like a "TopN" summary?

Example:

A) 7  B) 4  C) 6  D) 9  E)  2  F) 5  G) 7  H) 11 I) 10  J) 7  K) 4

Thanks in advance!

 

 

[ajinkya_c]

Hi,

You can do this at query level, like using rank function in query to assign rank to highest to lowest and display top 10 rank in the report.

Which ever db you using  just find out function  like RANK at db level.

At report level I also not aware which function can be used if you find any then please let me know.

Thanks

Ajinkya

[netti royala]

In MYSQL to display Top 10 highest salary or Balance's use this ,Change this Query according to your needs

SELECT username FROM mytable ORDER BY balance DESC LIMIT 10

[jgoddard]

Thanks for both responses-

 

I'm pretty new at this still, so to clarify I need to add this code at the query level, which would be the sql code in the Report Query? The Variable I am using to total the occurrences of the testresult field is $V{Result Count}. So I'm assuming the code would be something like

 

Select result_text from Testresult order by $V{Result count} DESC LIMIT 10?

 

Sorry for the confusion, just want to be sure I have the right idea.

 

Thanks!

[netti royala]

Order by is used for only Columns names in DB.

In your Query "SELECT RESULT_TEXT FROM TESTRESULT ORDER BY RESULT_TEXT DESC LIMIT 10;" .Here we are displayin the TOP 10 Records in Result_Text Column,If you want to clear Solution mail me cleary question.kantanetti@gmail.com.