nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Get dataset's REAL row count at runtime

Rep0rter

Hello, 
 
I need to know at runtime if the value from "Max number of rows to fetch from data source" decided at design time is been used, so I can warn the client/user that we are not using the whole data the real query returns. Actually I'm generating the report using that property but the user that requests the report doesn't know that we have reached the "max number of rows.." limit. 
I use a RunAndRenderTask and I've tried to get information from task.getErrors() before closing it (afther the task has finished) but reaching this limit is not considered as an error, how could I be warned about it? 
 
I'm using BIRT Runtime 4.2.2 integrated in a Java EE application. 
 
Thanks!

Find more posts tagged with

Comments

mwilliams

In your query, you could do something like: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
select customernumber,contactlastname,(select count(*) from customers) as totalrows
from customers
</pre>
 
This would give you a column with the total rows for the query (not limited) that you could use in your report. Or you could have a separate dataset to do the "count" query. Let me know.

Rep0rter

Thanks mwilliam! 
 
I knew I could do that but did not want to overload the report wirh another query. I have another big problem, I'm not the report designer and it's not easy to ask the client to modify all the existing reports. I'm now parametricing the row count limit and setting it at runtime before running the report (even if they have not set the "max number of row..." property at design time) so we can avoid some performance problems with too large reports, but we now have this issue when trying to detect this case. 
 
Any advice? Maybe could I execute the full original query without limitation and try to use the first X rows to generate the report? any idea of how to do that? 
 
Thanks! 
 
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="116645" data-time="1368197720" data-date="10 May 2013 - 07:55 AM">
In your query, you could do something like: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
select customernumber,contactlastname,(select count(*) from customers) as totalrows
from customers
</pre>
 
This would give you a column with the total rows for the query (not limited) that you could use in your report. Or you could have a separate dataset to do the "count" query. Let me know. </blockquote>

mwilliams

One thing you could do is to check the number of rows returned against the limit set. Then, you could give a warning that states that since the max number of rows allowed to be returned was reached that some data may not be visible. This would cover the only questionable number of returned rows, which would be the query returning the exact number of rows as the limit.

Rep0rter

That woukd be great! How could I get the number of rows returned at runtime? 
 
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="116714" data-time="1368474084" data-date="13 May 2013 - 12:41 PM">
One thing you could do is to check the number of rows returned against the limit set. Then, you could give a warning that states that since the max number of rows allowed to be returned was reached that some data may not be visible. This would cover the only questionable number of returned rows, which would be the query returning the exact number of rows as the limit. </blockquote>

mwilliams

Ah. I wasn't thinking about you not having access to anything with the designs. I was gonna say, there's a count aggregation that you could use to get the row count. You might look into modifying the report design in Java before you run it to add the computed column.

Rep0rter

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="116725" data-time="1368495976" data-date="13 May 2013 - 06:46 PM">
Ah. I wasn't thinking about you not having access to anything with the designs. I was gonna say, there's a count aggregation that you could use to get the row count. You might look into modifying the report design in Java before you run it to add the computed column. </blockquote>
 
After making some SQL tests I'll try to add a "rownum" column to the query and a "rownum < :myLimit" at the end of the query conditions but i'm actually having some problems to get into the datasets query at runtime. 
 
I'm actually trying to get to the DataSet this way. Any advice on how to get to the DataSet and modify it before executing the report?

<pre class='_prettyXprint _lang-auto _linenums:0'>
Module module = design.getDesignHandle().getModule();
List<DesignElement> allElements = module.getAllElements();
for (DesignElement designElement : allElements) {
DesignElementHandle handle = designElement.getHandle(module);
if (handle instanceof OdaDataSetHandle){
OdaDataSetHandle odaDataSetHandle = (OdaDataSetHandle) handle;
log.info("dataSetHandle.getDataSourceName(): " + odaDataSetHandle.getDataSourceName());
}
else if (handle instanceof DataSetHandle){
DataSetHandle dataSetHandle = (DataSetHandle) handle;
log.info("dataSetHandle.getDataSourceName(): " + dataSetHandle.getDataSourceName());
}
</pre>
 
If I get to alter the query how could I get the lasts rows rownum columns value? 
 
Thanks again!