Using multiselect parameter in dataset query

Two questions.
 
1.) How can I search this forum for a specific topic ? (i.e. multiselect parameter) I do not see a search box.
 
2.) I am using BIRT 3.7.1 with oracle. I have created a report parm p_status that is defined as String, List Box, multiple values. I want to use p_status values in a dataset query. I found a youtube vid :

https://www.youtube.com/watch?v=9aQ-ScTqAdI
but it did not work.
 
I have a dataset WO_maindataset that I want to use the value from p_status in the sql query.
 
The open script for the WO_maindataset is (this is not the full code, just the select sql) :
//

// Add query to sqlText variable. 
sqlText = "select a.wonum, " 
+ " a.description , " 
+ " a.location, " 
+ " a.status, " 
+ " a.wopriority, " 
+ " a.supervisor, " 
+ " a.reportedby, " 
+ " to_char(a.reportdate, 'mm/dd/yyyy') reportdate " 
+ " from maximo.workorder a " 
+ " where 1 = 1" 
+ where 
+ " and to_char(reportdate, 'yyyymmdd') >= '" + params["p_rpt_date_from"].substr(4,7) + params["p_rpt_date_from"].substr(0,4) + "'" 
+ " and to_char(reportdate, 'yyyymmdd') <= '" + params["p_rpt_date_to"].substr(4,7) + params["p_rpt_date_to"].substr(0,4) + "'" 
+ " and a.status in (999) " 
+ " order by wonum" 
//+ order 
;
//

I then created a beforeopen script :
 
this.sqlText=this.sqlText.replace("999",params["p_status"].value.join("','"));
 
also tried
 
this.queryText=this.querylText.replace("999",params["p_status"].value.join("','"));
 
 
 
but I am getting the following errors (See below) :
Any help is appreciated
 
 
 
<div>The following items have errors:</div>
 <div>
<div>ReportDesign (id = 1):</div>
</div>
<div>
<div>+ There are errors evaluating script "this.sqlText=this.sqlText.replace("999",params["p_status"].value.join("','")); ": 
Fail to execute script in function __bm_beforeOpen(). Source:

" + this.sqlText=this.sqlText.replace("999",params["p_status"].value.join("','")); + "

A BIRT exception occurred. See next exception for more information. 
Error evaluating Javascript expression. Script engine error: TypeError: Cannot call method "replace" of undefined (/report/data-sets/script-data-set[@id="2921"]/method[@name="beforeOpen"]#1) 
Script source: /report/data-sets/script-data-set[@id="2921"]/method[@name="beforeOpen"], line: 0, text: 
__bm_beforeOpen(). (Element ID:1) 
 at org.eclipse.birt.report.engine.script.internal.DtEScriptExecutor.handleJS(DtEScriptExecutor.java:99) 
 at org.eclipse.birt.report.engine.script.internal.DataSetScriptExecutor.handleJS(DataSetScriptExecutor.java:246) 
 at org.eclipse.birt.report.engine.script.internal.DataSetScriptExecutor.handleBeforeOpen(DataSetScriptExecutor.java:101) 
 at org.eclipse.birt.data.engine.impl.DataSetRuntime.beforeOpen(DataSetRuntime.java:543) 
 at org.eclipse.birt.data.engine.impl.QueryExecutor.dataSetBeforeOpen(QueryExecutor.java:210) 
 at org.eclipse.birt.data.engine.impl.QueryExecutor.prepareExecution(QueryExecutor.java:321) 
 at org.eclipse.birt.data.engine.impl.PreparedQuery.doPrepare(PreparedQuery.java:455) 
 at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.produceQueryResults(PreparedDataSourceQuery.java:190) 
 at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.execute(PreparedDataSourceQuery.java:178) 
 at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.execute(DataRequestSessionImpl.java:620) 
 at org.eclipse.birt.report.engine.data.dte.DteDataEngine.doExecuteQuery(DteDataEngine.java:152) 
 at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.execute(AbstractDataEngine.java:267) 
 at org.eclipse.birt.report.engine.executor.ExecutionContext.executeQuery(ExecutionContext.java:1905) 
 at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:80) 
 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:43) 
 at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.execute(WrappedReportItemExecutor.java:46) 
 at org.eclipse.birt.report.engine.internal.executor.l18n.LocalizedReportItemExecutor.execute(LocalizedReportItemExecutor.java:34) 
 at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:65) 
 at org.eclipse.birt.report.engine.layout.html.HTMLPageLM.layout(HTMLPageLM.java:92) 
 at org.eclipse.birt.report.engine.layout.html.HTMLReportLayoutEngine.layout(HTMLReportLayoutEngine.java:100) 
 at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.doRun(RunAndRenderTask.java:180) 
 at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.run(RunAndRenderTask.java:77) 
 at org.eclipse.birt.report.service.ReportEngineService.runAndRenderReport(ReportEngineService.java:929) 
 at org.eclipse.birt.report.service.BirtViewerReportService.runAndRenderReport(BirtViewerReportService.java:973) 
 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:727) 
 at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327) 
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:820) 
 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.service(ServletRegistration.java:61) 
 at org.eclipse.equinox.http.servlet.internal.ProxyServlet.processAlias(ProxyServlet.java:126) 
 at org.eclipse.equinox.http.servlet.internal.ProxyServlet.service(ProxyServlet.java:60) 
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:820) 
 at org.eclipse.equinox.http.jetty.internal.HttpServerManager$InternalHttpServiceServlet.service(HttpServerManager.java:317) 
 at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511) 
 at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:390) 
 at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182) 
 at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:765) 
 at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152) 
 at org.mortbay.jetty.Server.handle(Server.java:326) 
 at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:542) 
 at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:939) 
 at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:756) 
 at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:212) 
 at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404) 
 at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:409) 
 at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:582) 
