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; } } }

nested tables

networkfai

Hi everyone 
 
I have 6 table within one main table, the first 5 nested table can refrence 2 sets of attributes to populate there table with data via the ? place holder 
 
Please see Code 
<pre class='_prettyXprint _lang-auto _linenums:0'>

//Main Dataset

maximoDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());
maximoDataSet.open();

var sqlText = new String();

// Add query to sqlText variable.
sqlText = " select workorder.workorderid, workorder.woclass, workorder.wonum, workorder.description as wodesc, workorder.siteid, workorder.schedstart, "
+ " workorder.schedfinish, workorder.status, workorder.targstartdate, workorder.targcompdate, workorder.parent, "
+ " workorder.actstart, workorder.actfinish, workorder.worktype, workorder.reportdate, workorder.reportedby, workorder.wopriority, "
+ " workorder.glaccount, workorder.vendor, workorder.contract, workorder.classstructureid, workorder.failurecode, workorder.problemcode, "
+ " workorder.lead, workorder.supervisor, workorder.persongroup, workorder.owner, workorder.ownergroup, "
+ " workorder.commodity, workorder.commoditygroup, workorder.onbehalfof, "
+ " workorder.jpnum, workorder.assetnum, workorder.location, workorder.istask, workorder.orgid, workorder.cinum, jobplan.description as jpdesc, "
+ " asset.description as assetdesc, locations.description as locdesc, ci.description as cidesc, "
//tranportation specific fields START
+ " asset.status as assetstatus, asset.plustyear, asset.manufacturer, asset.plustmodel, asset.assettype, workorder.plustwarrpresent, "
+ " workorder.plustcomp, workorder.plustpos, workorder.plustreason, workorder.plustaccomp, workorder.plustfailure "
//tranportation specific fields END
+ "from workorder left outer join "
+ " wmassignment on "
+ " workorder.siteid=wmassignment.siteid "
+ " and workorder.wonum=wmassignment.wonum "
+ " and workorder.taskid=wmassignment.taskid left outer join "
+ " jobplan on "
+ " ( "
+ " (workorder.orgid=jobplan.orgid and workorder.siteid=jobplan.siteid) "
+ " or (workorder.orgid=jobplan.orgid and jobplan.siteid is null) "
+ " or (jobplan.orgid is null and jobplan.siteid is null) "
+ " ) "
+ " and workorder.jpnum=jobplan.jpnum left outer join "
+ " asset on "
+ " workorder.siteid=asset.siteid "
+ " and workorder.assetnum=asset.assetnum left outer join "
+ " locations on "
+ "workorder.siteid = locations.siteid "
+ " and workorder.location=locations.location left outer join "
+ " wochange on "
+ " workorder.siteid=wochange.siteid "
+ " and workorder.wonum=wochange.wonum left outer join "
+ " worelease on "
+ " workorder.siteid=worelease.siteid "
+ " and workorder.wonum=worelease.wonum left outer join "
+ " woactivity on "
+ " workorder.siteid=woactivity.siteid "
+ " and workorder.wonum=woactivity.wonum left outer join "
+ " ci on "
+ " workorder.cinum=ci.cinum "
// Include the Maximo where clause
//+ " where " + params["where"]
+ " where workorder.wonum = '321402' "
;
maximoDataSet.setQuery(sqlText);
</pre>
 
The second dataset refrences the main dataset 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>taskDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());
taskDataSet.open();

var sqlText = new String();

// Add query to sqlText variable.
sqlText = "select workorder.taskid, workorder.description, workorder.status, woactivity.pointnum, "
+ " workorder.measurementvalue, workorder.measuredate, workorder.observation, "
//tranportation new fields START
+ " workorder.plustcomp, workorder.plustpos, workorder.plustreason, "
+ " workorder.plustaccomp, workorder.plustfailure, workorder.plusthaswarranty, "
+ " wplabor.craft as wplcraft, wplabor.laborhrs as wplhours, "
+ " wpitem.itemnum AS wpmitemnum, wpitem.description AS wpmdesc, wpitem.itemqty AS wpmqty "
//tranportation new fields END
+ "from workorder left outer join "
//+ "from workorder inner join "
+ " woactivity on "
+ " workorder.siteid=woactivity.siteid "
+ " and workorder.wonum=woactivity.wonum "
+ " LEFT OUTER JOIN wplabor ON workorder.siteid = wplabor.siteid AND workorder.wonum = wplabor.wonum "
+ " LEFT OUTER JOIN wpitem ON wpitem.siteid = wplabor.siteid AND wpitem.wonum = wplabor.wonum "
//+ " inner JOIN wplabor ON workorder.siteid = wplabor.siteid AND workorder.wonum = wplabor.wonum "
//+ " inner JOIN wpitem ON wpitem.siteid = wplabor.siteid AND wpitem.wonum = wplabor.wonum "
+ " where workorder.parent=? "
+ " and workorder.siteid=? "
+ " and workorder.istask=1 "
+ " order by workorder.taskid"
//+ " order by wplhours"
;

