Get row count of dataset

If no data is found based on the selected parameter values, I have to show a msg "No Data Found" on the report. How can get the Row count of the dataset.

I have created "No Data" String param and need to show this parameter only if the row count = 0, else hide.

Can I get sample code for the same.

Thanks

Find more posts tagged with

Comments

mwilliams

Hi Pujan, 
 
See if this post from the devShare helps. If not, let me know. 
 
<a class='bbc_url' href='http://www.birt-exchange.org/devshare/designing-birt-reports/522-birt-display-quot-no-data-quot-when-table-is-empty-example/#description'>BIRT Display "No Data" When Table Is Empty Example - Designs & Code - BIRT Exchange</a>

Pujan

Thanks. It worked

mwilliams

Pujan,

Good to hear.

ndesai

Pujan/ Mike, 
 
I had a similar requirement and checked the example. 
But when I add a computed column for Aggregation 'COUNT' and run on server I get the following assertion error. 
 
May 6, 2009 12:28:44 PM org.eclipse.birt.report.engine.api.impl.RunAndRenderTask doRun 
SEVERE: Error happened while running the report. 
java.lang.AssertionError 
at org.eclipse.birt.data.engine.executor.aggregation.AggregationHelper.calculate(AggregationHelper.java:140) 
at org.eclipse.birt.data.engine.executor.aggregation.AggregationHelper.populateAggregations(AggregationHelper.java:109) 
at org.eclipse.birt.data.engine.executor.aggregation.AggregationHelper.<init>(AggregationHelper.java:86) 
at org.eclipse.birt.data.engine.executor.transform.pass.DataSetProcessUtil.populateAggrCCs(DataSetProcessUtil.java:195) 
at org.eclipse.birt.data.engine.executor.transform.pass.DataSetProcessUtil.populateDataSet(DataSetProcessUtil.java:108) 
at org.eclipse.birt.data.engine.executor.transform.pass.DataSetProcessUtil.doPopulate(DataSetProcessUtil.java:84) 
at org.eclipse.birt.data.engine.executor.transform.pass.PassManager.doPopulation(PassManager.java:277) 
at org.eclipse.birt.data.engine.executor.transform.pass.PassManager.doMultiPass(PassManager.java:227) 
at org.eclipse.birt.data.engine.executor.transform.pass.PassManager.pass(PassManager.java:93) 
at org.eclipse.birt.data.engine.executor.transform.pass.PassManager.populateResultSet(PassManager.java:70) 
at org.eclipse.birt.data.engine.executor.transform.ResultSetPopulator.populateResultSet(ResultSetPopulator.java:196) 
at org.eclipse.birt.data.engine.executor.transform.CachedResultSet.<init>(CachedResultSet.java:86) 
at org.eclipse.birt.data.engine.executor.DataSourceQuery.execute(DataSourceQuery.java:737) 
at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.executeOdiQuery(PreparedOdaDSQuery.java:331) 
at org.eclipse.birt.data.engine.impl.QueryExecutor.execute(QueryExecutor.java:904) 
at org.eclipse.birt.data.engine.impl.ServiceForQueryResults.executeQuery(ServiceForQueryResults.java:233) 
at org.eclipse.birt.data.engine.impl.QueryResults.getResultIterator(QueryResults.java:161) 
at org.eclipse.birt.report.engine.data.dte.QueryResultSet.<init>(QueryResultSet.java:92) 
at org.eclipse.birt.report.engine.data.dte.DteDataEngine.doExecuteQuery(DteDataEngine.java:128) 
at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.execute(AbstractDataEngine.java:248) 
at org.eclipse.birt.report.engine.executor.ExecutionContext.executeQuery(ExecutionContext.java:1736) 
at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:77) 
at org.eclipse.birt.report.engine.executor.TableItemExecutor.execute(TableItemExecutor.java:62) 
at org.eclipse.birt.report.engine.internal.executor.dup.SuppressDuplicateItemExecutor.execute(SuppressDuplicateItemExecutor.java:42) 
at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.execute(WrappedReportItemExecutor.java:45) 
at org.eclipse.birt.report.engine.internal.executor.l18n.LocalizedReportItemExecutor.execute(LocalizedReportItemExecutor.java:33) 
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:63) 
at org.eclipse.birt.report.engine.layout.html.HTMLStackingLM.layoutChildren(HTMLStackingLM.java:27) 
at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:133) 
at org.eclipse.birt.report.engine.layout.html.HTMLInlineStackingLM.resumeLayout(HTMLInlineStackingLM.java:104) 
at org.eclipse.birt.report.engine.layout.html.HTMLInlineStackingLM.layoutNodes(HTMLInlineStackingLM.java:153) 
at org.eclipse.birt.report.engine.layout.html.HTMLStackingLM.layoutChildren(HTMLStackingLM.java:27) 
at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:133) 
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:68) 
at org.eclipse.birt.report.engine.layout.html.HTMLStackingLM.layoutChildren(HTMLStackingLM.java:27) 
at org.eclipse.birt.report.engine.layout.html.HTMLTableLM.layoutChildren(HTMLTableLM.java:76) 
at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:133) 
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:68) 
at org.eclipse.birt.report.engine.layout.html.HTMLPageLM.layout(HTMLPageLM.java:90) 
at org.eclipse.birt.report.engine.layout.html.HTMLReportLayoutEngine.layout(HTMLReportLayoutEngine.java:101) 
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.doRun(RunAndRenderTask.java:151) 
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.run(RunAndRenderTask.java:72) 
at com.jpmorgan.tara.action.RiskReportAction.getHTMLReport(RiskReportAction.java:318) 
at com.jpmorgan.tara.action.RiskReportAction.execute(RiskReportAction.java:107) 
at org.apache.struts.chain.commands.servlet.ExecuteAction.execute(ExecuteAction.java:58) 
at org.apache.struts.chain.commands.AbstractExecuteAction.execute(AbstractExecuteAction.java:67) 
at org.apache.struts.chain.commands.ActionCommandBase.execute(ActionCommandBase.java:51) 
at org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:190) 
at org.apache.commons.chain.generic.LookupCommand.execute(LookupCommand.java:304) 
at org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:190) 
at org.apache.struts.chain.ComposableRequestProcessor.process(ComposableRequestProcessor.java:283) 
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913) 
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:462) 
at javax.servlet.http.HttpServlet.service(HttpServlet.java:763) 
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856) 
at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:223) 
at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125) 
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:283) 
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:175) 
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3245) 
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321) 
at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:121) 
at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2003) 
at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:1909) 
at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1359) 
at weblogic.work.ExecuteThread.execute(ExecuteThread.java:209) 
at weblogic.work.ExecuteThread.run(ExecuteThread.java:181) 
 
