Connect a Report Parameter to multiple data sets

sirim
edited February 11, 2022 in Analytics #1
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

Comments

  • mwilliams
    edited December 31, 1969 #2
    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.
    Warning No formatter is installed for the format ipb
  • sirim
    edited December 31, 1969 #3
    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
    edited December 31, 1969 #4
    What is your BIRT version? I'll create a quick example showing using the same parameter to limit multiple dataSets.
    Warning No formatter is installed for the format ipb
  • sirim
    edited December 31, 1969 #5
    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
    edited December 31, 1969 #6
    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.
    Warning No formatter is installed for the format ipb
  • sirim
    edited December 31, 1969 #7
    Thank you Michale!!

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

    Sirim
  • sirim
    edited December 31, 1969 #8
    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
    edited December 31, 1969 #9
    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.
    Warning No formatter is installed for the format ipb
  • sirim
    edited December 31, 1969 #10
    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
    edited December 31, 1969 #11
    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'.
    Warning No formatter is installed for the format ipb
  • sirim
    edited December 31, 1969 #12
    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
    edited December 31, 1969 #13
    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.
    Warning No formatter is installed for the format ipb
  • sirim
    edited December 31, 1969 #14
    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
    edited December 31, 1969 #15
    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
    edited December 31, 1969 #16
    Can you attach your design, please? Thanks!
    Warning No formatter is installed for the format ipb
  • sirim
    edited December 31, 1969 #17
    I have attached the report.

    Thank you
  • mwilliams
    edited December 31, 1969 #18
    Try this one out:
    Warning No formatter is installed for the format ipb
  • sirim
    edited December 31, 1969 #19
    It still gives the same error.

    I saw the change you made in the script...is there anything else that you changed?
  • mwilliams
    edited December 31, 1969 #20
    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.
    Warning No formatter is installed for the format ipb
  • sirim
    edited December 31, 1969 #21
    It works now!!!!!!!!!!!! :)

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

    Regards,
    Siri
  • mwilliams
    edited December 31, 1969 #22
    You're welcome!
    Warning No formatter is installed for the format ipb
  • sirim
    edited December 31, 1969 #23
    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
    edited December 31, 1969 #24
    Sorry for the delay. Can you post the new error along with your design? Thanks!
    Warning No formatter is installed for the format ipb
  • aayu_coder
    edited July 10, 2012 #25
    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
    edited December 31, 1969 #26
    It appears that you're passing an invalid value to a parameter. Can you attach your report design?
    Warning No formatter is installed for the format ipb
  • 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
    edited January 18, 2019 #28

    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

    Warning No formatter is installed for the format ipb