Limit number of rows on table component - Report Level

[sadakar]

Hi Community,

How can we limit the number of rows on table component report level ? I've tried $V{REPORT_COUNT}<11 on each text filed and field evaluation time set to Report. I can have the 10 rows but I am getting the total report with empty table cells.

Did any one tried this ever before ? Please provide your inputs.

Thank you in Advance for your time & attention on this thread.

Kind Regards,

Sadakar Pochampalli 

 

[peter.w]

Hi

Have you tried making the report query use a limit?

If you know how many records you want at a time.
In the actual export report take two parameters START_AT and LIMIT_TOO so that
 

 SELECT * FROM  transaction LIMIT 0 , 500 [/code]

This gives the first 500 rows becomes something like this in jasper
 

SELECT * FROM  transaction LIMIT $P{START_AT} , $P{LIMIT_TOO}[/code]

You would have to call the report more than once with the start and row count each time.
 

 

[sadakar]

Hi Peter, 

Thank you for your inputs on the thread. Let me clear you my scenario. I need to export reort to two sheets of excel. 

I have to show top 10 records in one sheet and all the records in another sheet. So I will be using SQL query only once & taking two table components. 

On the 1st table component I have to limit the number of rows to 10 & on the 2nd table component I will have to show all the records. 

I hope, I am clear with the description of issue.  

Tried below :

CASE -1 : I can take 2 datasets & I can write the SQL in each dataset and can limit to 10 rows in one of the datasets and populate data on the table components. 

This is not a best solution as I'm taking the same sql query twice and putting burden on report load. 

CASE- II:

I've tried with REPORT_COUNT variable less than to 11 on each of the text field of one of the table components and the evaluation of text field is set to Report. 

With this I can have 10 rows but the remaning rows also coming with empty cells. For example If my report having 3 pages of information I am getting all the 3 pages but data is displayed for 10 rows. This should be avoided. 

Is there any other work arounds to solve this ?

Thank you in advance :-) 

 

Kind Regards,

Sadakar Pochampalli

 

 

 

[peter.w]

Not sure if this will work re CASE - II but have you tried setting isRemoveLineWhenBlank="true" in the reportElement?

<reportElement isRemoveLineWhenBlank="true"</reportElement>[/code]

You will probably have to do this for all textFields in the first table for it to work.

[sadakar]

Here we go..!!!

Peter, thank you for your attention on this & providing your insights. 

Solution : 

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

 

1) I take two groups (or summary band) to keep two table components & created a dataset. (Used page break & ignore pagination property checked).

2) Made these two table components to use the created dataset.

3) Jumped into the report XML code and written 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. (used excel related properties to export into multiple sheets with it's names). 

 

Benefits of this thread 

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. 

 

:-) :-)

 

Putting this solution with more detailed explanation on Jasper Community Planet via my blog site shortly. 

 

[sadakar]

I'm back here again to extend this further

I've a calculated field which will display percentage field on table component. 
% field = A/B where B is the summation of all row values of A

After calculating I have to limit to top 10 rows, Is this possible ?

I have tried without adding REPORT_MAX_COUNT code and can have percentage field as expected but when I take the code it is limiting to 10 rows but the percentage is garbage.

On limitation, I am not getting accurate column summation for all values.

Thank you :-)

[sadakar]

Found the solution .. Here it is documented http://community.jaspersoft.com/blog/limit-number-rows-table-component-report-level-jasper-studio-6x-or-ireport-usage-reportmaxcount