[peraka]

 DataCube.docx (53.57K)
Number of downloads: 12Hi All,

Need help in writing an expression in "Summary Field" of a DataCube i.e. labordataset::laborhours(SUM)

My requirement is to exclude some values from the SUM i.e. if labortranstype in (A,B,C,D) then total should not consider the values against these A,B,C,D, it should sum up values other than above mentioned labortranstypes.

I have attached the screenshot. Please help me out.

Thanks,
Suresh.

[mwilliams]

If you can't do a computed column or create a new field in your scripted dataSet, if using that to do this, you could put an expression for the measure field, like:

temp = "A,B,C,D"
if (temp.search(dataSetRow["labortranstype"]) != -1){
0;
}
else{
dataSetRow["hours"]
}

[peraka]

mwilliams, on 27 January 2012 - 10:14 AM, said:

If you can't do a computed column or create a new field in your scripted dataSet, if using that to do this, you could put an expression for the measure field, like:

temp = "A,B,C,D"
if (temp.search(dataSetRow["labortranstype"]) != -1){
0;
}
else{
dataSetRow["hours"]
}

thank you williams, let me try this and get back to you, in case any questions.
Do I have to declare temp as a variable ? I will use the above exactly as given.

thanks,
suresh.

[mwilliams]

It will work as is or you can put var temp. It shouldn't really matter.

[peraka]

mwilliams, on 27 January 2012 - 10:47 AM, said:

It will work as is or you can put var temp. It shouldn't really matter.

Ok, thank you so much. Let me try this and get back to you. Thanks once again.

Suresh.

[peraka]

peraka, on 27 January 2012 - 10:48 AM, said:

Ok, thank you so much. Let me try this and get back to you. Thanks once again.

Suresh.

Hi Williams,

I had added the given code in the expression for a summary field of laborhours, but still it includes the laborhours of A,B,C,D in the total of laborhours.

Labortranstype is a dimension.

Where as it the total should not include the laborhours of A,B,C,D labortranstype in the sum.

Labortranstype is a dimension.

Please help.

Suresh.

[peraka]

peraka, on 29 January 2012 - 02:32 PM, said:

Hi Williams,

I had added the given code in the expression for a summary field of laborhours, but still it includes the laborhours of A,B,C,D in the total of laborhours.

Where as it the total should not include the laborhours of A,B,C,D labortranstype in the sum.

Labortranstype is a dimension.

Error Stack below.

The following items have errors:


