[SBI BIRT] Paramètres à valeur multiple [Résolu]

[zakato]

Bonjour,
je dispose d'un report BIRT avec le dataset suivant:

Code :

1
2
3
4
5
6
7
SELECT ANNEE,
         MOIS,
         MOYENNE,
         PROFONDEUR,
         SITE
FROM moyenne
WHERE annee = ?

Et cela marche nickel, je l'ai loadé dans BIRT j'ai créer une lov par requête (select distinct ANNEE from moyenne)
puis un paramètre sur cette lov et ca marche nickel

Maintenant, je veux avoir la possibilité de sélectionner plusieurs années,
j'ai essayé de modifier mon dataset dans BIRT de deux façons :

Code :

1
2
3
4
5
6
7
8
SELECT ANNEE,
       MOIS,
       MOYENNE,
       PROFONDEUR,
       SITE
FROM moyenne
 
WHERE annee IN ('"+params["annee"].value.replace(/,/g,"','")+"')

mais avec ça je suis obligé de supprimer le paramètre du dataset qui était lié à celui du report et lorsque j'essaye la preview ou le viewer, ma cross tab dans laquelle j'affiche mes valeurs est vide.

Ensuite j'ai essayé un dataset plutôt comme ça :

Code :

1
2
3
4
5
6
7
8
SELECT ANNEE,
       MOIS,
       MOYENNE,
       PROFONDEUR,
       SITE
FROM moyenne
 
WHERE annee IN (?)

mais là je suis obligé de sélectionner un paramètre à valeur non multiple
et lors de la preview j'obtiens l'erreur :