Caused by: org.eclipse.birt.data.engine.core.DataException: Fail to execute script in function __bm_beforeOpen(). Source:

" + this.sqlText=this.sqlText.replace("999",params["p_status"].value.join("','")); + "

Find more posts tagged with

Comments

cjm13905

I found another thread with the same topic.
I deleted the beforeopen script.
 
I am trying to embed the p_status values directly into my sql
I know this is a multi select, and ultimately need
 
" status in ('a', 'b', 'c') "
 
but I am just debugging now and
am trying to see the value in the parameter p_status in the output log.
In my open script I have added
 
+ " and a.status = " + params["p_status"]
 
but in my log it expands the sql to :
 
 AND a.status = [Ljava.lang.Object;
 
which fails. I am using params["p_status"] to reference the multiselect parameter value. Should I
be using some other command ?

pricher

Hi,
 
Based on your post:
 
I then created a beforeopen script :
 
this.sqlText=this.sqlText.replace("999",params["p_status"].value.join("','"));
 
also tried
 
this.queryText=this.querylText.replace("999",params["p_status"].value.join("','"));
<div> </div>
The first statement is incorrect since sqlText is not a valid property of this. The error message gave you a hint by saying that replace could not be called by undefined.
 
The second statement would work if you did not have a typo in the property you called on the this object on the right side of the assignment. It should read queryText, not querylText. Having said that, you seem to be replacing an integer in your IN statement (999). In that case, the join method should not include the single quotes. Those are used only if you use a string in the IN statement. Therefore, the correct statement should be :
 
this.queryText=this.queryText.replace("999",params["p_status"].value.join(","));
 
Hope this helps,
 
P.

cjm13905

Thanks Pierre. Before reading your post I tried updating my sql to use
 
+ " and a.status in ('" + params["p_status"].value.join("','") + "' ) "
 
and that appears to be working in BIRT designer. My understanding is that in maximo it might not work, so that will be the next step.

cjm13905

And I get it working in Designer, but when I move to Maximo, the multi values of status are not being displayed

pricher

Hi,
 
I am not an expert with Maximo, but from what you are saying, there could be a connectivity issue when you "move to Maximo" that prevent the parameter from being populated by the query. And BTW, what do you exactly mean by "move to Maximo"? 
 
P.

cjm13905

<blockquote class="ipsBlockquote" data-author="pricher" data-cid="142946" data-time="1458592089">
<div>
Hi,
 
I am not an expert with Maximo, but from what you are saying, there could be a connectivity issue when you "move to Maximo" that prevent the parameter from being populated by the query. And BTW, what do you exactly mean by "move to Maximo"? 
 
P.
</div>
</blockquote>
I am at a University. Maximo is our Asset Management system which incorporates BIRT reporting. We create our BIRT report in Eclipse 3.7.1 and then import it into Maximo. The parameter population works fine when we run the report in the Eclipse development tool, but does not work when we import it into Maximo.

wwilliams

I think you need to find the report development guide and read it.
 
<a data-ipb='nomediaparse' href='http://www-01.ibm.com/support/docview.wss?uid=swg21605970'>http://www-01.ibm.com/support/docview.wss?uid=swg21605970</a>