ReportDesign (id = 1):
- An exception occurred during processing. Please see the following message for details:
exception.error
Column binding "laborDataSet::laborhours" has referred to a data set column "labortranstype" which does not exist.
Column binding "laborDataSet::laborhours" has referred to a data set column "labortranstype" which does not exist.
exception.error ( 1 time(s) )
detail : org.eclipse.birt.report.engine.api.EngineException: An exception occurred during processing. Please see the following message for details:exception.errorColumn binding "laborDataSet::laborhours" has referred to a data set column "labortranstype" which does not exist.Column binding "laborDataSet::laborhours" has referred to a data set column "labortranstype" which does not exist. at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1121) at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.doPrepareQuery(AbstractDataEngine.java:215) at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.prepare(AbstractDataEngine.java:172) at org.eclipse.birt.report.engine.executor.ReportExecutor.execute(ReportExecutor.java:118) at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportExecutor.execute(WrappedReportExecutor.java:59) at org.eclipse.birt.report.engine.internal.executor.dup.SuppressDuplciateReportExecutor.execute(SuppressDuplciateReportExecutor.java:51) at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportExecutor.execute(WrappedReportExecutor.java:59) at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.doRun(RunAndRenderTask.java:149) at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.run(RunAndRenderTask.java:72) at org.eclipse.birt.report.service.ReportEngineService.runAndRenderReport(ReportEngineService.java:877) at org.eclipse.birt.report.service.BirtViewerReportService.runAndRenderReport(BirtViewerReportService.java:938) at org.eclipse.birt.report.service.actionhandler.BirtGetPageAllActionHandler.__execute(BirtGetPageAllActionHandler.java:131) at org.eclipse.birt.report.service.actionhandler.AbstractBaseActionHandler.execute(AbstractBaseActionHandler.java:90) at org.eclipse.birt.report.soapengine.processor.AbstractBaseDocumentProcessor.__executeAction(AbstractBaseDocumentProcessor.java:47) at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.executeAction(AbstractBaseComponentProcessor.java:143) at org.eclipse.birt.report.soapengine.processor.BirtDocumentProcessor.handleGetPageAll(BirtDocumentProcessor.java:183) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.process(AbstractBaseComponentProcessor.java:112) at org.eclipse.birt.report.soapengine.endpoint.BirtSoapBindingImpl.getUpdatedObjects(BirtSoapBindingImpl.java:66) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397) at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:186) at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:323) at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:32) at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:118) at org.apache.axis.SimpleChain.invoke(SimpleChain.java:83) at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:454) at org.apache.axis.server.AxisServer.invoke(AxisServer.java:281) at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:699) at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.doPost(BirtSoapMessageDispatcherServlet.java:265) at javax.servlet.http.HttpServlet.service(HttpServlet.java:616) at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327) at javax.servlet.http.HttpServlet.service(HttpServlet.java:689) at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.service(BirtSoapMessageDispatcherServlet.java:122) at org.eclipse.equinox.http.registry.internal.ServletManager$ServletWrapper.service(ServletManager.java:180) at org.eclipse.equinox.http.servlet.internal.ServletRegistration.handleRequest(ServletRegistration.java:90) at org.eclipse.equinox.http.servlet.internal.ProxyServlet.processAlias(ProxyServlet.java:111) at org.eclipse.equinox.http.servlet.internal.ProxyServlet.service(ProxyServlet.java:59) at javax.servlet.http.HttpServlet.service(HttpServlet.java:689) at org.eclipse.equinox.http.jetty.internal.HttpServerManager$InternalHttpServiceServlet.service(HttpServerManager.java:269) at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:428) at org.mortbay.jetty.servlet.ServletHandler.dispatch(ServletHandler.java:677) at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:568) at org.mortbay.http.HttpContext.handle(HttpContext.java:1530) at org.mortbay.http.HttpContext.handle(HttpContext.java:1482) at org.mortbay.http.HttpServer.service(HttpServer.java:909) at org.mortbay.http.HttpConnection.service(HttpConnection.java:820) at org.mortbay.http.HttpConnection.handleNext(HttpConnection.java:986) at org.mortbay.http.HttpConnection.handle(HttpConnection.java:837) at org.mortbay.http.SocketListener.handleConnection(SocketListener.java:245) at org.mortbay.util.ThreadedServer.handle(ThreadedServer.java:357) at org.mortbay.util.ThreadPool$PoolThread.run(ThreadPool.java:534)Caused by: org.eclipse.birt.report.data.adapter.api.AdapterException: An exception occurred during processing. Please see the following message for details:exception.errorColumn binding "laborDataSet::laborhours" has referred to a data set column "labortranstype" which does not exist.Column binding "laborDataSet::laborhours" has referred to a data set column "labortranstype" which does not exist. at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.prepare(DataRequestSessionImpl.java:526) at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.doPrepareQuery(AbstractDataEngine.java:208) ... 56 moreCaused by: org.eclipse.birt.data.engine.core.DataException: exception.errorColumn binding "laborDataSet::laborhours" has referred to a data set column "labortranstype" which does not exist.Column binding "laborDataSet::laborhours" has referred to a data set column "labortranstype" which does not exist. at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.materializeCube(DataRequestSessionImpl.java:603) at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.prepare(DataRequestSessionImpl.java:1062) at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.prepare(DataRequestSessionImpl.java:512) ... 57 moreCaused by: org.eclipse.birt.report.data.adapter.api.AdapterException: Column binding "laborDataSet::laborhours" has referred to a data set column "labortranstype" which does not exist.Column binding "laborDataSet::laborhours" has referred to a data set column "labortranstype" which does not exist. at org.eclipse.birt.report.data.adapter.impl.DataSetIterator.executeQuery(DataSetIterator.java:87) at org.eclipse.birt.report.data.adapter.impl.DataSetIterator.<init>(DataSetIterator.java:101) at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.createCube(DataRequestSessionImpl.java:742) at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.materializeCube(DataRequestSessionImpl.java:586) ... 59 moreCaused by: org.eclipse.birt.data.engine.core.DataException: Column binding "laborDataSet::laborhours" has referred to a data set column "labortranstype" which does not exist. at org.eclipse.birt.data.engine.impl.ResultIterator.validateManualBindingExpressions(ResultIterator.java:311) at org.eclipse.birt.data.engine.impl.ResultIterator.<init>(ResultIterator.java:148) at org.eclipse.birt.data.engine.impl.ResultIterator2.<init>(ResultIterator2.java:47) at org.eclipse.birt.data.engine.impl.QueryResults.getResultIterator(QueryResults.java:183) at org.eclipse.birt.report.data.adapter.impl.DataSetIterator.executeQuery(DataSetIterator.java:83) ... 62 more
- Can't find the prepared query org.eclipse.birt.data.engine.olap.impl.query.CubeQueryDefinition@e85c92.
Error.PreparedQueryNotFoundError ( 1 time(s) )
detail : org.eclipse.birt.report.engine.api.EngineException: Can't find the prepared query org.eclipse.birt.data.engine.olap.impl.query.CubeQueryDefinition@e85c92. at org.eclipse.birt.report.engine.executor.ExecutorManager$ExecutorContext.executeQuery(ExecutorManager.java:432) at org.eclipse.birt.report.item.crosstab.core.re.executor.BaseCrosstabExecutor.executeQuery(BaseCrosstabExecutor.java:113) at org.eclipse.birt.report.item.crosstab.core.re.executor.CrosstabReportItemExecutor.execute(CrosstabReportItemExecutor.java:102) at org.eclipse.birt.report.engine.executor.ExtendedItemExecutor.execute(ExtendedItemExecutor.java:61) 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 org.eclipse.birt.report.service.ReportEngineService.runAndRenderReport(ReportEngineService.java:877) at org.eclipse.birt.report.service.BirtViewerReportService.runAndRenderReport(BirtViewerReportService.java:938) at org.eclipse.birt.report.service.actionhandler.BirtGetPageAllActionHandler.__execute(BirtGetPageAllActionHandler.java:131) at org.eclipse.birt.report.service.actionhandler.AbstractBaseActionHandler.execute(AbstractBaseActionHandler.java:90) at org.eclipse.birt.report.soapengine.processor.AbstractBaseDocumentProcessor.__executeAction(AbstractBaseDocumentProcessor.java:47) at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.executeAction(AbstractBaseComponentProcessor.java:143) at org.eclipse.birt.report.soapengine.processor.BirtDocumentProcessor.handleGetPageAll(BirtDocumentProcessor.java:183) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.process(AbstractBaseComponentProcessor.java:112) at org.eclipse.birt.report.soapengine.endpoint.BirtSoapBindingImpl.getUpdatedObjects(BirtSoapBindingImpl.java:66) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397) at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:186) at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:323) at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:32) at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:118) at org.apache.axis.SimpleChain.invoke(SimpleChain.java:83) at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:454) at org.apache.axis.server.AxisServer.invoke(AxisServer.java:281) at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:699) at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.doPost(BirtSoapMessageDispatcherServlet.java:265) at javax.servlet.http.HttpServlet.service(HttpServlet.java:616) at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327) at javax.servlet.http.HttpServlet.service(HttpServlet.java:689) at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.service(BirtSoapMessageDispatcherServlet.java:122) at org.eclipse.equinox.http.registry.internal.ServletManager$ServletWrapper.service(ServletManager.java:180) at org.eclipse.equinox.http.servlet.internal.ServletRegistration.handleRequest(ServletRegistration.java:90) at org.eclipse.equinox.http.servlet.internal.ProxyServlet.processAlias(ProxyServlet.java:111) at org.eclipse.equinox.http.servlet.internal.ProxyServlet.service(ProxyServlet.java:59) at javax.servlet.http.HttpServlet.service(HttpServlet.java:689) at org.eclipse.equinox.http.jetty.internal.HttpServerManager$InternalHttpServiceServlet.service(HttpServerManager.java:269) at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:428) at org.mortbay.jetty.servlet.ServletHandler.dispatch(ServletHandler.java:677) at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:568) at org.mortbay.http.HttpContext.handle(HttpContext.java:1530) at org.mortbay.http.HttpContext.handle(HttpContext.java:1482) at org.mortbay.http.HttpServer.service(HttpServer.java:909) at org.mortbay.http.HttpConnection.service(HttpConnection.java:820) at org.mortbay.http.HttpConnection.handleNext(HttpConnection.java:986) at org.mortbay.http.HttpConnection.handle(HttpConnection.java:837) at org.mortbay.http.SocketListener.handleConnection(SocketListener.java:245) at org.mortbay.util.ThreadedServer.handle(ThreadedServer.java:357) at org.mortbay.util.ThreadPool$PoolThread.run(ThreadPool.java:534)Caused by: org.eclipse.birt.report.engine.api.EngineException: Can't find the prepared query org.eclipse.birt.data.engine.olap.impl.query.CubeQueryDefinition@e85c92. at org.eclipse.birt.report.engine.data.dte.DteDataEngine.doExecuteCube(DteDataEngine.java:162) at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.execute(AbstractDataEngine.java:258) at org.eclipse.birt.report.engine.executor.ExecutorManager$ExecutorContext.executeQuery(ExecutorManager.java:422) ... 71 more