taskDataSet.setQuery(sqlText);

taskDataSet.setQueryParameterValue(1, rows[0]["wonum"]);
taskDataSet.setQueryParameterValue(2, rows[0]["siteid"]);

</pre>
 
now where it all falls down is if on the 5th dataset, I have a nested table within that dataset, the nested table is suppose to populate it's table from information on the outer table that is groupped by task ID. Now when I try to run the query through the open method. It throws up an error. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
taskDataSet1 = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());
taskDataSet1.open();

var sqlText = new String();

// Add query to sqlText variable.
sqlText = "select workorder.taskid, workorder.description, workorder.status, woactivity.pointnum, "
+ " workorder.measurementvalue, workorder.measuredate, workorder.observation, "
//tranportation new fields START
+ " workorder.plustcomp, workorder.plustpos, workorder.plustreason, "
+ " workorder.plustaccomp, workorder.plustfailure, workorder.plusthaswarranty, "
+ " wplabor.craft as wplcraft, wplabor.laborhrs as wplhours, "
+ " wpitem.itemnum AS wpmitemnum, wpitem.description AS wpmdesc, wpitem.itemqty AS wpmqty "
//tranportation new fields END
+ "from workorder left outer join "
//+ "from workorder inner join "
+ " woactivity on "
+ " workorder.siteid=woactivity.siteid "
+ " and workorder.wonum=woactivity.wonum "
+ " LEFT OUTER JOIN wplabor ON workorder.siteid = wplabor.siteid AND workorder.wonum = wplabor.wonum "
+ " LEFT OUTER JOIN wpitem ON wpitem.siteid = wplabor.siteid AND wpitem.wonum = wplabor.wonum "
//+ " inner JOIN wplabor ON workorder.siteid = wplabor.siteid AND workorder.wonum = wplabor.wonum "
//+ " inner JOIN wpitem ON wpitem.siteid = wplabor.siteid AND wpitem.wonum = wplabor.wonum "
+ " where workorder.parent=? "
+ " and workorder.siteid='NRFAMS' "
+ " and workorder.taskid= " + row._outer["taskid"]
+ " and workorder.istask=1 "
+ " order by workorder.taskid"
//+ " order by wplhours"
;

taskDataSet1.setQuery(sqlText);

taskDataSet1.setQueryParameterValue(1, rows[0]["wonum"]);
//taskDataSet1.setQueryParameterValue(2, row[0]("taskid"));
</pre>
 
I know it is the <pre class='_prettyXprint _lang-auto _linenums:0'>row.outer["taskid"]</pre>, but since the outer table is grouped by taskid, I didnt think it would be a problem, or maybe i'm not thinking in a logical way, please can anyone help, and nope I cant upgrade BIRT, because the system it works with wont work with any other version

Find more posts tagged with

Comments

JasonW

Are you sure it is the row.__outer causing the issue? If you hard code the value does it work? Does the 5th dataset have a parameter? If so you can reference the dataset parameter like inputParams["mydatasetparameter"]

Also what is the error?

Jason

networkfai

<blockquote class='ipsBlockquote' data-author="'JasonW'" data-cid="80470" data-time="1311351784" data-date="22 July 2011 - 09:23 AM">
Are you sure it is the row.__outer causing the issue? If you hard code the value does it work? Does the 5th dataset have a parameter? If so you can reference the dataset parameter like inputParams["mydatasetparameter"] 
 
Also what is the error? 
 
Jason </blockquote>

networkfai

yes it does work, if i replace row._outer["taskid"] with a hard coded value like 10, but then that defeats the objective of the outer table feeding the inner table via row._outer["taskid"], because the task id tends to change value with each record

Hans_vd

Did you notice that there is a difference between row._outer["taskid"] and row.__outer
1 underscore character vs 2 underscore characters

Regards
Hans

networkfai

<a class='bbc_url' href='http://www.birt-exchange.org/org/forum/public/style_emoticons/'>http://www.birt-exchange.org/org/forum/public/style_emoticons/</a><#EMO_DIR#>/eek.gif that was a spelling mistake on my part and has no bearing on the original code, the problem still persist anyone with any ideas, really desperate

JasonW

What is the datatype for the taskid? Is it a string or integer?

Can you add something like the following to your script right before you set the id?

importPackage( Packages.java.io );
out = new PrintWriter( new FileWriter( "c:/temp/taskidcheck.txt", true ) );
out.println( "Task Id ="+ row.__outer["taskid"]);
out.flush();
out.close();

