Multiple values for a single parameter

[ramm]

Is it possible to pass multiple values for a single parameter? If so, how?

Say a query could look like "SELECT SalesId, customer, product, salevalue from salestable where customer in (1, 2, 3, 4, 5, 6....)"

 

The values 1,2,3,4,5,6... etc. would be supplied by the user. The list of values would need to be passed into the engine to generate the result set.

[slaisne]

Declare a String parameter PARAM.

Its value is: 1,2,3,4,5,6

 

Finally, use it in the query

SELECT SalesId, customer, product, salevalue from salestable where customer in ( $P!{PARAM} )

[nittu02]

slaisne wrote:

Declare a String parameter PARAM. Its value is: 1,2,3,4,5,6 Finally, use it in the query SELECT SalesId, customer, product, salevalue from salestable where customer in ( $P!{PARAM} )

 

 

Hey.. i read your reply.

 

I have an additional question. is it possible to add a multi-select drop down or something in order to pass multiple values to a single parameter? in zucker reports??

 

That is, if we are using the 'in' operator, we have to manually enter the values right? instead of that, can we have a couple of drop downs or a list of drop downs from which the parameters will be selected?

 

from the first drop down i will select 1. from the second, i will select 2. likewise, the query will be

 

SELECT SalesId, customer, product, salevalue from salestable where customer in (1,2)

 

any help would be really appreciated !!

 

:)

[slaisne]

If your data base support subqueries, use them

[csbac]

Hello!

Just ... a note about your solution:

The "!" in $P!{PARAM} is obviously VERY important.

 

Without, the parameter is uses as a JDBC parameter, and JDBC does not support in (?) parameters.

The error messages depend on the db system (firebird: cast error; mysql: empty report), yet it probably never works.

 

I presume the "!" makes it a string insertion, not a JDBC parameter?

 

Thanks,

Sebastian

 

PS: Just in case s/o noticed ... a few minutes ago my post was a bit longer and said exactly the opposite ;-)

Post edited by: csbac, at: 2007/03/16 12:37

[csbac]

Post edited by: csbac, at: 2007/03/16 12:38

[ashwiniwaghole]

Hello,

 I saw the post on forum its really helping me well, Report is run fine in iReport, But i have error in Zucker report when i compile it in.

I create location parameter in Zucker report parameter with direct input type.

i set expression for auxilary parameter as below

$P{location_c}.equals("") ? "" : " AND opportunities_cstm.location_c IN ("+$P{location_c}+")"

The auxilary parameter get null value.

Please reply me.

 

Code:

at.go_mobile.zuckerreports.JasperBatchMain custom/ZuckerReports/temp/e544f08a-fbac-2e58-8143-4d89cdcfa08b/cmd.properties 2>&1JasperBatchMain :: loading jasper design custom/ZuckerReports/resources/AllEnquiry.jasperJasperBatchMain :: getParameterValue(REPORT_PARAMETERS_MAP, java.util.Map) => nullJasperBatchMain :: getParameterValue(JASPER_REPORT, net.sf.jasperreports.engine.JasperReport) => nullJasperBatchMain :: getParameterValue(REPORT_CONNECTION, java.sql.Connection) => nullJasperBatchMain :: getParameterValue(REPORT_MAX_COUNT, java.lang.Integer) => nullJasperBatchMain :: getParameterValue(REPORT_DATA_SOURCE, net.sf.jasperreports.engine.JRDataSource) => nullJasperBatchMain :: getParameterValue(REPORT_SCRIPTLET, net.sf.jasperreports.engine.JRAbstractScriptlet) => nullJasperBatchMain :: getParameterValue(REPORT_LOCALE, java.util.Locale) => nullJasperBatchMain :: getParameterValue(REPORT_RESOURCE_BUNDLE, java.util.ResourceBundle) => nullJasperBatchMain :: getParameterValue(REPORT_TIME_ZONE, java.util.TimeZone) => nullJasperBatchMain :: getParameterValue(REPORT_FORMAT_FACTORY, net.sf.jasperreports.engine.util.FormatFactory) => nullJasperBatchMain :: getParameterValue(REPORT_CLASS_LOADER, java.lang.ClassLoader) => nullJasperBatchMain :: getParameterValue(REPORT_URL_HANDLER_FACTORY, java.net.URLStreamHandlerFactory) => nullJasperBatchMain :: getParameterValue(REPORT_FILE_RESOLVER, net.sf.jasperreports.engine.util.FileResolver) => nullJasperBatchMain :: getParameterValue(REPORT_TEMPLATES, java.util.Collection) => nullJasperBatchMain :: getParameterValue(SORT_FIELDS, java.util.List) => nullJasperBatchMain :: getParameterValue(REPORT_VIRTUALIZER, net.sf.jasperreports.engine.JRVirtualizer) => nullJasperBatchMain :: getParameterValue(IS_IGNORE_PAGINATION, java.lang.Boolean) => nullJasperBatchMain :: getParameterValue(NUMBERFORMAT, java.text.DecimalFormat) => nullJasperBatchMain :: getParameterValue(LOCALE, java.util.Locale) => nullJasperBatchMain :: getParameterValue(DateFrom, java.util.Date) => Sat Jan 01 00:00:00 IST 2011JasperBatchMain :: getParameterValue(DateTo, java.util.Date) => Wed Mar 23 00:00:00 IST 2011JasperBatchMain :: getParameterValue(location_c, java.lang.String) => 'Pune','Mumbai'JasperBatchMain :: getParameterValue(AUX_Location, java.lang.String) => nullJasperBatchMain :: getParameterValue(EnquiryMode, java.lang.String) => For Orderlog4j:WARN No appenders could be found for logger (net.sf.jasperreports.extensions.ExtensionsEnvironment).log4j:WARN Please initialize the log4j system properly.JasperBatchMain :: Error executing SQL statement for : AllEnquirynet.sf.jasperreports.engine.JRException: Error executing SQL statement for : AllEnquiryat net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:143)at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:684)at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:605)at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1281)at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:901)at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:845)at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:58)at at.go_mobile.zuckerreports.JasperBatchMain.main(JasperBatchMain.java:126)Caused by: java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Pune','Mumbai')and opportunities_cstm.enquiry_date_c >= '2' at line 8at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2926)at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)at com.mysql.jdbc.Connection.execSQL(Connection.java:2978)at com.mysql.jdbc.Connection.execSQL(Connection.java:2902)at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:933)at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1027)at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:137)

[ashwiniwaghole]

Hello,

 I saw the post on forum its really helping me well, Report is run fine in iReport, But i have error in Zucker report when i compile it in.

I create location parameter in Zucker report parameter with direct input type.

i set expression for auxilary parameter as below

$P{location_c}.equals("") ? "" : " AND opportunities_cstm.location_c IN ("+$P{location_c}+")"

The auxilary parameter get null value.

If u found the solution thn , Please reply me.