Is there a way for me to debug this code as there are no log statements in the code. Currently the way my code/report design is, I cannot upload it here for your reference. 
 
Please let me know if you can suggest something by looking at the stack trace. 
 
Thanks, 
Nikhil

mwilliams

Hi Nikhil,

Are you getting this error in the designer or when deployed or both? Can you create a sample report with the classic models sample database where this breaks? If you attach an example, please specify your BIRT version. Thanks.

ndesai

Hi Mike,

Thanks for the reply.

I get this error only when it is deployed on the server and not in the designer.
After further investigations this is my finding.

I get this error only when all of the following conditions are met
- the report is deployed on server
- the table has a computed column for 'COUNT'
- the result set has no records.

when the result set has 1 or more records, it works fine.

I tried to create a sample report with the classic models sample database, but somehow I am struggling with the driver. But I hope the above information should be enough for you to reproduce the issue. I am on BIRT 2.3.1

I will try to upload the sample report soon...

Thanks
Nikhil

ndesai

Hi Mike ,

Any updates on this. Were you able to reproduce the issue ?

Thanks,
Nikhil

mwilliams

Nikhil,

The example I created in the devShare has a computed column that uses the aggreagation 'COUNT'. When I deploy that report, it works as expected. If the result set is empty, it tells me the Query returned no data. I used 2.3.0 though. I don't have the viewer deployed for 2.3.1. If you can't find a way to reproduce the error with the sample database, I'll download the 2.3.1 runtime and try that. If you can reproduce the error, that'd be great though.

Let me know.

ndesai

I am sorry Mike but I guess you will have to download 2.3.1 runtime. 
 
I have been able to reproduce the error. So heres the deal - 
1) In 2.3.1 runtime the computed column works if the data set has results but if it returns 0 results it breaks with the above mentioned exception. 
2) In the attached rptdesign file, I have created a data set with a query having a where clause where 1!=1. This clause will return no records and the code breaks. if you comment this clause it works perfectly fine. 
3) Here are the env details - 
Birt 2.3.1 
Weblogic 9.2 
Struts 
Classic Model Sample DB 
4) I have also attached my sample code for struts and the jsp. 
 
Please let me know if you need any further details. 
 
Thanks, 
Nikhil

mwilliams

Nikhil,

I don't have weblogic or struts, so I don't know if I can use your entire example. However, I deployed the report design to Tomcat using the 2.3.1 runtime and got no error with the 'where 1!=1' clause still in the query.

ndesai

Thanks Mike,

I'll try to deploy on some other appserver and see if its a problem only with Weblogic.

Cheers,
Nikhil

ndesai

Hi Mike,

This seems to be a definite bug. Is there a way for me to register it as a bug somewhere so that someone can reproduce it and fix it.

Also is there a (alternative) way to hide the table when the dataset count is zero, without using computed column for 'COUNT'.

Kindly help as there seems to be no way out for me.

Thanks,
Nikhil

mwilliams

Nikhil, 
 
You can report bugs here: 
 
<a class='bbc_url' href='http://www.eclipse.org/birt/phoenix/reportabug.php'>BIRT: Reporting Bugs and Requesting Enhancements</a> 
 
I'll work on an example of another way I think should work and attach it if it does.

mwilliams

Nikhil,

Here's another example that works. It uses a second dataSet to count the rows in the query. That dataSet must be bound to the report by the data element at the top of the report to run the script that sets the variable inside the dataSet. It is hidden however, so it won't appear in the report. Check and see if this approach works for you.

ndesai

Thanks Mike, 
 
I got it resolved using row.__rownum != -1 to determine if the result set size is zero. 
 
Thanks a lot for all your help. 
 
Cheers, 
Nikhil

mwilliams

Nikhil,

Excellent! Glad you got something to work for you!

Let us know whenever you have questions.