How to implement in SpagoBI Server a BIRT report parameter that allows multiple values [SOLVED]

This message was edited 3 times. Last update was at 09/08/2012 10:58:56

Hello,

I have created a BIRT report in SpagoBI Studio. The report has a parameter that allows multiple values and works ok in SpagoBI Studio preview.

When I try to create the corresponding parameter in SpagoBI Server (I have created the LOV, the Analytical Driver and added the Analytical Driver to my Report Document) and try to run the report I get the following exception:

25 Jul 2012 15:59:32,735 ERROR it.eng.spagobi.engines.birt.BirtReportServlet.runReport:692 - Error while running the report: org.eclipse.birt.report.engine.api.impl.ParameterValidationException: The type of parameter "projectId" is expected as "Object[]", not "java.lang.String".

And my question is: How do we define parameters that allow multiple values in SpagoBI studio? What should the type of that Analytical Driver be?

Thank you very much in advance

(I m on SpagoBI 3.5)

This message was edited 1 time. Last update was at 26/07/2012 09:15:12

Hi Anna,
in SpagoBI a multivalue parameter is coded in a String where the separator of the values is the ;. So for example the string “1;2;3;4;” is the representation of a parameter that has the 4 values 1,2,3,4.
So if you create a parameter of type “checklist” the value of the associated parameter will have that format.

Now suppose the URL of your multivalue parameter in SpagoBI is MULTIVALUE_P (with value 1;2;3;4; ), in BIRT you should create a parameter of type String. In BIRT the value of the parameter will be parsed an the ; will be replaced with a , so the value will be “1,2,3,4”. In this way you can add the parameter in an in clause of a query.
For the cross navigation of multivalue parameters take a look here https://wiki.spagobi.org/xwiki/bin/view/spagobi_server/Cross+Navigation#HPropagatingmulti-valueparameters

Hi Alberto,

I have created my parameter in BIRT as String and checked the 'Allow Multiple Values' checkbox.
The corresponding Analytical Driver is also of type String but I still get the error.

You don't have tho marjk the parameter as 'Allow Multiple Values' in BIRT..
It's a simple String

But If I dont mark it as 'Allow Multiple Values' then it only returns one row.

I am using the filter desribed below on the Data Set:

row["projectId"] in params["projectId"].value

This message was edited 3 times. Last update was at 26/07/2012 17:28:22

If you keep the parameters as normal String you can write the query in this way:

Select …
From …
Where …. And _PROJECT_ID_

Now click on the data set, and open the tab Script.
In the main panel select Script: beforeOpen in the combo box and write this code

this.queryText = this.queryText.replace(_PROJECT_ID_, “ projectId in ( “+params["projectId"].value+” ) ”);

In this way you are substituting the placeholder _PROJECT_ID_ in the query with the string “ projectId in ( “+params["projectId"].value+” ) ”

In this way the query to execute will be something like

Select …
From …
Where …. And projectId in ( [values in the parameter projectId] )

This message was edited 1 time. Last update was at 30/07/2012 11:03:09

Hi Alberto,

I was able to create some signle value filters without the beforeOpen script.
What I did was create the parameter in the Birt Designer as String and not multiselect as you said, then create the Analytical Driver as well as String and use the Combo Box option.

Thank you very much for your help.

Hi Alberto,

I am still struggling with the multi-value parameters.

I am trying to populate a checklist-defined analytical driver (simple string parameter in birt as you suggested) from withing my application using the following code:

but I am getting the following exception:

[http-8080-7] 02 Aug 2012 14:48:11,819 ERROR it.eng.spagobi.sdk.documents.impl.DocumentsServiceImpl.executeDocument:667 - error while retrieving parameters errors
it.eng.spago.error.EMFInternalError: severity [ERROR] description [SelectCommand :: execute() ::
java.sql.SQLException: Line 1: Incorrect syntax near ';'.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:62
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:41
at net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:125
at it.eng.spago.dbaccess.sql.command.std.SelectCommand.execute(SelectCommand.java:93)
at it.eng.spagobi.behaviouralmodel.lov.bo.QueryDetail.validateValues(QueryDetail.java:627)
at it.eng.spagobi.analiticalmodel.document.handlers.ExecutionInstance.getValidationErrorsOnValuesForQueries(ExecutionInstance.java:77
at it.eng.spagobi.analiticalmodel.document.handlers.ExecutionInstance.getValidationErrorsOnValues(ExecutionInstance.java:760)
at it.eng.spagobi.analiticalmodel.document.handlers.ExecutionInstance.getParametersErrors(ExecutionInstance.java:610)
at it.eng.spagobi.sdk.documents.impl.DocumentsServiceImpl.executeDocument(DocumentsServiceImpl.java:665)
at it.eng.spagobi.sdk.documents.stub.DocumentsServiceSoapBindingImpl.executeDocument(DocumentsServiceSoapBindingImpl.java:64)
at it.eng.spagobi.sdk.documents.stub.DocumentsServiceSoapBindingSkeleton.executeDocument(DocumentsServiceSoapBindingSkeleton.java:365)
at sun.reflect.GeneratedMethodAccessor882.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
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:11
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 javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:12
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Thread.java:662)
]

