nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Report Parameter - Multiple String Values in where clause

holger

<p>Hey Guys,</p><p> </p><p>i know it sounds strange but after one working day googling, reading forums, reading the birt field guide i am closer to my solution but couldn't find my mistake (and the report is still not running)</p><p> </p><p>It seams for me as a newbie really horrible that there is no way other than scripting to solve a pretty easy problem.</p><p> </p><p>Imagine following sql:</p><p> </p><p>select *</p><p>from table1</p><p>where name IN('name1','name2','name3')</p><p> </p><p>I set up the report parameter rp_name (a dynamic list box with multiple string values allowed) and want to give this parameter into the where clause.</p><p> </p><p>The solution should be here, but after trying hours it still doesn't work (at least in the preview i get always empty results)</p><p><a data-ipb='nomediaparse' href='http://www.birt-exchange.org/forum/index.php?/topic/23286-how-to-pass-multiple-values-to-a-single-report-parameter/page-2'>http://www.birt-exchange.org/forum/index.php?/topic/23286-how-to-pass-multiple-values-to-a-single-report-parameter/page-2</a></p><p> </p><p>my query in the end will be nested so i would need the where statement in many many subqueries to filter out special combinations</p><p> </p><p>Is there somebody he can send me please a rptdesign in which i can check what i did wrong?!</p><p> </p><p>Or how can i check the query which is builded and used to see what birt is doing?!</p><p> </p><p>For any help I'm really thankful and will offer a free beer for the solution once the solver is in berlin.</p><p> </p><p>cheers and thanks</p><p> </p><p>holger</p><p> </p>

Find more posts tagged with

Comments

kclark

<p>What version of BIRT are you using? I'll build an example for you. I think what you could do is create a new String from the beforeOpen() of your data set to hold the value of your parameter. Then create an ArrayList and then split() the string on a comma and loop through the values to be formatted like "Selection 1", "Selection 2" etc.</p><p> </p><p>Then you could edit the query doing something like this</p><pre class="_prettyXprint">this.queryText = "select * from someTable where someField in(" + newParameterFormat + ")";</pre>

holger

<p>Hey kclark,</p><p> </p><p>thanks for the answer..</p><p> </p><p>I use BIRT 4.3.0..</p><p> </p><p>If you could create a working rptdesign for me it would be such help coz i tried already everything with scripting in before open...but.....nothing</p><p> </p><p>But apparently it couldnt be that hard..</p>

kclark

<p>I just came across <a data-ipb='nomediaparse' href='http://www.birt-exchange.org/devshare/_/designing-birt-reports/771-using-a-multivalue-parameter-in-a-in-clause'>this devshare</a>, it's similar to what I was describing but it looks to be easier. Let me know if this helps!</p>

holger

<p>You are freaking genious...its finally working like a charme...</p><p> </p><p>come to berlin and get your free beer:)</p>

holger