Error happens when create hierarchy:time.NewTabularHierarchy.
Failed to prepare the query execution for the data set: moyennes
Cannot convert the parameter value [Ljava.lang.Object;@1e37ee4 to type class java.lang.Integer.
Can not convert the value of [Ljava.lang.Object;@1e37ee4 to Integer type.
Failed to prepare the query execution for the data set: moyennes
Cannot convert the parameter value [Ljava.lang.Object;@1e37ee4 to type class java.lang.Integer.
Can not convert the value of [Ljava.lang.Object;@1e37ee4 to Integer type.
exception.error ( 1 time(s) )
detail : org.eclipse.birt.report.engine.api.EngineException: An exception occurred during processing. Please see the following message for details:
exception.error
Error happens when create hierarchy:time.NewTabularHierarchy.
Failed to prepare the query execution for the data set: moyennes
Cannot convert the parameter value [Ljava.lang.Object;@1e37ee4 to type class java.lang.Integer.
Can not convert the value of [Ljava.lang.Object;@1e37ee4 to Integer type.
Failed to prepare the query execution for the data set: moyennes
Cannot convert the parameter value [Ljava.lang.Object;@1e37ee4 to type class java.lang.Integer.
Can not convert the value of [Ljava.lang.Object;@1e37ee4 to Integer type.
at org.eclipse.birt.report.engine.executor.ExecutorManager$ExecutorContext.executeQuery(ExecutorManager.java:449)
at org.eclipse.birt.report.item.crosstab.core.re.executor.BaseCrosstabExecutor.executeQuery(BaseCrosstabExecutor.java:114)
at org.eclipse.birt.report.item.crosstab.core.re.executor.CrosstabReportItemExecutor.execute(CrosstabReportItemExecutor.java:102)
at org.eclipse.birt.report.engine.executor.ExtendedItemExecutor.execute(ExtendedItemExecutor.java: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:90)
at org.eclipse.birt.report.engine.layout.html.HTMLReportLayoutEngine.layout(HTMLReportLayoutEngine.java:99)
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.doRun(RunAndRenderTask.java:172)
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.run(RunAndRenderTask.java:75)
at org.eclipse.birt.report.service.ReportEngineService.runAndRenderReport(ReportEngineService.java:920)
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.GeneratedMethodAccessor42.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.GeneratedMethodAccessor39.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:318)
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:322)
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:
exception.error
Error happens when create hierarchy:time.NewTabularHierarchy.
Failed to prepare the query execution for the data set: moyennes
Cannot convert the parameter value [Ljava.lang.Object;@1e37ee4 to type class java.lang.Integer.
Can not convert the value of [Ljava.lang.Object;@1e37ee4 to Integer type.
Failed to prepare the query execution for the data set: moyennes
Cannot convert the parameter value [Ljava.lang.Object;@1e37ee4 to type class java.lang.Integer.
Can not convert the value of [Ljava.lang.Object;@1e37ee4 to Integer type.
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.execute(DataRequestSessionImpl.java:623)
at org.eclipse.birt.report.engine.data.dte.DteDataEngine.doExecuteCube(DteDataEngine.java:222)
at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.execute(AbstractDataEngine.java:270)
at org.eclipse.birt.report.engine.executor.ExecutorManager$ExecutorContext.executeQuery(ExecutorManager.java:439)
... 59 more
Caused by: org.eclipse.birt.data.engine.core.DataException: exception.error
Error happens when create hierarchy:time.NewTabularHierarchy.
Failed to prepare the query execution for the data set: moyennes
Cannot convert the parameter value [Ljava.lang.Object;@1e37ee4 to type class java.lang.Integer.
Can not convert the value of [Ljava.lang.Object;@1e37ee4 to Integer type.
Failed to prepare the query execution for the data set: moyennes
Cannot convert the parameter value [Ljava.lang.Object;@1e37ee4 to type class java.lang.Integer.
Can not convert the value of [Ljava.lang.Object;@1e37ee4 to Integer type.
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.materializeCube(DataRequestSessionImpl.java:736)
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.execute(DataRequestSessionImpl.java:612)
... 62 more
Caused by: org.eclipse.birt.report.data.adapter.api.AdapterException: Error happens when create hierarchy:time.NewTabularHierarchy.
Failed to prepare the query execution for the data set: moyennes
Cannot convert the parameter value [Ljava.lang.Object;@1e37ee4 to type class java.lang.Integer.
Can not convert the value of [Ljava.lang.Object;@1e37ee4 to Integer type.
Failed to prepare the query execution for the data set: moyennes
Cannot convert the parameter value [Ljava.lang.Object;@1e37ee4 to type class java.lang.Integer.
Can not convert the value of [Ljava.lang.Object;@1e37ee4 to Integer type.
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.populateDimension(DataRequestSessionImpl.java:1305)
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.populateDimensions(DataRequestSessionImpl.java:1153)
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.createCube(DataRequestSessionImpl.java:784)
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.materializeCube(DataRequestSessionImpl.java:721)
... 63 more
Caused by: org.eclipse.birt.report.data.adapter.api.AdapterException: Failed to prepare the query execution for the data set: moyennes
Cannot convert the parameter value [Ljava.lang.Object;@1e37ee4 to type class java.lang.Integer.
Can not convert the value of [Ljava.lang.Object;@1e37ee4 to Integer type.
Failed to prepare the query execution for the data set: moyennes
Cannot convert the parameter value [Ljava.lang.Object;@1e37ee4 to type class java.lang.Integer.
Can not convert the value of [Ljava.lang.Object;@1e37ee4 to Integer type.
at org.eclipse.birt.report.data.adapter.impl.DataSetIterator.executeQuery(DataSetIterator.java:85)
at org.eclipse.birt.report.data.adapter.impl.DataSetIterator.<init>(DataSetIterator.java:109)
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.populateDimension(DataRequestSessionImpl.java:1278)
... 66 more
Caused by: org.eclipse.birt.data.engine.core.DataException: Failed to prepare the query execution for the data set: moyennes
Cannot convert the parameter value [Ljava.lang.Object;@1e37ee4 to type class java.lang.Integer.
Can not convert the value of [Ljava.lang.Object;@1e37ee4 to Integer type.
at org.eclipse.birt.data.engine.impl.QueryExecutor.prepareExecution(QueryExecutor.java:343)
at org.eclipse.birt.data.engine.impl.PreparedQuery.doPrepare(PreparedQuery.java:448)
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.data.engine.impl.PreparedDataSourceQuery.execute(PreparedDataSourceQuery.java:159)
at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.execute(PreparedDataSourceQuery.java:142)
at org.eclipse.birt.report.data.adapter.impl.DataSetIterator.executeQuery(DataSetIterator.java:81)
... 68 more
Caused by: org.eclipse.birt.data.engine.core.DataException: Cannot convert the parameter value [Ljava.lang.Object;@1e37ee4 to type class java.lang.Integer.
Can not convert the value of [Ljava.lang.Object;@1e37ee4 to Integer type.
at org.eclipse.birt.data.engine.executor.DataSourceQuery.convertToValue(DataSourceQuery.java:1079)
at org.eclipse.birt.data.engine.executor.DataSourceQuery.getParameterInputValue(DataSourceQuery.java:548)
at org.eclipse.birt.data.engine.executor.DataSourceQuery.addParametersToQuerySpec(DataSourceQuery.java:489)
at org.eclipse.birt.data.engine.executor.DataSourceQuery.populateQuerySpecification(DataSourceQuery.java:423)
at org.eclipse.birt.data.engine.executor.DataSourceQuery.prepare(DataSourceQuery.java:272)
at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.prepareOdiQuery(PreparedOdaDSQuery.java:441)
at org.eclipse.birt.data.engine.impl.QueryExecutor.prepareExecution(QueryExecutor.java:339)
... 75 more
Caused by: org.eclipse.birt.core.exception.CoreException: Can not convert the value of [Ljava.lang.Object;@1e37ee4 to Integer type.
at org.eclipse.birt.core.data.DataTypeUtil.toInteger(DataTypeUtil.java:281)
at org.eclipse.birt.core.data.DataTypeUtil.convert(DataTypeUtil.java:154)
at org.eclipse.birt.data.engine.executor.DataSourceQuery.convertToValue(DataSourceQuery.java:1075)
... 81 more

quelqu'un a-t-il une piste ?

[zakato]

Bon j'ai finalement réussi à faire fonctionner les paramètres multiples des rapport Birt sous SpagoBI :

alors pour commencer ma requête se limite au select...from, je n'ajoute pas de clause where par contre je fini ma requete par un espace

ensuite je crée un paramètre de Rapport ici "annee"

et enfin dans le script du dataset concerné pour la fonction beforeOpen()

je balance :

Code :

1
2
var params = reportContext.getParameterValue("annee");
this.queryText+= "where moyenne.ANNEE IN ("+params+")";

Pour ceux qui sont bloqués avec des paramètres voici l'astuce avec laquelle je me suis débloqué :

créer un rapport vide avec un paramètre param et ensuite ajouter un champ data dont l'expression est
reportContext.getParameterValue("param")

et lancer le rapport dans l'environnement qui bloque (Birt ou Spago)
et ainsi vous verrez affichée la valeur que birt reçoit comme paramètre.
Vous pourrez ainsi en apprecier le format et savoir s'il faut le modifier ou pas pour l'inclure dans votre clause where par le script.

Voila j'espère que ces astuces en aideront certains.

;-)