Connect a Report Parameter to multiple data sets

Hello everyone,

I have four different data set in the report I am generating.

I need to add report parameters to get input from the user while running report. All the data set needs the same input information. However the way I have it set now is , it asks the user to enter the data indiviually for each data set.

I want to know if we can link a single report parameter to more than one data set, so that it would be easy and the user will not have to enter the same information again and again.

If report Parameters is not the appropriate way to do it, could someone let me know which would be the best method to follow.
I would really appriciate any kind of help on this topic.

Thank you.
Siri

Find more posts tagged with

Comments

mwilliams

Yes, you can use the same report parameter for multiple dataSets. When you create your query and set your dataSet parameter marker '?', you then just link the report parameter to the dataSet parameter in the parameters section of the dataSet editor. Hope this helps.

sirim

Thank you Michale!!

I jsut figured that out and was trying to do the same, however i do not get the desired result.

I have then all in the sequence, the '?' appear in the query. By any chance do you have any idea why they do not give me the right result?

Regards,
Sirim

mwilliams

What is your BIRT version? I'll create a quick example showing using the same parameter to limit multiple dataSets.

sirim

I am using BIRT 3.7.

I am using 4 different parameter.

1) For the end date
2) For the Start Date
3)For the end date
4) Selecting the project name.

The 1 and the 3 one are the same parameters, while the 4 one is a list box from which the user can select either a single project or more than one.
"And(g.groupdescription_title = ?)"
This is the query I am writting....My question is as this would be selecting one or more than one value from the list do you think the query should be somewhat different.

Thank you for the help

Sirim

mwilliams

Look at this sample. It was made in 3.7.0. It has 3 parameters, StartDate, EndDate, and Customers. All 3 are used in both databases. Customers is multi-select. The marker '12345' in the query is replaced by the multiple select values from the parameter in the dataSet beforeOpen script. Hope this helps.

sirim

Thank you Michale!!

I will try this and will let you know if it works.

Sirim

sirim

Hello Michael,

In your example you are selecting the list from Integers, In my query I am doing it with stirngs they are the names of the projects.

In have wirtten the query in this way
And (g.groupdescription_title IN ("abcde"))

The query runs without any errors, however does not display any result.
I understand that we do not include a parameter for this in the parameter list of the data set.

Could you help me with the other changes that I need to do?

Thank you.

Regards,
Sirim

mwilliams

Put And (g.groupdescription_title IN ('abcde')) in your query and in the join statement in the code, change it to join("','"). That should do it.

sirim

Where do we have the dataSet beforeOpen script?

I have never written this, whyt do we need it .

I thought the Join in the code you were telling about should be in this Script, Is that right?

I tried to search it on google but did not have much help.

Give me some thoughts.

Thank you

mwilliams

Yes, the join is in the beforeOpen script. If you select the dataSet in the data explorer, then click on the script tab under the design window, you should be able to select the beforeOpen script from the drop down, if it's not already selected. You need the script in the beforeOpen to create a comma separated string out of your parameter object values and to do a replace on your marker string 'abcde'.

sirim

temp = params["GroupTitle"].join(",");
this.queryText = this.queryText.replace('abcde',temp);

This the script I am writting.

There are errors evaluating script "temp = params["GroupTitle"].join(",");
this.queryText = this.queryText.replace('abcde',temp);":
Fail to execute script in function __bm_beforeOpen(). Source:

" + temp = params["GroupTitle"].join(",");
this.queryText = this.queryText.replace('abcde',temp); + "

Above is the error I am getting.

It does not give any result for the when I do a preview.

I am a lot confused about this whole scripting thing, I have never used this before.

Thank you

mwilliams

It needs to be join("','"), for a multi-select string parameter. Notice there are single quotes surrounding the comma. Can you click on the '+' next to the error and post the entire error stack? Thanks.

sirim

Thank you Michael!!