Check the text file to see if it contains the right id.

If task id is a string you may need single quotes around it like:

+ " and workorder.taskid= '" + row._outer["taskid"]
+ "' and workorder.istask=1 "

Jason

networkfai

its definatly an integer, but as soon as i run it I get an invalid column type error. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
Mon Jul 25 16:57:00 BST 2011 INFO DataSet [taskDataSetx]: select workorder.taskid, workorder.description, workorder.status, woactivity.pointnum, workorder.measurementvalue, workorder.measuredate, workorder.observation, workorder.plustcomp, workorder.plustpos, workorder.plustreason, workorder.plustaccomp, workorder.plustfailure, workorder.plusthaswarranty, wplabor.craft as wplcraft, wplabor.laborhrs as wplhours, wpitem.itemnum AS wpmitemnum, wpitem.description AS wpmdesc, wpitem.itemqty AS wpmqty from workorder left outer join woactivity on workorder.siteid=woactivity.siteid and workorder.wonum=woactivity.wonum LEFT OUTER JOIN wplabor ON workorder.siteid = wplabor.siteid AND workorder.wonum = wplabor.wonum LEFT OUTER JOIN wpitem ON wpitem.siteid = wplabor.siteid AND wpitem.wonum = wplabor.wonum where workorder.parent='321402' and workorder.siteid='NRFAMS' and workorder.istask=1 order by workorder.taskid
Mon Jul 25 16:57:00 BST 2011 INFO DataSet [taskDataSetx] close called.
Mon Jul 25 16:57:00 BST 2011 INFO DataSet [taskDataSet1] open called.
Mon Jul 25 16:57:00 BST 2011 INFO DataSet [taskDataSet1]: select workorder.taskid, workorder.description, workorder.status, woactivity.pointnum, workorder.measurementvalue, workorder.measuredate, workorder.observation, workorder.plustcomp, workorder.plustpos, workorder.plustreason, workorder.plustaccomp, workorder.plustfailure, workorder.plusthaswarranty, wplabor.craft as wplcraft, wplabor.laborhrs as wplhours, wpitem.itemnum AS wpmitemnum, wpitem.description AS wpmdesc, wpitem.itemqty AS wpmqty from workorder left outer join woactivity on workorder.siteid=woactivity.siteid and workorder.wonum=woactivity.wonum LEFT OUTER JOIN wplabor ON workorder.siteid = wplabor.siteid AND workorder.wonum = wplabor.wonum LEFT OUTER JOIN wpitem ON wpitem.siteid = wplabor.siteid AND wpitem.wonum = wplabor.wonum where workorder.parent=? and workorder.siteid='NRFAMS' and workorder.taskid=? and workorder.istask=1 order by workorder.taskid
Mon Jul 25 16:57:00 BST 2011 INFO DataSet [taskDataSet1] Bind index [1] = org.eclipse.birt.data.engine.script.DataExceptionMocker@7e2d66
Mon Jul 25 16:57:00 BST 2011 ERROR DataSet [taskDataSet1] fetch failed:
java.sql.SQLException: Invalid column type
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:110)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:171)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:439)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:7916)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:7496)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8153)
at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:8134)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:213)
at com.ibm.tivoli.maximo.report.script.MXReportDataSetImpl.fetch(MXReportDataSetImpl.java:239)
at sun.reflect.GeneratedMethodAccessor371.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.mozilla.javascript.MemberBox.invoke(MemberBox.java:174)
at org.mozilla.javascript.NativeJavaMethod.call(NativeJavaMethod.java:197)
at org.mozilla.javascript.optimizer.OptRuntime.callProp0(OptRuntime.java:116)
at org.mozilla.javascript.gen.c49._c1(DataSet[taskDataSet1].__bm_FETCH:2)
at org.mozilla.javascript.gen.c49.call(DataSet[taskDataSet1].__bm_FETCH)
at org.mozilla.javascript.optimizer.OptRuntime.callName0(OptRuntime.java:105)
at org.mozilla.javascript.gen.c14._c0(DataSet[mainDataSet].__bm_FETCH:1)
at org.mozilla.javascript.gen.c14.call(DataSet[mainDataSet].__bm_FETCH)
at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:304)
at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:2769)
at org.mozilla.javascript.gen.c14.call(DataSet[mainDataSet].__bm_FETCH)
at org.mozilla.javascript.gen.c14.exec(DataSet[mainDataSet].__bm_FETCH)
at org.eclipse.birt.core.script.JavascriptEvalUtil.evaluateRawScript(JavascriptEvalUtil.java:98)
at org.eclipse.birt.core.script.JavascriptEvalUtil.evaluateScript(JavascriptEvalUtil.java:137)
at org.eclipse.birt.data.engine.script.ScriptEvalUtil.evaluateJSAsExpr(ScriptEvalUtil.java:548)
at org.eclipse.birt.data.engine.script.JSMethodRunner.runScript(JSMethodRunner.java:79)
at org.eclipse.birt.report.engine.script.internal.DtEScriptExecutor.handleJS(DtEScriptExecutor.java:71)
at org.eclipse.birt.report.engine.script.internal.DataSetScriptExecutor.handleJS(DataSetScriptExecutor.java:159)
at org.eclipse.birt.report.engine.script.internal.ScriptDataSetScriptExecutor.handleFetch(ScriptDataSetScriptExecutor.java:100)
at org.eclipse.birt.data.engine.impl.ScriptDataSetRuntime.fetch(ScriptDataSetRuntime.java:84)
at org.eclipse.birt.data.engine.impl.PreparedScriptDSQuery$ScriptDSQueryExecutor$CustomDataSet.fetch(PreparedScriptDSQuery.java:247)
at org.eclipse.birt.data.engine.executor.cache.OdiAdapter.fetch(OdiAdapter.java:148)
at org.eclipse.birt.data.engine.executor.cache.RowResultSet.next(RowResultSet.java:91)
at org.eclipse.birt.data.engine.executor.cache.ExpandableRowResultSet.next(ExpandableRowResultSet.java:62)
a at org.eclipse.birt.data.engine.executor.cache.SmartCacheHelper.populateData(SmartCacheHelper.java:27
a at org.eclipse.birt.data.engine.executor.cache.SmartCacheHelper.initInstance(SmartCacheHelper.java:25
a at org.eclipse.birt.data.engine.executor.cache.SmartCacheHelper.getResultSetCache(SmartCacheHelper.java:21
a at org.eclipse.birt.data.engine.executor.cache.SmartCache.<init>(SmartCache.java:6
a at org.eclipse.birt.data.engine.executor.transform.pass.PassUtil.populateOdiResultSet(PassUtil.java:11
a at org.eclipse.birt.data.engine.executor.transform.pass.PassUtil.pass(PassUtil.java:6
a at org.eclipse.birt.data.engine.executor.transform.pass.PassManager.doSinglePass(PassManager.java:12
a at org.eclipse.birt.data.engine.executor.transform.pass.PassManager.pass(PassManager.java:8
a at org.eclipse.birt.data.engine.executor.transform.pass.PassManager.populateResultSet(PassManager.java:6
a at org.eclipse.birt.data.engine.executor.transform.ResultSetPopulator.populateResultSet(ResultSetPopulator.java:21
a at org.eclipse.birt.data.engine.executor.transform.CachedResultSet.<init>(CachedResultSet.java:12
a at org.eclipse.birt.data.engine.executor.CandidateQuery.execute(CandidateQuery.java:10
a at org.eclipse.birt.data.engine.impl.PreparedScriptDSQuery$ScriptDSQueryExecutor.executeOdiQuery(PreparedScriptDSQuery.java:21
a at org.eclipse.birt.data.engine.impl.QueryExecutor.execute(QueryExecutor.java:74
a at org.eclipse.birt.data.engine.impl.ServiceForQueryResults.executeQuery(ServiceForQueryResults.java:18
a at org.eclipse.birt.data.engine.impl.QueryResults.getResultIterator(QueryResults.java:14
a at org.eclipse.birt.report.engine.data.dte.DteResultSet.<init>(DteResultSet.java:10
a at org.eclipse.birt.report.engine.data.dte.DteDataEngine.doExecuteQuery(DteDataEngine.java:12
a at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.execute(AbstractDataEngine.java:21
a at org.eclipse.birt.report.engine.executor.ExecutionContext.executeQuery(ExecutionContext.java:164
a at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:6
a at org.eclipse.birt.report.engine.executor.TableItemExecutor.execute(TableItemExecutor.java:7
a at org.eclipse.birt.report.engine.internal.executor.l18n.LocalizedReportItemExecutor.execute(LocalizedReportItemExecutor.java:3
a at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutChildren(HTMLBlockStackingLM.java:6
a at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:15
a at org.eclipse.birt.report.engine.layout.html.HTMLInlineStackingLM.resumeLayout(HTMLInlineStackingLM.java:9
a at org.eclipse.birt.report.engine.layout.html.HTMLInlineStackingLM.layoutChildren(HTMLInlineStackingLM.java:13
a at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:15
a at org.eclipse.birt.report.engine.layout.html.HTMLRowLM.layout(HTMLRowLM.java:3
a at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutChildren(HTMLBlockStackingLM.java:6
a at org.eclipse.birt.report.engine.layout.html.HTMLTableBandLM.layoutChildren(HTMLTableBandLM.java:6
a at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:15
a at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutChildren(HTMLBlockStackingLM.java:6
a at org.eclipse.birt.report.engine.layout.html.HTMLTableLM.layoutChildren(HTMLTableLM.java:7
a at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:15
a at org.eclipse.birt.report.engine.layout.html.HTMLPageLM.layoutChildren(HTMLPageLM.java:13
a at org.eclipse.birt.report.engine.layout.html.HTMLPageLM.layout(HTMLPageLM.java:8
a at org.eclipse.birt.report.engine.layout.html.HTMLReportLayoutEngine.layout(HTMLReportLayoutEngine.java:8
a at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.doRun(RunAndRenderTask.java:20
a at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.run(RunAndRenderTask.java:14
a at org.eclipse.birt.report.service.ReportEngineService.runAndRenderReport(Unknown Sourc
a at org.eclipse.birt.report.service.ReportEngineService.runAndRenderReport(Unknown Sourc
a at org.eclipse.birt.report.service.BirtViewerReportService.runAndRenderReport(Unknown Sourc
a at org.eclipse.birt.report.service.actionhandler.BirtRunAndRenderActionHandler.__execute(Unknown Sourc
a at org.eclipse.birt.report.service.actionhandler.AbstractBaseActionHandler.execute(Unknown Sourc
a at org.eclipse.birt.report.presentation.aggregation.layout.EngineFragment.doService(Unknown Sourc
a at org.eclipse.birt.report.presentation.aggregation.AbstractBaseFragment.service(Unknown Sourc
a at org.eclipse.birt.report.servlet.BirtEngineServlet.__doGet(Unknown Sourc
a at org.eclipse.birt.report.servlet.BaseReportEngineServlet.doGet(Unknown Sourc
a at javax.servlet.http.HttpServlet.service(HttpServlet.java:74
a at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:32
a at javax.servlet.http.HttpServlet.service(HttpServlet.java:85
a at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:24
a at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:19
a at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:25
a at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:64
a at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:48
a at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:99
a at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:19
a at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:64
a at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:24
a at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:64
a at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:48
a at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:99
a at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:242
a at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:18
a at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:64
a at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:17
a at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:64
a at org.eclipse.tomcat.internal.EclipseErrorReportValve.invoke(EclipseErrorReportValve.java:15
a at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:64
a at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:48
a at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:99
a at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:17
a at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:64
a at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:48
a at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:99
a at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:19
a at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:82
a at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:70
a at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:58
a at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:68
a at java.lang.Thread.run(Unknown Sourc

Mon Jul 25 16:57:00 BST 2011 INFO DataSet [taskDataSet1] close called.
Mon Jul 25 16:57:00 BST 2011 INFO DataSet [taskDataSet1] open called.
Mon Jul 25 16:57:00 BST 2011 INFO DataSet [taskDataSet1]: select workorder.taskid, workorder.description, workorder.status, woactivity.pointnum, workorder.measurementvalue, workorder.measuredate, workorder.observation, workorder.plustcomp, workorder.plustpos, workorder.plustreason, workorder.plustaccomp, workorder.plustfailure, workorder.plusthaswarranty, wplabor.craft as wplcraft, wplabor.laborhrs as wplhours, wpitem.itemnum AS wpmitemnum, wpitem.description AS wpmdesc, wpitem.itemqty AS wpmqty from workorder left outer join woactivity on workorder.siteid=woactivity.siteid and workorder.wonum=woactivity.wonum LEFT OUTER JOIN wplabor ON workorder.siteid = wplabor.siteid AND workorder.wonum = wplabor.wonum LEFT OUTER JOIN wpitem ON wpitem.siteid = wplabor.siteid AND wpitem.wonum = wplabor.wonum where workorder.parent=? and workorder.siteid='NRFAMS' and workorder.taskid=? and workorder.istask=1 order by workorder.taskid
Mon Jul 25 16:57:00 BST 2011 INFO DataSet [taskDataSet1] Bind index [1] = org.eclipse.birt.data.engine.script.DataExceptionMocker@de8c87
Mon Jul 25 16:57:00 BST 2011 ERROR DataSet [taskDataSet1] fetch failed:
java.sql.SQLException: Invalid column type
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:110)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:171)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:439)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:7916)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:7496)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8153)
at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:8134)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:213)
at com.ibm.tivoli.maximo.report.script.MXReportDataSetImpl.fetch(MXReportDataSetImpl.java:239)
at sun.reflect.GeneratedMethodAccessor371.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.mozilla.javascript.MemberBox.invoke(MemberBox.java:174)
at org.mozilla.javascript.NativeJavaMethod.call(NativeJavaMethod.java:197)
at org.mozilla.javascript.optimizer.OptRuntime.callProp0(OptRuntime.java:116)
at org.mozilla.javascript.gen.c49._c1(DataSet[taskDataSet1].__bm_FETCH:2)
at org.mozilla.javascript.gen.c49.call(DataSet[taskDataSet1].__bm_FETCH)
at org.mozilla.javascript.optimizer.OptRuntime.callName0(OptRuntime.java:105)
at org.mozilla.javascript.gen.c14._c0(DataSet[mainDataSet].__bm_FETCH:1)
at org.mozilla.javascript.gen.c14.call(DataSet[mainDataSet].__bm_FETCH)
at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:304)
at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:2769)
at org.mozilla.javascript.gen.c14.call(DataSet[mainDataSet].__bm_FETCH)
at org.mozilla.javascript.gen.c14.exec(DataSet[mainDataSet].__bm_FETCH)
at org.eclipse.birt.core.script.JavascriptEvalUtil.evaluateRawScript(JavascriptEvalUtil.java:98)
at org.eclipse.birt.core.script.JavascriptEvalUtil.evaluateScript(JavascriptEvalUtil.java:137)
at org.eclipse.birt.data.engine.script.ScriptEvalUtil.evaluateJSAsExpr(ScriptEvalUtil.java:548)
at org.eclipse.birt.data.engine.script.JSMethodRunner.runScript(JSMethodRunner.java:79)
at org.eclipse.birt.report.engine.script.internal.DtEScriptExecutor.handleJS(DtEScriptExecutor.java:71)
at org.eclipse.birt.report.engine.script.internal.DataSetScriptExecutor.handleJS(DataSetScriptExecutor.java:159)
at org.eclipse.birt.report.engine.script.internal.ScriptDataSetScriptExecutor.handleFetch(ScriptDataSetScriptExecutor.java:100)
at org.eclipse.birt.data.engine.impl.ScriptDataSetRuntime.fetch(ScriptDataSetRuntime.java:84)
at org.eclipse.birt.data.engine.impl.PreparedScriptDSQuery$ScriptDSQueryExecutor$CustomDataSet.fetch(PreparedScriptDSQuery.java:247)
at org.eclipse.birt.data.engine.executor.cache.OdiAdapter.fetch(OdiAdapter.java:148)
at org.eclipse.birt.data.engine.executor.cache.RowResultSet.next(RowResultSet.java:91)
at org.eclipse.birt.data.engine.executor.cache.ExpandableRowResultSet.next(ExpandableRowResultSet.java:62)
at org.eclipse.birt.data.engine.executor.cache.SmartCacheHelper.populateData(SmartCacheHelper.java:275)
at org.eclipse.birt.data.engine.executor.cache.SmartCacheHelper.initInstance(SmartCacheHelper.java:250)
at org.eclipse.birt.data.engine.executor.cache.SmartCacheHelper.getResultSetCache(SmartCacheHelper.java:213)
at org.eclipse.birt.data.engine.executor.cache.SmartCache.<init>(SmartCache.java:66)
at org.eclipse.birt.data.engine.executor.transform.pass.PassUtil.populateOdiResultSet(PassUtil.java:111)
at org.eclipse.birt.data.engine.executor.transform.pass.PassUtil.pass(PassUtil.java:66)
at org.eclipse.birt.data.engine.executor.transform.pass.PassManager.doSinglePass(PassManager.java:123)
at org.eclipse.birt.data.engine.executor.transform.pass.PassManager.pass(PassManager.java:83)
at org.eclipse.birt.data.engine.executor.transform.pass.PassManager.populateResultSet(PassManager.java:65)
at org.eclipse.birt.data.engine.executor.transform.ResultSetPopulator.populateResultSet(ResultSetPopulator.java:210)
at org.eclipse.birt.data.engine.executor.transform.CachedResultSet.<init>(CachedResultSet.java:124)
at org.eclipse.birt.data.engine.executor.CandidateQuery.execute(CandidateQuery.java:106)
at org.eclipse.birt.data.engine.impl.PreparedScriptDSQuery$ScriptDSQueryExecutor.executeOdiQuery(PreparedScriptDSQuery.java:213)
at org.eclipse.birt.data.engine.impl.QueryExecutor.execute(QueryExecutor.java:743)
at org.eclipse.birt.data.engine.impl.ServiceForQueryResults.executeQuery(ServiceForQueryResults.java:183)
at org.eclipse.birt.data.engine.impl.QueryResults.getResultIterator(QueryResults.java:148)
at org.eclipse.birt.report.engine.data.dte.DteResultSet.<init>(DteResultSet.java:100)
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:210)
at org.eclipse.birt.report.engine.executor.ExecutionContext.executeQuery(ExecutionContext.java:1641)
at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:62)
at org.eclipse.birt.report.engine.executor.TableItemExecutor.execute(TableItemExecutor.java:73)
at org.eclipse.birt.report.engine.internal.executor.l18n.LocalizedReportItemExecutor.execute(LocalizedReportItemExecutor.java:35)
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutChildren(HTMLBlockStackingLM.java:63)
at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:159)
at org.eclipse.birt.report.engine.layout.html.HTMLInlineStackingLM.resumeLayout(HTMLInlineStackingLM.java:94)
at org.eclipse.birt.report.engine.layout.html.HTMLInlineStackingLM.layoutChildren(HTMLInlineStackingLM.java:138)
at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:159)
at org.eclipse.birt.report.engine.layout.html.HTMLRowLM.layout(HTMLRowLM.java:30)
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutChildren(HTMLBlockStackingLM.java:68)
at org.eclipse.birt.report.engine.layout.html.HTMLTableBandLM.layoutChildren(HTMLTableBandLM.java:67)
at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:159)
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutChildren(HTMLBlockStackingLM.java:68)
at org.eclipse.birt.report.engine.layout.html.HTMLTableLM.layoutChildren(HTMLTableLM.java:74)
at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:159)
at org.eclipse.birt.report.engine.layout.html.HTMLPageLM.layoutChildren(HTMLPageLM.java:139)
at org.eclipse.birt.report.engine.layout.html.HTMLPageLM.layout(HTMLPageLM.java:81)
at org.eclipse.birt.report.engine.layout.html.HTMLReportLayoutEngine.layout(HTMLReportLayoutEngine.java:85)
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.doRun(RunAndRenderTask.java:205)
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.run(RunAndRenderTask.java:142)
at org.eclipse.birt.report.service.ReportEngineService.runAndRenderReport(Unknown Source)
at org.eclipse.birt.report.service.ReportEngineService.runAndRenderReport(Unknown Source)
at org.eclipse.birt.report.service.BirtViewerReportService.runAndRenderReport(Unknown Source)
at org.eclipse.birt.report.service.actionhandler.BirtRunAndRenderActionHandler.__execute(Unknown Source)
at org.eclipse.birt.report.service.actionhandler.AbstractBaseActionHandler.execute(Unknown Source)
at org.eclipse.birt.report.presentation.aggregation.layout.EngineFragment.doService(Unknown Source)
at org.eclipse.birt.report.presentation.aggregation.AbstractBaseFragment.service(Unknown Source)
at org.eclipse.birt.report.servlet.BirtEngineServlet.__doGet(Unknown Source)
at org.eclipse.birt.report.servlet.BaseReportEngineServlet.doGet(Unknown Source)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:246)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2422)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:171)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.eclipse.tomcat.internal.EclipseErrorReportValve.invoke(EclipseErrorReportValve.java:153)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:199)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:828)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:700)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:584)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Unknown Source)

Mon Jul 25 16:57:00 BST 2011 INFO DataSet [taskDataSet1] close called.
Mon Jul 25 16:57:00 BST 2011 INFO DataSet [taskDataSet1] open called.
Mon Jul 25 16:57:00 BST 2011 INFO DataSet [taskDataSet1]: select workorder.taskid, workorder.description, workorder.status, woactivity.pointnum, workorder.measurementvalue, workorder.measuredate, workorder.observation, workorder.plustcomp, workorder.plustpos, workorder.plustreason, workorder.plustaccomp, workorder.plustfailure, workorder.plusthaswarranty, wplabor.craft as wplcraft, wplabor.laborhrs as wplhours, wpitem.itemnum AS wpmitemnum, wpitem.description AS wpmdesc, wpitem.itemqty AS wpmqty from workorder left outer join woactivity on workorder.siteid=woactivity.siteid and workorder.wonum=woactivity.wonum LEFT OUTER JOIN wplabor ON workorder.siteid = wplabor.siteid AND workorder.wonum = wplabor.wonum LEFT OUTER JOIN wpitem ON wpitem.siteid = wplabor.siteid AND wpitem.wonum = wplabor.wonum where workorder.parent=? and workorder.siteid='NRFAMS' and workorder.taskid=? and workorder.istask=1 order by workorder.taskid
Mon Jul 25 16:57:00 BST 2011 INFO DataSet [taskDataSet1] Bind index [1] = org.eclipse.birt.data.engine.script.DataExceptionMocker@9fb03a
Mon Jul 25 16:57:00 BST 2011 ERROR DataSet [taskDataSet1] fetch failed:
</pre>
 
I have even stopped using row._outer command & started using 
<pre class='_prettyXprint _lang-auto _linenums:0'>DataSet1.setQueryParameterValue(2, rows[0]["taskid"]);</pre>
but still getting invalid column error. Is it actually possible to pass data between an outer table and inner nested table

JasonW

What version of BIRT is this using?
can you try setting the task id in a global variable in the outer tables detail row onCreate?

var ti =this.getColumnValue("taskid");
reportContext.setGlobalVariable("mytaskid", ti);

Then use

reportContext.getGlobalVariable("mytaskid"); in your script on the 5th dataset?

Jason

networkfai

I am using birt 2.1.2 with maximo, as for the last soloution it still came up with invalid column, am i missing a step or is it possible to direct me where Im going wrong :unsure:

networkfai

<blockquote class='ipsBlockquote' data-author="'networkfai'" data-cid="80543" data-time="1311618283" data-date="25 July 2011 - 11:24 AM">
I am using birt 2.1.2 with maximo, as for the last soloution it still came up with invalid column, am i missing a step or is it possible to direct me where Im going wrong :unsure: </blockquote>
 
 
My mistake this the correct build version 
 
Eclipse BIRT Designer Version 2.1.1.v20060922-1058 Build <20060926-0959>

JasonW

Look at the attached example. Look at the outer table's oncreate script on the detail row and look at the beforeOpen script on the inner dataset.

Jason

networkfai

<blockquote class='ipsBlockquote' data-author="'JasonW'" data-cid="80550" data-time="1311621365" data-date="25 July 2011 - 12:16 PM">
Look at the attached example. Look at the outer table's oncreate script on the detail row and look at the beforeOpen script on the inner dataset. 
 
Jason </blockquote>
 
Eureka, it works but there is one problem. The outer table list the task number 10,10,10,20, & 30. The inner table then use the outer task number to display the task details, only one problem is the task number always stays at 10, hence if a task number is 20, instead of the inner task to look for details with task number 20, it only looks for task 10. I have place this code on the oncreate method for the outer table 
<pre class='_prettyXprint _lang-auto _linenums:0'>
var ord = this.getRowData().getColumnValue("taskid");
reportContext.setGlobalVariable("ti", ord);
</pre>
 
On the inner taskset (open method), I have placed this code 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
var m = reportContext.getGlobalVariable("ti");
var sqlText = new String();

// Add query to sqlText variable.
sqlText = "select workorder.taskid, workorder.description, workorder.status, woactivity.pointnum, "
+ " workorder.measurementvalue, workorder.measuredate, workorder.observation, "
//tranportation new fields START
+ " workorder.plustcomp, workorder.plustpos, workorder.plustreason, "
+ " workorder.plustaccomp, workorder.plustfailure, workorder.plusthaswarranty, "
+ " wplabor.craft as wplcraft, wplabor.laborhrs as wplhours, "
+ " wpitem.itemnum AS wpmitemnum, wpitem.description AS wpmdesc, wpitem.itemqty AS wpmqty "
//tranportation new fields END
+ "from workorder left outer join "
//+ "from workorder inner join "
+ " woactivity on "
+ " workorder.siteid=woactivity.siteid "
+ " and workorder.wonum=woactivity.wonum "
+ " LEFT OUTER JOIN wplabor ON workorder.siteid = wplabor.siteid AND workorder.wonum = wplabor.wonum "
+ " LEFT OUTER JOIN wpitem ON wpitem.siteid = wplabor.siteid AND wpitem.wonum = wplabor.wonum "
//+ " inner JOIN wplabor ON workorder.siteid = wplabor.siteid AND workorder.wonum = wplabor.wonum "
//+ " inner JOIN wpitem ON wpitem.siteid = wplabor.siteid AND wpitem.wonum = wplabor.wonum "
+ " where workorder.parent=? "
+ " and workorder.siteid=? "
+ " and workorder.taskid= " + m
+ " and workorder.istask=1 "
+ " order by workorder.taskid"
//+ " order by wplhours"
;

taskDataSet2.setQuery(sqlText);

taskDataSet2.setQueryParameterValue(1, rows[0]["wonum"]);
taskDataSet2.setQueryParameterValue(2, rows[0]["siteid"]);
</pre>
 
As I said the variable m remains a constant 10, could you explain to me what Im doing wronng Ive looked at the attachemnt you sent me, but to no avail

JasonW

The table oncreate is only fired once. The script needs to be on the table detail oncreate script.

Jason

paulk

You need to use inputParams["param1"] where you had row._outer["taskid"] in the query. Then right click on the dataset and go to edit. Go to the Parameters option and create a new parameter called param1. Select the subtable object in your report, go to the Binding tab of the Property Editor. Click the Dataset Parameter Binding... button. Set the value of param1 to the taskid column of the outside table you want to reference. Now the subtable will only return rows for that taskid.

param1 can be any name you choose. You can setup multiple parameters this way, just give each a unique name.