Pass Parameters seperated by commas to Report Using List Box Multiple Value Option

Shasha · March 25, 2013

Hi All,

I am facing some problem while making a birtReport.
I amd having almost 10 parameters in my report almost all of which are Listbox multiple value. Some are mandator while others are optional.

Now In parameter binding I amd trying to pass the values from parameter.

The Query is something Like

"..... where BANK.TERRITORY_CODE= '"+params["Country"].value+"'"+
"and RECS_BDR_PROOF.PERIOD_NAME IN ('"+params["Period"].value+"')"+
"and MESSAGE_FEED.BAL_SHEET_CATEGORY IN ('"+params["BS category"].value+"')"+
"and MESSAGE_FEED.COA_SEGMENT_1 IN ('"+params["Company Code"].value+"')"+
"and MESSAGE_FEED.COA_SEGMENT_3 IN ('"+params["Segment"].value+"')"+
"and MESSAGE_FEED.COA_SEGMENT_2 IN ('"+params["GL"].value+"')"+
" group by ........."

There are two things i want to do.
1. If parameter value is slected then only i need to add this to where clause.
2. I want to use IN in where clause so that i can passmultiple value. Right now the resule i am getting value from multiple List box is in the form "[a,b,c]"

Shasha · March 25, 2013

Actually I was trying to solve this byu using some previous post also.

if (params["BS category"].value!=null || !(params["BS category"].value)=="")
"MESSAGE_FEED.BAL_SHEET_CATEGORY IN ('"+params["BS category"].value+"') and"

if(params["Period"].value!=null || !(params["Period"].value)=="")
"RECS_BDR_PROOF.PERIOD_NAME IN ('"+params["Period"].value+"' and"

if (params["Company Code"].value!=null || !(params["Company Code"].value)=="")
"MESSAGE_FEED.COA_SEGMENT_1 IN ('"+params["Company Code"].value+"' and"

if (params["Segment"].value!=null || !(params["Segment"].value)=="")
"MESSAGE_FEED.COA_SEGMENT_3 IN ('"+params["Segment"].value+"' and"

if (params["GL"].value!=null || !(params["GL"].value)=="")
"MESSAGE_FEED.COA_SEGMENT_2 IN ('"+params["GL"].value+"'"..

But syntax error is coming//

Tubal · March 25, 2013

You could code this in javascript in the beforeOpen event of the dataset. 
 
Your query in your dataset would be something like: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT myStuff
FROM myTables
/**params**/</pre>
 
And then in the beforeOpen event, do something like: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
var myWhereStatement = "WHERE ";