An exception occurred during processing. Please see the following message for details:
Failed to prepare the query execution for the data set: dsetStarted
Cannot set the string value (Outbound Transportation Systems) to parameter 3.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot set preparedStatement parameter string value.
SQL error #1:Parameter index out of range (3 > number of parameters, which is 2).
;
java.sql.SQLException: Parameter index out of range (3 > number of parameters, which is 2). (Element ID:15)
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:
Failed to prepare the query execution for the data set: dsetStarted
Cannot set the string value (Outbound Transportation Systems) to parameter 3.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot set preparedStatement parameter string value.
SQL error #1:Parameter index out of range (3 > number of parameters, which is 2).
;
java.sql.SQLException: Parameter index out of range (3 > number of parameters, which is 2). (Element ID:15)
at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1214)
at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1193)
at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:96)
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.GeneratedMethodAccessor102.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.GeneratedMethodAccessor33.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.report.data.adapter.api.AdapterException: An exception occurred during processing. Please see the following message for details:
Failed to prepare the query execution for the data set: dsetStarted
Cannot set the string value (Outbound Transportation Systems) to parameter 3.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot set preparedStatement parameter string value.
SQL error #1:Parameter index out of range (3 > number of parameters, which is 2).
;
java.sql.SQLException: Parameter index out of range (3 > number of parameters, which is 2).
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.execute(DataRequestSessionImpl.java:640)
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)
... 57 more
Caused by: org.eclipse.birt.data.engine.core.DataException: Failed to prepare the query execution for the data set: dsetStarted
Cannot set the string value (Outbound Transportation Systems) to parameter 3.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot set preparedStatement parameter string value.
SQL error #1:Parameter index out of range (3 > number of parameters, which is 2).
;
java.sql.SQLException: Parameter index out of range (3 > number of parameters, which is 2).
at org.eclipse.birt.data.engine.impl.QueryExecutor.prepareExecution(QueryExecutor.java:345)
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.data.engine.impl.PreparedOdaDSQuery.execute(PreparedOdaDSQuery.java:145)
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.execute(DataRequestSessionImpl.java:620)
... 61 more
Caused by: org.eclipse.birt.data.engine.odaconsumer.OdaDataException: Cannot set the string value (Outbound Transportation Systems) to parameter 3.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot set preparedStatement parameter string value.
SQL error #1:Parameter index out of range (3 > number of parameters, which is 2).
;
java.sql.SQLException: Parameter index out of range (3 > number of parameters, which is 2).
at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.newException(ExceptionHandler.java:54)
at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:108)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.doSetString(PreparedStatement.java:4315)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.setString(PreparedStatement.java:3919)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.setParameterValue(PreparedStatement.java:3134)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.setParameterValue(PreparedStatement.java:3070)
at org.eclipse.birt.data.engine.executor.DataSourceQuery.setInputParameterBinding(DataSourceQuery.java:1124)
at org.eclipse.birt.data.engine.executor.DataSourceQuery.addParameterDefns(DataSourceQuery.java:572)
at org.eclipse.birt.data.engine.executor.DataSourceQuery.prepare(DataSourceQuery.java:290)
at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.prepareOdiQuery(PreparedOdaDSQuery.java:455)
at org.eclipse.birt.data.engine.impl.QueryExecutor.prepareExecution(QueryExecutor.java:341)
... 66 more
Caused by: org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot set preparedStatement parameter string value.
SQL error #1:Parameter index out of range (3 > number of parameters, which is 2).
;
java.sql.SQLException: Parameter index out of range (3 > number of parameters, which is 2).
at org.eclipse.birt.report.data.oda.jdbc.Statement.setString(Statement.java:654)
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.setString(OdaQuery.java:738)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.doSetString(PreparedStatement.java:4310)
... 74 more
Caused by: java.sql.SQLException: Parameter index out of range (3 > number of parameters, which is 2).
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3729)
at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3713)
at com.mysql.jdbc.PreparedStatement.setString(PreparedStatement.java:4553)
at org.eclipse.birt.report.data.oda.jdbc.Statement.setString(Statement.java:649)
... 76 more

This is the complete error I am getting

sirim

Michael,

I have reduced to one error now, I have added the complete error in this post

The following items have errors:

