Limit number of rows to be fetched in query
Posted on February 8, 2016 at 4:17am
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.
Joined: Sep 9 2015 - 1:41am
Last seen: 4 years 12 months ago
Posted on February 8, 2016 at 6:07am
You can use a page break and give it a print when condition like;
$V{REPORT_COUNT} % 10 == 0
Hope this helps,
KKriplani
Joined: Sep 4 2015 - 2:18am
Last seen: 1 year 1 month ago
Posted on February 8, 2016 at 8:27am
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
Joined: Mar 18 2015 - 1:10pm
Last seen: 4 years 4 months ago
Posted on February 8, 2016 at 1:11pm
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.
Joined: Apr 24 2010 - 4:31pm
Last seen: 1 year 9 months ago
Posted on February 8, 2016 at 5:55pm
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.
Joined: Sep 9 2015 - 1:41am
Last seen: 4 years 12 months ago
Posted on February 8, 2016 at 10:14pm
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.
Joined: Nov 19 2014 - 11:02pm
Last seen: 4 years 7 months ago
Posted on February 8, 2016 at 11:10pm
So are you saying the best way to get the top X rows is to alter the SQL query ?
Joined: Sep 9 2015 - 1:41am
Last seen: 4 years 12 months ago
Posted on February 9, 2016 at 1:13pm
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.
Joined: Nov 19 2014 - 11:02pm
Last seen: 4 years 7 months ago
Posted on February 17, 2016 at 12:40pm
order by clause will return the rows you want in your limitted subset query, or additional where clauses. It may be data dependent.
Joined: Mar 18 2015 - 1:10pm
Last seen: 4 years 4 months ago
