Limit number of rows to be fetched in query

[psundaravaradhan]

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.

[kkriplani]

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

Hope this helps,
KKriplani

[christopher.g.kennedy]

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

 

 

 

[hozawa]

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.

[psundaravaradhan]

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

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

$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.

[zh3ntil]

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.

 

 

 

 

[psundaravaradhan]

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

[zh3ntil]

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.

[christopher.g.kennedy]

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