ReportDesign (id = 1):
- There are errors evaluating script "temp = params["ProjectTitle"].join("","");
this.queryText = this.queryText.replace("abcde",temp);":
TypeError: Cannot call method "replace" of undefined (/report/method[@name="initialize"]#2) (Element ID:1)
birt.core.JavascriptCommonError ( 1 time(s) )
detail : org.eclipse.birt.report.engine.api.EngineException: There are errors evaluating script "temp = params["ProjectTitle"].join("","");
this.queryText = this.queryText.replace("abcde",temp);":
TypeError: Cannot call method "replace" of undefined (/report/method[@name="initialize"]#2) (Element ID:1)
at org.eclipse.birt.report.engine.script.internal.ScriptExecutor.addException(ScriptExecutor.java:244)
at org.eclipse.birt.report.engine.script.internal.ReportScriptExecutor.handleInitialize(ReportScriptExecutor.java:81)
at org.eclipse.birt.report.engine.api.impl.EngineTask.loadDesign(EngineTask.java:1774)
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.doRun(RunAndRenderTask.java:98)
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.GeneratedMethodAccessor102.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.GeneratedMethodAccessor33.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.core.exception.CoreException: There are errors evaluating script "temp = params["ProjectTitle"].join("","");
this.queryText = this.queryText.replace("abcde",temp);":
TypeError: Cannot call method "replace" of undefined (/report/method[@name="initialize"]#2)
at org.eclipse.birt.report.engine.javascript.JavascriptEngine.evaluate(JavascriptEngine.java:295)
at org.eclipse.birt.core.script.ScriptContext.evaluate(ScriptContext.java:154)
at org.eclipse.birt.report.engine.executor.ExecutionContext.evaluate(ExecutionContext.java:780)
at org.eclipse.birt.report.engine.executor.ExecutionContext.evaluate(ExecutionContext.java:709)
at org.eclipse.birt.report.engine.script.internal.ScriptExecutor.handleScriptInternal(ScriptExecutor.java:117)
at org.eclipse.birt.report.engine.script.internal.ScriptExecutor.handleScript(ScriptExecutor.java:104)
at org.eclipse.birt.report.engine.script.internal.ReportScriptExecutor.handleInitialize(ReportScriptExecutor.java:72)
... 51 more
Caused by: org.mozilla.javascript.EcmaError: TypeError: Cannot call method "replace" of undefined (/report/method[@name="initialize"]#2)
at org.mozilla.javascript.ScriptRuntime.constructError(ScriptRuntime.java:3654)
at org.mozilla.javascript.ScriptRuntime.constructError(ScriptRuntime.java:3632)
at org.mozilla.javascript.ScriptRuntime.typeError(ScriptRuntime.java:3660)
at org.mozilla.javascript.ScriptRuntime.typeError2(ScriptRuntime.java:3679)
at org.mozilla.javascript.ScriptRuntime.undefCallError(ScriptRuntime.java:3698)
at org.mozilla.javascript.ScriptRuntime.getPropFunctionAndThisHelper(ScriptRuntime.java:2221)
at org.mozilla.javascript.ScriptRuntime.getPropFunctionAndThis(ScriptRuntime.java:2214)
at org.mozilla.javascript.gen.c3327._c0(/report/method[@name="initialize"]:2)
at org.mozilla.javascript.gen.c3327.call(/report/method[@name="initialize"])
at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:398)
at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3065)
at org.mozilla.javascript.gen.c3327.call(/report/method[@name="initialize"])
at org.mozilla.javascript.gen.c3327.exec(/report/method[@name="initialize"])
at org.eclipse.birt.report.engine.javascript.JavascriptEngine.evaluate(JavascriptEngine.java:290)
... 57 more

Thank you for all the help

mwilliams

Can you attach your design, please? Thanks!

sirim

I have attached the report.

Thank you

mwilliams

Try this one out:

sirim

It still gives the same error.

I saw the change you made in the script...is there anything else that you changed?

mwilliams

That was the only change I made. I didn't figure it would fix it, but I thought it was worth a shot and the only "error" I saw in the script. I found the issue, now, though. I didn't see that you had the same script in your "initialize" script. There is no this.queryText in initialize, so, that is the "replace" that is causing issues. I removed it. Now try it.

sirim

It works now!!!!!!!!!!!!

Thank you so much for all your help and time Michael....I really appreiciate it...

Regards,
Siri

mwilliams

You're welcome!

sirim

Hello Michael,

I am not sure if you would be able to help me with this. I am trying to have the same kind of report with the script in beforeOpen(). It gives me the similar error.

I would like to know where exaclty in the initialize in the viewer, so that I can check and eliminate the error.

any help would be useful.

Regards,
Sirim

mwilliams

Sorry for the delay. Can you post the new error along with your design? Thanks!

aayu_coder

Hi,

I am facing the same problem and i am not getting how to solve it very new with Birt and eclipse and get confuse for to how to add parameters. it would be good if you can provide me some videos or PDF to understand better. I have add JDBC also still i am getting error

please help me

Thanks in Advance

The error I am getting is

org.eclipse.birt.report.engine.api.EngineException: Error happened while running the report.
at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.doRun(DatasetPreviewTask.java:135)
at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.runDataset(DatasetPreviewTask.java:97)
at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.execute(DatasetPreviewTask.java:49)
at org.eclipse.birt.report.designer.data.ui.dataset.DataSetPreviewer.preview(DataSetPreviewer.java:69)
at org.eclipse.birt.report.designer.data.ui.dataset.ResultSetPreviewPage$5.run(ResultSetPreviewPage.java:337)
at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:121)
Caused by: org.eclipse.birt.report.data.adapter.api.AdapterException: An exception occurred during processing. Please see the following message for details:
Failed to prepare the query execution for the data set: Data Set
Cannot set the string value (null) to parameter 1.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot set preparedStatement parameter string value.
SQL error #1:Invalid parameter index 1.
;
java.sql.SQLException: Invalid parameter index 1.
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.execute(DataRequestSessionImpl.java:644)
at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.extractQuery(DatasetPreviewTask.java:166)
at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.doRun(DatasetPreviewTask.java:126)
... 5 more
Caused by: org.eclipse.birt.data.engine.core.DataException: Failed to prepare the query execution for the data set: Data Set
Cannot set the string value (null) to parameter 1.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot set preparedStatement parameter string value.
SQL error #1:Invalid parameter index 1.
;
java.sql.SQLException: Invalid parameter index 1.
at org.eclipse.birt.data.engine.impl.QueryExecutor.prepareExecution(QueryExecutor.java:345)
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.data.engine.impl.PreparedOdaDSQuery.execute(PreparedOdaDSQuery.java:145)
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.execute(DataRequestSessionImpl.java:624)
... 7 more
Caused by: org.eclipse.birt.data.engine.odaconsumer.OdaDataException: Cannot set the string value (null) to parameter 1.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot set preparedStatement parameter string value.
SQL error #1:Invalid parameter index 1.
;
java.sql.SQLException: Invalid parameter index 1.
at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.newException(ExceptionHandler.java:54)
at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:108)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.doSetString(PreparedStatement.java:4315)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.setString(PreparedStatement.java:3919)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.retrySetNullParamValue(PreparedStatement.java:3738)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.retrySetParameterValue(PreparedStatement.java:3245)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.setParameterValue(PreparedStatement.java:3188)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.setParameterValue(PreparedStatement.java:3070)
at org.eclipse.birt.data.engine.executor.DataSourceQuery.setInputParameterBinding(DataSourceQuery.java:1124)
at org.eclipse.birt.data.engine.executor.DataSourceQuery.addParameterDefns(DataSourceQuery.java:572)
at org.eclipse.birt.data.engine.executor.DataSourceQuery.prepare(DataSourceQuery.java:290)
at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.prepareOdiQuery(PreparedOdaDSQuery.java:442)
at org.eclipse.birt.data.engine.impl.QueryExecutor.prepareExecution(QueryExecutor.java:341)
... 12 more
Caused by: org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot set preparedStatement parameter string value.
SQL error #1:Invalid parameter index 1.
;
java.sql.SQLException: Invalid parameter index 1.
at org.eclipse.birt.report.data.oda.jdbc.Statement.setString(Statement.java:654)
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.setString(OdaQuery.java:738)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.doSetString(PreparedStatement.java:4310)
... 22 more
Caused by: java.sql.SQLException: Invalid parameter index 1.
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.getParameter(JtdsPreparedStatement.java:341)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.setParameter(JtdsPreparedStatement.java:410)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.setString(JtdsPreparedStatement.java:690)
at org.eclipse.birt.report.data.oda.jdbc.Statement.setString(Statement.java:649)
... 24 more

mwilliams

It appears that you're passing an invalid value to a parameter. Can you attach your report design?

ccoutsouve

Hello Michael

I am having the same issue as sirim.
Can you send me the example you gave sirim so i can compare to my issue?
Thank You

jfranken

Changes to the forum have caused attachments from older posts to no longer be accessible. Attached is a new example I created. There is code in the beforeOpen event of the Orders and Payments data sets to add where clauses to the respective queries. For multiselect parameters like the customer parameter, the parameter value is a Java object and must be converted to a JavaScript string in code. Note that this example is untested and is provided for demonstration purposes only.

To test the report, good settings for the parameters:are:
Start Date = Jun 1, 2012
End Date = Jan 1, 2014
Customer Number = 119 and 131