<p>New Problem:)</p><p> </p><p>I want to have a bunch of list boxes to fill multiple where clause...check example...</p><p> </p><p>I always get an error...is BIRT able to solve this problem...or kclark or somebody??</p><p> </p><p> </p><p>Query Example:</p><p> </p><div>SELECT <span> </span>table1.column1,</div><div>table2.column2,</div><div>SUM(CASE WHEN table3.column3 IS NULL THEN 1 ELSE 0 END)</div><div> </div><div> </div><div>FROM table1</div><div> </div><div>LEFT JOIN table2 ON(..)</div><div> </div><div>LEFT JOIN (select column1 from table3 group by column_x) ec ON(...)</div><div> </div><div>LEFT JOIN table4 ON(..)</div><div> </div><div> </div><div>WHERE table1.date between 'ddd' and 'eee' and table4.column1 IN('****') and table2.column2 IN('yyy') </div><div> </div><div>GROUP BY table1.column1</div><div> </div><div> </div><div> </div><div>I do in the beforeopen section the following:</div><div> </div><div><div>this.queryText = this.queryText.replace("ddd", params["rp_1].value.join("','" )); --which is a date</div><div>this.queryText = this.queryText.replace("eee", params["rp_2"].value.join("','" )); --which is a date</div><div><div>this.queryText = this.queryText.replace("****", params["rp_3"].value.join("','" )); --which is a string</div><div>this.queryText = this.queryText.replace("yyy", params["rp_4"].value.join("','" )); --which is a string</div><div> </div><div>Any suggestion?

</div></div></div>

holger

<p>Nobody with ideas?? Is birt really that complicate??</p>

kclark

<p>Sorry for the late reply. What error are you getting? I'll give this a try and let you know what I come up with.</p><p> </p><p>Edit:</p><p>Can you post some sample data similar to what you're working with so I can try and recreate something similar on my end.</p>

holger

<p>Hey kclark,</p><p> </p><p>thanks for replying:) I think I will owe you more than one beer once this problem is solved!</p><p> </p><p>So I'm trying to input in the data set this type of query and get the following error after inserting the query and pressing ok...see error.png</p><p> </p><p>Sample data is critical, because we live here in germany...they don't like posting samples with real data:(</p><p> </p><p>Can I help you with something else to make it easier for you??</p><p> </p><p> </p>

holger

<p>nobody?</p><p> </p><p>kclark?

</p>

kclark

<p>Can you copy and paste that stack trace?</p>

holger

<p>If I insert the query (with replaced) parameters, i receive the following, but it is the same query like the original only with replace parameters, so it should return a result object...</p><p> </p><p> </p><p> </p><div>org.eclipse.birt.data.engine.odaconsumer.OdaDataException: Cannot get the result set metadata.</div><div> org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.</div><div>SQL error #1:ERROR: current transaction is aborted, commands ignored until end of transaction block</div><div> ;</div><div> org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block</div><div> </div><div>at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.newException(ExceptionHandler.java:52)</div><div> </div><div>at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:108)</div><div> </div><div>at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:84)</div><div> </div><div>at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getRuntimeMetaData(PreparedStatement.java:414)</div><div> </div><div>at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getProjectedColumns(PreparedStatement.java:377)</div><div> </div><div>at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.doGetMetaData(PreparedStatement.java:347)</div><div> </div><div>at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.execute(PreparedStatement.java:563)</div><div> </div><div>at org.eclipse.birt.data.engine.executor.DataSourceQuery.execute(DataSourceQuery.java:957)</div><div> </div><div>at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.executeOdiQuery(PreparedOdaDSQuery.java:466)</div><div> </div><div>at org.eclipse.birt.data.engine.impl.QueryExecutor.execute(QueryExecutor.java:1208)</div><div> </div><div>at org.eclipse.birt.data.engine.impl.ServiceForQueryResults.executeQuery(ServiceForQueryResults.java:233)</div><div> </div><div>at org.eclipse.birt.data.engine.impl.QueryResults.getResultIterator(QueryResults.java:178)</div><div> </div><div>at org.eclipse.birt.data.engine.impl.QueryResults.getResultMetaData(QueryResults.java:132)</div><div> </div><div>at org.eclipse.birt.report.data.adapter.impl.DataSetMetaDataHelper.getRuntimeMetaData(DataSetMetaDataHelper.java:194)</div><div> </div><div>at org.eclipse.birt.report.data.adapter.impl.DataSetMetaDataHelper.getRealMetaData(DataSetMetaDataHelper.java:153)</div><div> </div><div>at org.eclipse.birt.report.data.adapter.impl.DataSetMetaDataHelper.getDataSetMetaData(DataSetMetaDataHelper.java:115)</div><div> </div><div>at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.getDataSetMetaData(DataRequestSessionImpl.java:246)</div><div> </div><div>at org.eclipse.birt.report.designer.data.ui.util.DataSetProvider.populateAllOutputColumns(DataSetProvider.java:221)</div><div> </div><div>at org.eclipse.birt.report.designer.data.ui.util.DataSetProvider.getColumns(DataSetProvider.java:177)</div><div> </div><div>at org.eclipse.birt.report.designer.data.ui.util.DataSetProvider.getColumns(DataSetProvider.java:144)</div><div> </div><div>at org.eclipse.birt.report.designer.data.ui.dataset.OutputColumnsPage.setAnalysisTypeForColumn(OutputColumnsPage.java:584)</div><div> </div><div>at org.eclipse.birt.report.designer.data.ui.dataset.OutputColumnsPage.performOk(OutputColumnsPage.java:310)</div><div> </div><div>at org.eclipse.birt.report.designer.data.ui.property.AbstractPropertyDialog.okPressed(AbstractPropertyDialog.java:790)</div><div> </div><div>at org.eclipse.birt.report.designer.data.ui.property.AbstractPropertyDialog.okPressed(AbstractPropertyDialog.java:775)</div><div> </div><div>at org.eclipse.birt.report.designer.data.ui.dataset.DataSetEditor.okPressed(DataSetEditor.java:683)</div><div> </div><div>at org.eclipse.birt.report.designer.data.ui.property.AbstractPropertyDialog.buttonPressed(AbstractPropertyDialog.java:650)</div><div> </div><div>at org.eclipse.jface.dialogs.Dialog$2.widgetSelected(Dialog.java:628)</div><div> </div><div>at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:248)</div><div> </div><div>at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)</div><div> </div><div>at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1057)</div><div> </div><div>at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4170)</div><div> </div><div>at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3759)</div><div> </div><div>at org.eclipse.jface.window.Window.runEventLoop(Window.java:826)</div><div> </div><div>at org.eclipse.jface.window.Window.open(Window.java:802)</div><div> </div><div>at org.eclipse.birt.report.designer.ui.dialogs.BaseDialog.open(BaseDialog.java:111)</div><div> </div><div>at org.eclipse.birt.report.designer.data.ui.providers.EditableDataSetNodeProvider.performEdit(EditableDataSetNodeProvider.java:102)</div><div> </div><div>at org.eclipse.birt.report.designer.internal.ui.views.DefaultNodeProvider.performRequest(DefaultNodeProvider.java:472)</div><div> </div><div>at org.eclipse.birt.report.designer.internal.ui.views.actions.EditAction.doAction(EditAction.java:85)</div><div> </div><div>at org.eclipse.birt.report.designer.internal.ui.views.actions.AbstractElementAction.run(AbstractElementAction.java:70)</div><div> </div><div>at org.eclipse.birt.report.designer.internal.ui.views.RenameListener.doubleClick(RenameListener.java:214)</div><div> </div><div>at org.eclipse.jface.viewers.StructuredViewer$1.run(StructuredViewer.java:845)</div><div> </div><div>at org.eclipse.core.runtime.SafeRunner.run(SafeRunner.java:42)</div><div> </div><div>at org.eclipse.ui.internal.JFaceUtil$1.run(JFaceUtil.java:49)</div><div> </div><div>at org.eclipse.jface.util.SafeRunnable.run(SafeRunnable.java:175)</div><div> </div><div>at org.eclipse.jface.viewers.StructuredViewer.fireDoubleClick(StructuredViewer.java:843)</div><div> </div><div>at org.eclipse.jface.viewers.AbstractTreeViewer.handleDoubleSelect(AbstractTreeViewer.java:1477)</div><div> </div><div>at org.eclipse.jface.viewers.StructuredViewer$4.widgetDefaultSelected(StructuredViewer.java:1246)</div><div> </div><div>at org.eclipse.jface.util.OpenStrategy.fireDefaultSelectionEvent(OpenStrategy.java:249)</div><div> </div><div>at org.eclipse.jface.util.OpenStrategy.access$0(OpenStrategy.java:246)</div><div> </div><div>at org.eclipse.jface.util.OpenStrategy$1.handleEvent(OpenStrategy.java:307)</div><div> </div><div>at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)</div><div> </div><div>at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1057)</div><div> </div><div>at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4170)</div><div> </div><div>at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3759)</div><div> </div><div>at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine$9.run(PartRenderingEngine.java:1113)</div><div> </div><div>at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:332)</div><div> </div><div>at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine.run(PartRenderingEngine.java:997)</div><div> </div><div>at org.eclipse.e4.ui.internal.workbench.E4Workbench.createAndRunUI(E4Workbench.java:138)</div><div> </div><div>at org.eclipse.ui.internal.Workbench$5.run(Workbench.java:610)</div><div> </div><div>at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:332)</div><div> </div><div>at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:567)</div><div> </div><div>at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:150)</div><div> </div><div>at org.eclipse.birt.report.designer.ui.rcp.DesignerApplication.run(DesignerApplication.java:38)</div><div> </div><div>at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)</div><div> </div><div>at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)</div><div> </div><div>at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)</div><div> </div><div>at java.lang.reflect.Method.invoke(Unknown Source)</div><div> </div><div>at org.eclipse.equinox.internal.app.EclipseAppContainer.callMethodWithException(EclipseAppContainer.java:587)</div><div> </div><div>at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:198)</div><div> </div><div>at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:110)</div><div> </div><div>at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:79)</div><div> </div><div>at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:354)</div><div> </div><div>at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:181)</div><div> </div><div>at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)</div><div> </div><div>at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)</div><div> </div><div>at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)</div><div> </div><div>at java.lang.reflect.Method.invoke(Unknown Source)</div><div> </div><div>at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java:636)</div><div> </div><div>at org.eclipse.equinox.launcher.Main.basicRun(Main.java:591)</div><div> </div><div>at org.eclipse.equinox.launcher.Main.run(Main.java:1450)</div><div> </div><div>Caused by: org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.</div><div>SQL error #1:ERROR: current transaction is aborted, commands ignored until end of transaction block</div><div> ;</div><div> org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block</div><div> </div><div>at org.eclipse.birt.report.data.oda.jdbc.Statement.executeQuery(Statement.java:481)</div><div> </div><div>at org.eclipse.birt.report.data.oda.jdbc.Statement.getMetaUsingDefaultPolicy(Statement.java:388)</div><div> </div><div>at org.eclipse.birt.report.data.oda.jdbc.Statement.getMetaData(Statement.java:330)</div><div> </div><div>at org.eclipse.birt.report.data.oda.jdbc.bidi.BidiStatement.getMetaData(BidiStatement.java:56)</div><div> </div><div>at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.doGetMetaData(OdaQuery.java:423)</div><div> </div><div>at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.getMetaData(OdaQuery.java:390)</div><div> </div><div>at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getRuntimeMetaData(PreparedStatement.java:407)</div><div> </div><div>... 76 more</div><div> </div><div>Caused by: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block</div><div> </div><div>at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2096)</div><div> </div><div>at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1829)</div><div> </div><div>at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)</div><div> </div><div>at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:510)</div><div> </div><div>at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:386)</div><div> </div><div>at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271)</div><div> </div><div>at org.eclipse.birt.report.data.oda.jdbc.Statement.executeQuery(Statement.java:477)</div><div> </div><div>... 82 more</div>

GLO_FR

<p>Hi holger,</p><p> </p><p>First of all, you can't have several dates in a between order.</p><p>So if you have different date (ddd and eee), use just one date</p><p> </p><p>Second thing, can you run BIRT in console mode and put a</p><p><strong>Packages.java.lang.System.out.println(this.queryText.trim());</strong></p><p> </p><p>at the end of your beforeOpenScript so you can see the all request</p>

GLO_FR

<p>Try also to cast your date in string</p><p> </p><div>this.queryText = this.queryText.replace("ddd", params["rp_1].value.<strong>toString()</strong>);</div><div>this.queryText = this.queryText.replace("eee", params["rp_2"].value.<strong>toString()</strong>);</div><div> </div><div>tested on Actuate BIRT Designer Professional Version : 11.0.4 and it works</div>

holger

<p>it looks like there is only the possibily to replace one string and not a bunch of them...</p><p> </p><p>or has anybody a sample file with replacing 3 or more IN statements and one date parameter?</p><p> </p><p>@Glo_FR</p><p> </p><p>I tried to work in the console mode but it didnt really work...its missing the special exe and i don't know where it is...is the console modus working with all installations??</p>

GLO_FR

<p>Hi</p><p> </p><p>For the "console mode" just run BIRT with the <strong>eclipsec.exe</strong> file ("C:Program Files (x86)....eclipseeclipse<strong>c</strong>.exe")</p><p> </p><p>attached is an example based on the Classic Model sample database.</p><p> </p><p>Select several countries in the list, choose 2 dates (like 1/1/2000 and 1/1/20013) and run the report and it should work</p><p> </p><p> </p>

holger

<p>Hey Glo_FR,</p><p> </p><p>I'm really happy that you wrote me such nice rptdesign!</p><p> </p><p>AFTER HOURS spending time in solving problems I finally made it!!!!!!</p><p> </p><p>It is so easy in the end and I'm so thankful for your help!!!</p><p> </p><p> </p><p>Once you are in Berlin, I will offer you some drinks!</p><p> </p><p>Come and get them:)</p><p> </p><p> </p><p>Have a nice weekend!</p>

GLO_FR

<p>Cool.</p><p> </p><p>>><em> Once you are in Berlin, I will offer you some drinks!</em></p><p>Nice offer, thank!</p><p>Good for you that you/we don't live in Munich, because with such an offer, in some days, it would have cost you a bunch of euros !

</p><p> </p><p>Have a nice week-end too</p>

appform

<p>Hi</p>
<p> </p>
<p>Can you provide me the same sample file using stored procedure instead of query.</p>
<p>To produce the report i am using stored procedure.</p>
<p>i am calling the stored procedure having a multi select input parameter like this in data set editor:-</p>
<p> </p>
<p>{call Test(?)}</p>
<p> </p>
<p>below code mentioned in beforeOpen():-</p>
<p> </p>
<p>this.queryText = this.queryText.replace("?",params["User"].value.join("','" ));<br><br>
Packages.java.lang.System.out.println(this.queryText.trim());</p>
<p> </p>
<p>but i am getting 17070 error.</p>
<p> </p>
<p>Please help me.</p>
<p> </p>
<p>Regards,</p>
<p>Swapna.</p>

GLO_FR

<p>Hi,</p>
<p> </p>
<p>you can't change something in the stored procedure from BIRT.</p>
<p>The stored procedure is stored on the "DB side".</p>
<p>You can execute it from BIRT but y<span style="font-size:14px;">ou can't modify it.</span></p>

appform

<p>Thanks for your reply.</p>
<p> </p>
<p>Regards,</p>
<p>Swapna.</p>

Freddie_M

<p>Hello everyone</p>
<p> </p>
<p>Switching back to the original Topic of this Post : "Multiple String Values in where clause"</p>
<p> </p>
<p>==> is there any way to pass multiples values to the IN Clause of my DataSet without changing the Query at Report execution time ?</p>
<p> </p>
<p>I can't use features like Property Binding since the Select Statement is not in the Report itself but outsourced in a dedicated DB.</p>
<p> </p>
<p>Basically i would like to define a simple DataSet select * from table1 where table1.column1 in (?) </p>
<p> </p>
<p>Thanks a lot everyone !!</p>
<p> </p>
<p>Chris</p>

igorciza

<p>@GLO_FR Could you please let me know how safe is your solution against SQL Injection. I am trying to deal with multi select parameter but our main concern is SQL injection</p>