Attached the Screenshot of labortranstype in the dataset laborDataSet.

Please help.

Suresh.

[peraka]

peraka, on 29 January 2012 - 02:32 PM, said:

Hi Williams,

I had added the given code in the expression for a summary field of laborhours, but still it includes the laborhours of A,B,C,D in the total of laborhours.

Where as it the total should not include the laborhours of A,B,C,D labortranstype in the sum.

Labortranstype is a dimension.

Error Stack below.

The following items have errors:


ReportDesign (id = 1):
- An exception occurred during processing. Please see the following message for details:
exception.error
Column binding "laborDataSet::laborhours" has referred to a data set column "labortranstype" which does not exist.
Column binding "laborDataSet::laborhours" has referred to a data set column "labortranstype" which does not exist.
exception.error ( 1 time(s) )
detail : org.eclipse.birt.report.engine.api.EngineException: An exception occurred during processing. Please see the following message for details:exception.errorColumn binding "laborDataSet::laborhours" has referred to a data set column "labortranstype" which does not exist.Column binding "laborDataSet::laborhours" has referred to a data set column "labortranstype" which does not exist. at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1121) at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.doPrepareQuery(AbstractDataEngine.java:215) at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.prepare(AbstractDataEngine.java:172) at org.eclipse.birt.report.engine.executor.ReportExecutor.execute(ReportExecutor.java:118) at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportExecutor.execute(WrappedReportExecutor.java:59) at org.eclipse.birt.report.engine.internal.executor.dup.SuppressDuplciateReportExecutor.execute(SuppressDuplciateReportExecutor.java:51) at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportExecutor.execute(WrappedReportExecutor.java:59) at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.doRun(RunAndRenderTask.java:149) at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.run(RunAndRenderTask.java:72) at org.eclipse.birt.report.service.ReportEngineService.runAndRenderReport(ReportEngineService.java:877) at org.eclipse.birt.report.service.BirtViewerReportService.runAndRenderReport(BirtViewerReportService.java:938) at org.eclipse.birt.report.service.actionhandler.BirtGetPageAllActionHandler.__execute(BirtGetPageAllActionHandler.java:131) at org.eclipse.birt.report.service.actionhandler.AbstractBaseActionHandler.execute(AbstractBaseActionHandler.java:90) at org.eclipse.birt.report.soapengine.processor.AbstractBaseDocumentProcessor.__executeAction(AbstractBaseDocumentProcessor.java:47) at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.executeAction(AbstractBaseComponentProcessor.java:143) at org.eclipse.birt.report.soapengine.processor.BirtDocumentProcessor.handleGetPageAll(BirtDocumentProcessor.java:183) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.process(AbstractBaseComponentProcessor.java:112) at org.eclipse.birt.report.soapengine.endpoint.BirtSoapBindingImpl.getUpdatedObjects(BirtSoapBindingImpl.java:66) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397) at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:186) at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:323) at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:32) at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:118) at org.apache.axis.SimpleChain.invoke(SimpleChain.java:83) at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:454) at org.apache.axis.server.AxisServer.invoke(AxisServer.java:281) at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:699) at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.doPost(BirtSoapMessageDispatcherServlet.java:265) at javax.servlet.http.HttpServlet.service(HttpServlet.java:616) at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327) at javax.servlet.http.HttpServlet.service(HttpServlet.java:689) at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.service(BirtSoapMessageDispatcherServlet.java:122) at org.eclipse.equinox.http.registry.internal.ServletManager$ServletWrapper.service(ServletManager.java:180) at org.eclipse.equinox.http.servlet.internal.ServletRegistration.handleRequest(ServletRegistration.java:90) at org.eclipse.equinox.http.servlet.internal.ProxyServlet.processAlias(ProxyServlet.java:111) at org.eclipse.equinox.http.servlet.internal.ProxyServlet.service(ProxyServlet.java:59) at javax.servlet.http.HttpServlet.service(HttpServlet.java:689) at org.eclipse.equinox.http.jetty.internal.HttpServerManager$InternalHttpServiceServlet.service(HttpServerManager.java:269) at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:428) at org.mortbay.jetty.servlet.ServletHandler.dispatch(ServletHandler.java:677) at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:568) at org.mortbay.http.HttpContext.handle(HttpContext.java:1530) at org.mortbay.http.HttpContext.handle(HttpContext.java:1482) at org.mortbay.http.HttpServer.service(HttpServer.java:909) at org.mortbay.http.HttpConnection.service(HttpConnection.java:820) at org.mortbay.http.HttpConnection.handleNext(HttpConnection.java:986) at org.mortbay.http.HttpConnection.handle(HttpConnection.java:837) at org.mortbay.http.SocketListener.handleConnection(SocketListener.java:245) at org.mortbay.util.ThreadedServer.handle(ThreadedServer.java:357) at org.mortbay.util.ThreadPool$PoolThread.run(ThreadPool.java:534)Caused by: org.eclipse.birt.report.data.adapter.api.AdapterException: An exception occurred during processing. Please see the following message for details:exception.errorColumn binding "laborDataSet::laborhours" has referred to a data set column "labortranstype" which does not exist.Column binding "laborDataSet::laborhours" has referred to a data set column "labortranstype" which does not exist. at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.prepare(DataRequestSessionImpl.java:526) at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.doPrepareQuery(AbstractDataEngine.java:208) ... 56 moreCaused by: org.eclipse.birt.data.engine.core.DataException: exception.errorColumn binding "laborDataSet::laborhours" has referred to a data set column "labortranstype" which does not exist.Column binding "laborDataSet::laborhours" has referred to a data set column "labortranstype" which does not exist. at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.materializeCube(DataRequestSessionImpl.java:603) at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.prepare(DataRequestSessionImpl.java:1062) at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.prepare(DataRequestSessionImpl.java:512) ... 57 moreCaused by: org.eclipse.birt.report.data.adapter.api.AdapterException: Column binding "laborDataSet::laborhours" has referred to a data set column "labortranstype" which does not exist.Column binding "laborDataSet::laborhours" has referred to a data set column "labortranstype" which does not exist. at org.eclipse.birt.report.data.adapter.impl.DataSetIterator.executeQuery(DataSetIterator.java:87) at org.eclipse.birt.report.data.adapter.impl.DataSetIterator.<init>(DataSetIterator.java:101) at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.createCube(DataRequestSessionImpl.java:742) at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.materializeCube(DataRequestSessionImpl.java:586) ... 59 moreCaused by: org.eclipse.birt.data.engine.core.DataException: Column binding "laborDataSet::laborhours" has referred to a data set column "labortranstype" which does not exist. at org.eclipse.birt.data.engine.impl.ResultIterator.validateManualBindingExpressions(ResultIterator.java:311) at org.eclipse.birt.data.engine.impl.ResultIterator.<init>(ResultIterator.java:148) at org.eclipse.birt.data.engine.impl.ResultIterator2.<init>(ResultIterator2.java:47) at org.eclipse.birt.data.engine.impl.QueryResults.getResultIterator(QueryResults.java:183) at org.eclipse.birt.report.data.adapter.impl.DataSetIterator.executeQuery(DataSetIterator.java:83) ... 62 more
- Can't find the prepared query org.eclipse.birt.data.engine.olap.impl.query.CubeQueryDefinition@e85c92.
Error.PreparedQueryNotFoundError ( 1 time(s) )
detail : org.eclipse.birt.report.engine.api.EngineException: Can't find the prepared query org.eclipse.birt.data.engine.olap.impl.query.CubeQueryDefinition@e85c92. at org.eclipse.birt.report.engine.executor.ExecutorManager$ExecutorContext.executeQuery(ExecutorManager.java:432) at org.eclipse.birt.report.item.crosstab.core.re.executor.BaseCrosstabExecutor.executeQuery(BaseCrosstabExecutor.java:113) at org.eclipse.birt.report.item.crosstab.core.re.executor.CrosstabReportItemExecutor.execute(CrosstabReportItemExecutor.java:102) at org.eclipse.birt.report.engine.executor.ExtendedItemExecutor.execute(ExtendedItemExecutor.java:61) 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 org.eclipse.birt.report.service.ReportEngineService.runAndRenderReport(ReportEngineService.java:877) at org.eclipse.birt.report.service.BirtViewerReportService.runAndRenderReport(BirtViewerReportService.java:938) at org.eclipse.birt.report.service.actionhandler.BirtGetPageAllActionHandler.__execute(BirtGetPageAllActionHandler.java:131) at org.eclipse.birt.report.service.actionhandler.AbstractBaseActionHandler.execute(AbstractBaseActionHandler.java:90) at org.eclipse.birt.report.soapengine.processor.AbstractBaseDocumentProcessor.__executeAction(AbstractBaseDocumentProcessor.java:47) at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.executeAction(AbstractBaseComponentProcessor.java:143) at org.eclipse.birt.report.soapengine.processor.BirtDocumentProcessor.handleGetPageAll(BirtDocumentProcessor.java:183) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.process(AbstractBaseComponentProcessor.java:112) at org.eclipse.birt.report.soapengine.endpoint.BirtSoapBindingImpl.getUpdatedObjects(BirtSoapBindingImpl.java:66) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397) at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:186) at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:323) at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:32) at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:118) at org.apache.axis.SimpleChain.invoke(SimpleChain.java:83) at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:454) at org.apache.axis.server.AxisServer.invoke(AxisServer.java:281) at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:699) at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.doPost(BirtSoapMessageDispatcherServlet.java:265) at javax.servlet.http.HttpServlet.service(HttpServlet.java:616) at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327) at javax.servlet.http.HttpServlet.service(HttpServlet.java:689) at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.service(BirtSoapMessageDispatcherServlet.java:122) at org.eclipse.equinox.http.registry.internal.ServletManager$ServletWrapper.service(ServletManager.java:180) at org.eclipse.equinox.http.servlet.internal.ServletRegistration.handleRequest(ServletRegistration.java:90) at org.eclipse.equinox.http.servlet.internal.ProxyServlet.processAlias(ProxyServlet.java:111) at org.eclipse.equinox.http.servlet.internal.ProxyServlet.service(ProxyServlet.java:59) at javax.servlet.http.HttpServlet.service(HttpServlet.java:689) at org.eclipse.equinox.http.jetty.internal.HttpServerManager$InternalHttpServiceServlet.service(HttpServerManager.java:269) at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:428) at org.mortbay.jetty.servlet.ServletHandler.dispatch(ServletHandler.java:677) at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:568) at org.mortbay.http.HttpContext.handle(HttpContext.java:1530) at org.mortbay.http.HttpContext.handle(HttpContext.java:1482) at org.mortbay.http.HttpServer.service(HttpServer.java:909) at org.mortbay.http.HttpConnection.service(HttpConnection.java:820) at org.mortbay.http.HttpConnection.handleNext(HttpConnection.java:986) at org.mortbay.http.HttpConnection.handle(HttpConnection.java:837) at org.mortbay.http.SocketListener.handleConnection(SocketListener.java:245) at org.mortbay.util.ThreadedServer.handle(ThreadedServer.java:357) at org.mortbay.util.ThreadPool$PoolThread.run(ThreadPool.java:534)Caused by: org.eclipse.birt.report.engine.api.EngineException: Can't find the prepared query org.eclipse.birt.data.engine.olap.impl.query.CubeQueryDefinition@e85c92. at org.eclipse.birt.report.engine.data.dte.DteDataEngine.doExecuteCube(DteDataEngine.java:162) at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.execute(AbstractDataEngine.java:258) at org.eclipse.birt.report.engine.executor.ExecutorManager$ExecutorContext.executeQuery(ExecutorManager.java:422) ... 71 more