Is the problem the way I am setting the multi-value parameter values or the analytical driver definition?

(I have created another topic under sdk here https://www.spagoworld.org/jforum/posts/list/0/1600.page#5486 because this might be more relevant there)

This message was edited 3 times. Last update was at 09/08/2012 10:59:48

Just putting it down here in case somebody encounters the same problem.
My exception was caused by the ';' I was putting at the end of my LOV query definition.
More details here: https://www.spagoworld.org/jforum/posts/list/0/1600.page#5486

And now Alberto's suggestion above works : )

Hello there,

I'm using SpagoBI 4.0 with Java 1.6 on Windows Server 2008 R2.

I also try to use a multivalue parameter, but somehow it's not possible to use the analytical driver type CheckList in SpagoBI 4.0.
I remember it was in 3.6 and it's also documented in the SpagoBI 3.6 book, but now all the different types do not allow to choose multiple values.
E.g. in BIRT I add a string parameter where I'd like to pass multiple IDs to the query, like "WHERE campaign_id IN (123, 456)".
When I open the report in SpagoBI, I cannot choose multiple values from the LOV that shows me the campaigns with their IDs.

Aghedin, your logic is correct and I try to follow it, but cannot achieve it in SpagoBI 4.0. Any idea how to achieve this in SpagoBI 4.0?

Thanks!

I just saw that in the document development of the report, I can set up a paramater to be Multivalue.
Now when I run the report, I can pick multiple values from the list, which is fine.

After executing the document with the parameters, the report loads but returns only the template without any data, the parameters in the reports are shown as "null".

The paramater in BIRT is a simple string parameter and I was expecting this parameter in SpagoBI, e.g. containing "123;456;" to be replaced with "123,456" to use it in an IN clause.
But it doesn't seem to work.
The LOV query doesn't end with ";" to clarify.

Any ideas how to do this in SpagoBI 4.0?

erhard.spw wrote:I just saw that in the document development of the report, I can set up a paramater to be Multivalue.
Now when I run the report, I can pick multiple values from the list, which is fine.

After executing the document with the parameters, the report loads but returns only the template without any data, the parameters in the reports are shown as "null".

The paramater in BIRT is a simple string parameter and I was expecting this parameter in SpagoBI, e.g. containing "123;456;" to be replaced with "123,456" to use it in an IN clause.
But it doesn't seem to work.
The LOV query doesn't end with ";" to clarify.

Any ideas how to do this in SpagoBI 4.0?

Alright, I solved the empty report issue by fixing the analytical driver. The parameter was set to string, changing to integer fixed it.

But one problem still remains: Let's say the parameter has two values, 123, 456. I choose them in the list before report execution.
I added a data field in the report to display the parameter, it shows me: 123,456. Obviously the parameters were passed correctly.
In the report itself I only see values for one ID (I have a table which is grouped by the ID).

The query in BIRT is as follows:
select ...
from ...
where ID IN (?)

Is this correct?

Since the string is '123,456', I assume the ? is replaced by the string and the query would be: select ... where ID IN ('123','456').
I assume something is not correct in BIRT. The parameter is set as Text Box, type String.

Did somebody implement this succesfully?

erhard.spw wrote:Hello there,

I'm using SpagoBI 4.0 with Java 1.6 on Windows Server 2008 R2.

I also try to use a multivalue parameter, but somehow it's not possible to use the analytical driver type CheckList in SpagoBI 4.0.
I remember it was in 3.6 and it's also documented in the SpagoBI 3.6 book, but now all the different types do not allow to choose multiple values.
E.g. in BIRT I add a string parameter where I'd like to pass multiple IDs to the query, like "WHERE campaign_id IN (123, 456)".
When I open the report in SpagoBI, I cannot choose multiple values from the LOV that shows me the campaigns with their IDs.

Aghedin, your logic is correct and I try to follow it, but cannot achieve it in SpagoBI 4.0. Any idea how to achieve this in SpagoBI 4.0?

Thanks!

Hello, I have the same problem in SpagoBI 4.0.
Have you got a solution to this problem ?

Thank's for your help.

Hi,

Does anyone have a simple solution for this already ?
I'm also stuck at this point.

Thanks,
Mike

i did what aghedin proposed:

If you keep the parameters as normal String you can write the query in this way:

Select …
From …
Where …. And _PROJECT_ID_

Now click on the data set, and open the tab Script.
In the main panel select Script: beforeOpen in the combo box and write this code

this.queryText = this.queryText.replace(_PROJECT_ID_, “ projectId in ( “+params["projectId"].value+” ) ”);

In this way you are substituting the placeholder _PROJECT_ID_ in the query with the string “ projectId in ( “+params["projectId"].value+” ) ”

In this way the query to execute will be something like

Select …
From …
Where …. And projectId in ( [values in the parameter projectId] )