ramm Posted January 3, 2007 Share Posted January 3, 2007 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. Link to comment Share on other sites More sharing options...
slaisne Posted January 3, 2007 Share Posted January 3, 2007 Declare a String parameter PARAM.Its value is: 1,2,3,4,5,6 Finally, use it in the querySELECT SalesId, customer, product, salevalue from salestable where customer in ( $P!{PARAM} ) Link to comment Share on other sites More sharing options...
nittu02 Posted March 13, 2007 Share Posted March 13, 2007 slaisne wrote:Declare a String parameter PARAM.Its value is: 1,2,3,4,5,6Finally, use it in the querySELECT 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 !! :) Link to comment Share on other sites More sharing options...
slaisne Posted March 13, 2007 Share Posted March 13, 2007 If your data base support subqueries, use them Link to comment Share on other sites More sharing options...
csbac Posted March 16, 2007 Share Posted March 16, 2007 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 Link to comment Share on other sites More sharing options...
csbac Posted March 16, 2007 Share Posted March 16, 2007 Post edited by: csbac, at: 2007/03/16 12:38 Link to comment Share on other sites More sharing options...
ashwiniwaghole Posted March 23, 2011 Share Posted March 23, 2011 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) Link to comment Share on other sites More sharing options...
ashwiniwaghole Posted March 23, 2011 Share Posted March 23, 2011 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. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now