Attached the Screenshot of labortranstype in the dataset laborDataSet.

Please help.

Suresh.

Attached File(s)

•  Labortranstype.docx (61.5K)
  Number of downloads: 0

[peraka]

Hi Williams,

How do I write an expression for laborhours calculation based on labortranstype in the Aggregation builder, which includes Dimensions & Measures.
Attached the current Aggregation Builder of the field.

Attached File(s)

•  Aggregation Builder.docx (21.96K)
  Number of downloads: 2

[mwilliams]

Ok, so your crosstab looks something like:

.......................| A | B | C | D | E | F | G | H |
row dimension1 | 10 | 9 | 8 | 7 | 6 | 5 | 4 | 3 |
row dimension2 | 11 | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
etc

and you want to sum the columns that are not ABCD?

Can you just attach your report? Or send it to me in an email?

[peraka]

 mncreport.rptdesign (178.3K)
Number of downloads: 4
Hi Williams, I had attached the report. It is displays preview as below



Suresh 1 2 3 4 5 6 7 8..........
A 2.00
B
C
D
Work 3.5 5.00
Non-work
Total Normal Hours 5.5 5.00

in the above example, for day 2, total is 5.5, but I want total to be 3.5 only. Whenever there are values against A,B,C,D...those shouldn't be included in the "Total Normal Hours".