if (params["BS category"].value!=null || !(params["BS category"].value)=="") {
myWhereStatement = myWhereStatement + "MESSAGE_FEED.BAL_SHEET_CATEGORY IN ('"+params["BS category"].toString("','") +"') and"
...
...
this.queryText = this.queryText.replace("/**params**/",myWhereStatement);
</pre>
 
Basically you're replacing the /**params**/ portion of your dataset query with whatever you come up with in your javascript code. 
 
You'd have to do some creative if/else stuff to decide where to put your AND statements and stuff, but you get the general idea.

Shasha · March 26, 2013

Hi Tubal Thanks for Reply. 
 
I tried this also. 
I am getting this error. 
 
The following items have errors: 
 
 
Table (id = 25): 
+ Can not load the report query: 25. Errors occurred when generating the report document for the report element with ID 25. 
Error.ReportQueryLoadingError2 ( 1 time(s) ) 
detail : org.eclipse.birt.report.engine.api.EngineException: Can not load the report query: 25. Errors occurred when generating the report document for the report element with ID 25. 
at org.eclipse.birt.report.engine.data.dte.DataPresentationEngine.doExecuteQuery(DataPresentationEngine.java:164) 
at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.execute(AbstractDataEngine.java:265) 
at org.eclipse.birt.report.engine.executor.ExecutionContext.executeQuery(ExecutionContext.java:1890) 
at org.eclipse.birt.report.engine.internal.document.v4.ReportItemExecutor.executeQuery(ReportItemExecutor.java:412) 
at org.eclipse.birt.report.engine.internal.document.v4.TableItemExecutor.doExecute(TableItemExecutor.java:72) 
at org.eclipse.birt.report.engine.internal.document.v4.ReportItemExecutor.execute(ReportItemExecutor.java:294) 
at org.eclipse.birt.report.engine.internal.document.v4.ContainerExecutor.prepareChildExecutor(ContainerExecutor.java:226) 
at org.eclipse.birt.report.engine.internal.document.v4.ContainerExecutor.hasNextChild(ContainerExecutor.java:110) 
at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.hasNextChild(WrappedReportItemExecutor.java:86) 
at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.hasNextChild(WrappedReportItemExecutor.java:86) 
at org.eclipse.birt.report.engine.api.impl.RenderTask$ReportExecutorWrapper.hasNextChild(RenderTask.java:780) 
at org.eclipse.birt.report.engine.layout.html.HTMLPageLM$1.hasNextChild(HTMLPageLM.java:68) 
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:62) 
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.RenderTask$PageRangeRender.render(RenderTask.java:661) 
at org.eclipse.birt.report.engine.api.impl.RenderTask.render(RenderTask.java:284) 
at org.eclipse.birt.report.service.ReportEngineService.renderReport(ReportEngineService.java:1545) 
at org.eclipse.birt.report.service.BirtViewerReportService.getPage(BirtViewerReportService.java:204) 
at org.eclipse.birt.report.service.actionhandler.BirtChangeParameterActionHandler.doRenderPage(BirtChangeParameterActionHandler.java:82) 
at org.eclipse.birt.report.service.actionhandler.AbstractChangeParameterActionHandler.__execute(AbstractChangeParameterActionHandler.java:103) 
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.handleChangeParameter(BirtDocumentProcessor.java:100) 
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) 
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) 
at java.lang.reflect.Method.invoke(Unknown Source) 
at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.process(AbstractBaseComponentProcessor.java:112) 
at org.eclipse.birt.report.soapengine.endpoint.BirtSoapBindingImpl.getUpdatedObjects(BirtSoapBindingImpl.java:66) 
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) 
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) 
at java.lang.reflect.Method.invoke(Unknown Source) 
at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397) 
at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:186) 
at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:323) 
at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:32) 
at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:118) 
at org.apache.axis.SimpleChain.invoke(SimpleChain.java:83) 
at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:454) 
at org.apache.axis.server.AxisServer.invoke(AxisServer.java:281) 
at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:699) 
at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.doPost(BirtSoapMessageDispatcherServlet.java:265) 
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727) 
at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327) 
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820) 
at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.service(BirtSoapMessageDispatcherServlet.java:122) 
at org.eclipse.equinox.http.registry.internal.ServletManager$ServletWrapper.service(ServletManager.java:180) 
at org.eclipse.equinox.http.servlet.internal.ServletRegistration.service(ServletRegistration.java:61) 
at org.eclipse.equinox.http.servlet.internal.ProxyServlet.processAlias(ProxyServlet.java:126) 
at org.eclipse.equinox.http.servlet.internal.ProxyServlet.service(ProxyServlet.java:60) 
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820) 
at org.eclipse.equinox.http.jetty.internal.HttpServerManager$InternalHttpServiceServlet.service(HttpServerManager.java: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: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) 
 
 
 
 
 
 
 
 
 
<blockquote class='ipsBlockquote' data-author="'Tubal'" data-cid="115379" data-time="1364224887" data-date="25 March 2013 - 08:21 AM">
You could code this in javascript in the beforeOpen event of the dataset. 
 
Your query in your dataset would be something like: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT myStuff
FROM myTables
/**params**/</pre>
 
And then in the beforeOpen event, do something like: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
var myWhereStatement = "WHERE ";

if (params["BS category"].value!=null || !(params["BS category"].value)=="") {
myWhereStatement = myWhereStatement + "MESSAGE_FEED.BAL_SHEET_CATEGORY IN ('"+params["BS category"].toString("','") +"') and"
...
...
this.queryText = this.queryText.replace("/**params**/",myWhereStatement);
</pre>
 
Basically you're replacing the /**params**/ portion of your dataset query with whatever you come up with in your javascript code. 
 
You'd have to do some creative if/else stuff to decide where to put your AND statements and stuff, but you get the general idea. </blockquote>

Shasha · March 26, 2013

Stucked up with this.. Please find the screenshot for the simplest IN query i have written

Tubal · March 26, 2013

There's a few things that could be wrong, but the easiest way to see what's happening would be to send your query to your console to see what query it's generating, and what you need to fix. 
 
If you're using Windows, start up the eclipse console version (start eclipsec.exe instead of eclipse.exe) this will open eclipse with 2 windows. One is the console, and one is the normal eclipse environment. 
 
At the end of your beforeOpen event, add this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>Packages.java.lang.System.out.println(this.queryText);</pre>
 
This will send the query text to the console window, and you'll be able to see what query it's generating, and where the error is. 
 
This isn't really pertinent and won't make a difference, but you could leave your group statement in your dataset query to keep your beforeOpen cleaner: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT myStuff
FROM myTables
/**params**/
GROUP BY myGroups
</pre>

Shasha · March 28, 2013

Thanks a Lot Tubal for Your help.. 
 
Finally I could be able to resolve the issue

seeing the console really helped

I can use multiple parameter listbox and can add values dynamically in queries.. 
 
 
 
Just 1 Issue.. How can I replace Blank value with any text of my choice?

Pass Parameters seperated by commas to Report Using List Box Multiple Value Option

Comments

Categories