Limit number of rows to be fetched in query

Am trying to limit the total rows to be printed in a report as a user defined function. select top $P{rowlimit} is something I can do in mssql. But this report could take any orcle datasource also. So is there a way to do this jasper level ? I read about report_max_count. But am not able to assign a value to this parameter.

Can you please suggest a way to achieve this in jrxml? I can't use report scriptlet or java code.

psundaravaradhan's picture
Joined: Sep 9 2015 - 1:41am
Last seen: 6 years 11 months ago

8 Answers:

You can use a page break and give it a print when condition like;
$V{REPORT_COUNT} % 10 == 0

Hope this helps,
KKriplani

kkriplani's picture
4728
Joined: Sep 4 2015 - 2:18am
Last seen: 9 months 3 weeks ago

At the oracle plsql level you can set rownum <= x in the where clause in your jrxml file.  

The following will return 5 itemnames from an item table when the user enters rowlimit = 5:

 <queryString>
         <![CDATA[selectitemname from items where rownum <= $P{rowlimit};]]>
</queryString>
Hope this helps, 
Chris Kennedy
 
 

 

christopher.g.kennedy's picture
Joined: Mar 18 2015 - 1:10pm
Last seen: 6 years 3 months ago

There's isn't a property to set maximize row to fetch at JasperReports level. You'll need to have different datasources for mysql and oracle.

hozawa's picture
163609
Joined: Apr 24 2010 - 4:31pm
Last seen: 3 years 8 months ago

I seem to have found a way to achieve this effect.

<filterExpression> <![CDATA[$V{REPORT_COUNT}<=$P{rowlimit}]]> </filterExpression>

$P{rowlimit} is the user defined parameter that can prompt. But not sure of the sideeffects, as in don't know if it actually fetches the top rows of select.

psundaravaradhan's picture
Joined: Sep 9 2015 - 1:41am
Last seen: 6 years 11 months ago

Hi psundaravaradhan,

You are right. That's the only way to make it on Jasper level is giving a filter expression. But If you want to use like that and the result set has too many rows, some performance issue will come with it. Because when you giving a filter expression for the top records, reports show top records after fetching all the query results.

 

 

 
 
zh3ntil's picture
3115
Joined: Nov 19 2014 - 11:02pm
Last seen: 6 years 7 months ago

So are you saying the best way to get the top X rows is to alter the SQL query ?

psundaravaradhan's picture
Joined: Sep 9 2015 - 1:41am
Last seen: 6 years 11 months ago

If your query result has too many rows, then yes.You should test two way and decide. In my experience, around 100K records the performance difference is noticeable.

zh3ntil's picture
3115
Joined: Nov 19 2014 - 11:02pm
Last seen: 6 years 7 months ago

order by clause will return the rows you want in your limitted subset query, or additional where clauses.  It may be data dependent.

christopher.g.kennedy's picture
Joined: Mar 18 2015 - 1:10pm
Last seen: 6 years 3 months ago
Feedback
randomness