Suresh.

[mwilliams]

Here's a way to do it. I keep a persistentGlobalVariable for each day and only add the value if it's not A-D. Let me know if you have questions.

Attached File(s)

•  OnlySumCertainRowsInCrosstab.zip (2.46K)
  Number of downloads: 5

[peraka]

Hi Williams, I have attached the output screenshot. You have my rptdesign file.

My requirement is only to exclude the highlighted hours 2.00 from the highlighted total normal hours 4.50.

Hope this will be clear to you.

Attached File(s)

•  Reportoutputscreenshot.docx (40.95K)
  Number of downloads: 5

[mwilliams]

Did you not look at the above example? It does exactly that. I just used my own data like yours, so I could run the report. It uses script to sum the values for each column except those in the A, B, C, and D rows.

[peraka]

Hi Williams,
I have gone through your attachment but I was totally confused. I really don't know how to add that to my report.

Do I have to add those lines to my XML Source Open Method ?

Please let me know.

My XML Source file is available in this post, let me know where exactly to add.

Thanks in advance for your support.

Suresh.

[mwilliams]

The only script you'll need from the report is what is on the measure element in the crosstab, in its onCreate method and then delete the grand total element from the grand total row and replace it with a text box like I put in my design. The script in my dataSet in the report is only to create data like yours so I could run the report. Don't worry about that. FYI, I re-posted the design with simplified script in the measure element. The only thing you should need to change in the script is the group and field names for the two dimensions and the name for the measure.

