Limit number of rows on Table component at report level in Jasper Studio 6.x or iReport - usage of REPORT_MAX_COUNT parameter for table

This post will guide you on below things
1) How to limit the number of rows on table component at report level.
2) How to work with REPORT_MAX_COUNT
3) How to export Excel output to two tabs(sheets)

USE CASE : 
Data set(SQL) returns N number of rows. Display top 10 rows in one sheet & All rows(including top 10 rows) in another sheet.

BENEFITS
1) Used 1 SQL query on two table components.
2) Limiting the rows to display on table components at report level ( not in SQL level).
3) Increase the performance of report (SQL query hits db only once)
4) Save time & provide value added solutions to client.

Solution Steps: 

1)Take two groups (or summary band) to keep two table components & create a dataset.
  (Use page break & ignore pagination property checked).
2) Make these two table components to use the created dataset.
3) Jump into the report XML code and write the code for first table component.
i.e., (Search for 1st table component code in XML editor)

<jr:table xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd">

<datasetRun subDataset="E2E Inventory" uuid="f7d1fb44-409b-4dee-a200-e78f49ed1bbf">

<datasetParameter name="REPORT_MAX_COUNT">
     <datasetParameterExpression><![CDATA[new Integer(10)]]>          </datasetParameterExpression>
</datasetParameter>

<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]>  </connectionExpression>
</datasetRun>

4) Save the report & preview it on server then export to Excel. (use excel related properties to export into multiple sheets with it's names).


Excel Export properties (Add below properties to report - right click report & find properties).

net.sf.jasperreports.export.xls.sheet.names.1	Top 10 Rows
net.sf.jasperreports.export.xls.sheet.names.2	All Items
net.sf.jasperreports.page.break.no.pagination	apply
net.sf.jasperreports.export.xls.ignore.cell.border	false

IMP NOTE : 
The procedure is useful when you do not have any calculated fields on table component.
Click here to find another post on limiting number of rows on table component with calculated fileds.

limit the number of rows after adding a calculated field on table component & remove all empty cells

USE CASE : 
Find a percentage field on table component and limit the rows to top 10
i.e., There is a field "A" of Double type then find the percentage field of A and then limit to top 10.
% filed = A/Total_Of_A

In this case we can't apply REPORT_MAX_COUNT parameter to table & to it's dataset.(b'z we have a calculated field of percentage .. The summation variable "Total_Of_A" adding all the values of A but not only 10 values.

Here are the solution steps. 
1) Take a variable(Total_Of_Field_A)and find the summation of column A.
2) Add a new cell(header, detail and footer cells) to the table component and take a text field in Detail cell and in it's expression write A/$V{Total_Of_Field_A}
3) The above calculation returns the calculated filed on table component.
4) Now select all the fields and go to print when expression commonly and write $V{REPORT_COUNT}<11 which will returns only top 10 rows.
5) Issue : Though you get top 10 rows you will be finding all empty cells of whole table data.
6) Remove border for all the cells. ( i.e., to the Column cells in Detail band of table ) and provide border only to text fields.

In this way one can easily write report limiting to 10 rows using table component at report level.

References :
1) http://community.jaspersoft.com/sites/all/libraries/pdf.js/web/viewer.html?file=http://community.jaspersoft.com/system/files/documentation/jasperreports-ultimate-guide-3v2.pdf#page=75

2) http://community.jaspersoft.com/wiki/jasperreports-built-parameter-reportmaxcount-and-interactive-jive-sort-feature


If you like the article click on g+ button  :-)

Cheers..!!!