Limit number of rows to be fetched in query

0

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: 3 years 4 months ago

8 Answers:

0

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
837
Joined: Sep 4 2015 - 2:18am
Last seen: 12 months 4 days ago
0

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: 2 years 8 months ago
0

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
44219
Joined: Apr 24 2010 - 4:31pm
Last seen: 1 month 6 days ago
0

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: 3 years 4 months ago
0

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
531
Joined: Nov 19 2014 - 11:02pm
Last seen: 3 years 4 days ago
0

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: 3 years 4 months ago
0

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
531
Joined: Nov 19 2014 - 11:02pm
Last seen: 3 years 4 days ago
0

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: 2 years 8 months ago
Feedback
randomness