Let me know if you have further questions!

[peraka]

Williams,

I could see an array in open method, did you use that just to display it in the preview without any dataset ?

My understanding is

"I have to copy and paste the given script in the 'onCreate' method. "
But I want to exclude it in "Total Normal Hours". It's displayed from the expression given just above "Total Hours" label.

I think you can figure out from the XML. Please let me know how to exclude this from that expression, is that possible to add 'onCreate' method to that expression.

Suresh.

[mwilliams]

There is an array in the open method of the scripted dataSet, yes. This is the array that I use to fill my dataSet so that I can have data like yours. You don't need to worry about that script!

[mwilliams]

As for your other question, maybe I'm not understanding what you're trying to do. I thought you were trying to create a total number of hours by adding a column without including values from A, B, C, or D? The name of my "Total" row was given by me. You could put "Total Normal Hours" in the label, if you want. If you want the total besides A-D in one row, the A-D total in another row, and then a final total, you'll just need to do a little more work on top of my example. I can do that too, if you'd like. Let me know.

[peraka]

What you've done is correct. but there is a difference in "Total Hours" and "Total Normal Hours".

Total Hours = Total Normal hours + OT hours

Total Normal Hours = WORK + STDN + PERMIT + TRAV

OT hours = A+B+C+D

Currently report it displaying "Total Normal Hours" including A,B,C,D. Which I don't want.

this "Total Normal Hours" is displayed using an expression just above the "Total Hours".

You'll understand this when you put the xml source file in eclipse and see for the layout.

As you mentioned, I need a separate total for "OT hours" which should only include "A,B,C,D".

Thanks,
Suresh.