preetha00

The problem is the parameters...in the report i need optional parameters also which i rectify using teh case when feature actually that query works fine if i am changing all the parameters as mandatory parameters.....but i require the optional parameters also... so if my query is like this SELECT invoice.`INVOICE_DATE` AS Date, invoice.`TILL_NO` AS Till_No, time(invoice.CREATED_ON)as "Time", concat(user.first_name," ",user.last_name)as "Cashier", payment_details.`SYS_INVOICE_NO` AS Invoice_No, invoice.`total_amount`, tender_type_level1.`sub_tender1_name` AS Type_of_Coupen, payment_details.`coup_denomination`, payment_details.`coup_qty`, payment_details.`coup_amount_paid` FROM `user` user, `invoice` invoice, `payment_details` payment_details, `tender_type_level1` tender_type_level1 WHERE (invoice.SYS_INVOICE_NO = payment_details.SYS_INVOICE_NO) and (invoice.CASHIER_ID = user.USER_ID) and payment_details.sub_tender1_id = tender_type_level1.sub_tender1_id and (invoice.BILL_VOID_FLG IS null and payment_details.PAYMENT_VOID_FLG = 'N' and payment_details.TENDER_ID = 'CP') AND invoice.invoice_date >= date('2009-04-01') AND invoice.invoice_date <= date('2009-04-29') AND invoice.TILL_NO in ($P!{tillNoValuated}) AND tender_type_level1.sub_tender1_name IN ($P!{typeOfCouponValuated}) AND invoice.sys_invoice_no IN ($P!{invoiceNovaluated}) AND invoice.cashier_id=(case when ($P{cashierId}) != '' then ($P{cashierId}) else invoice.cashier_id end) AND invoice.store_id in ($P!{storeIdsValuated}) i get the report generated when i make all the params mandatory.

there are seperate exporter API for each type in the jasperreports like net.sf.jasperreports.engine.JasperPrint net.sf.jasperreports.engine.export.JRHtmlExporter net.sf.jasperreports.engine.export.JRPdfExporter net.sf.jasperreports.engine.export.JRXmlExporter net.sf.jasperreports.engine.export.JRXlsExporter net.sf.jasperreports.engine.export.JRCsvExporter you can use them to export it to different formats.

Thnks i changed those types to string itself... Now my query looks something like this SELECT invoice.`INVOICE_DATE` AS Date, invoice.`TILL_NO` AS Till_No, time(invoice.CREATED_ON)as "Time", concat(user.first_name," ",user.last_name)as "Cashier", payment_details.`SYS_INVOICE_NO` AS Invoice_No, invoice.`total_amount`, tender_type_level1.`sub_tender1_name` AS Type_of_Coupen, payment_details.`coup_denomination`, payment_details.`coup_qty`, payment_details.`coup_amount_paid` FROM `user` user, `invoice` invoice, `payment_details` payment_details, `tender_type_level1` tender_type_level1 WHERE (invoice.SYS_INVOICE_NO = payment_details.SYS_INVOICE_NO) and (invoice.CASHIER_ID = user.USER_ID) and payment_details.sub_tender1_id = tender_type_level1.sub_tender1_id and (invoice.BILL_VOID_FLG IS null and payment_details.PAYMENT_VOID_FLG = 'N' and payment_details.TENDER_ID = 'CP') AND invoice.invoice_date >= date($P{fromDate}) AND invoice.invoice_date <= date($P{toDate}) AND invoice.TILL_NO in ($P!{tillNoValuated}) AND tender_type_level1.sub_tender1_name IN ($P!{typeOfCouponValuated}) AND invoice.sys_invoice_no IN (case when ($P{invoiceNovaluated}) != '' then ($P!{invoiceNovaluated}) else invoice.sys_invoice_no end) AND invoice.cashier_id=(case when ($P{cashierId}) != '' then ($P{cashierId}) else invoice.cashier_id end) AND invoice.store_id in (case when ($P{storeIdsValuated}) != '' then ($P!{storeIdsValuated}) else invoice.store_id end) but now when i try to execute the query i get an error net.sf.jasperreports.engine.JRException at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource( at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource( at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource( at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters( at net.sf.jasperreports.engine.fill.JRBaseFiller.fill( at net.sf.jasperreports.engine.fill.JRBaseFiller.fill( at net.sf.jasperreports.engine.fill.JRFiller.fillReport( at net.sf.jasperreports.engine.JasperFillManager.fillReport( at net.sf.jasperreports.engine.JasperFillManager.fillReportToFile( at net.sf.jasperreports.engine.JasperFillManager.fillReportToFile( at src.JasperReportTest.runreport( at src.JasperReportTest.main( Caused by: at com.mysql.jdbc.MysqlIO.checkErrorPacket( at com.mysql.jdbc.MysqlIO.sendCommand( at com.mysql.jdbc.MysqlIO.sqlQueryDirect( at com.mysql.jdbc.Connection.execSQL( at com.mysql.jdbc.PreparedStatement.executeQuery( at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:135) : Error executing SQL statement for : Test ReportJRJdbcQueryExecuter.java:141)JRFillDataset.java:671)JRFillDataset.java:591)JRBaseFiller.java:1212)JRBaseFiller.java:842)JRBaseFiller.java:791)JRFiller.java:63)JasperFillManager.java:402)JasperFillManager.java:188)JasperFillManager.java:94)JasperReportTest.java:57)JasperReportTest.java:87)java.sql.SQLException: Operand should contain 1 column(s)MysqlIO.java:2001)MysqlIO.java:1168)MysqlIO.java:1279)Connection.java:2281)PreparedStatement.java:1634)I have bolded the part of the text that i feel that is the reason for the error, though i am not sure why it is wrong. please help. Post Edited by preetha00 at 04/29/2009 04:27

please let me know how i can make that query work in jasper report.

Can we not include more than one IN operator with parameter in a query. I am using jasper report with ireport I am trying the following query SELECT invoice.`INVOICE_DATE` AS Date, invoice.`TILL_NO` AS Till_No, time(invoice.CREATED_ON)as "Time", concat(user.first_name," ",user.last_name)as "Cashier", payment_details.`SYS_INVOICE_NO` AS Invoice_No, invoice.`total_amount`, tender_type_level1.`sub_tender1_name` AS Type_of_Coupen, payment_details.`coup_denomination`, payment_details.`coup_qty`, payment_details.`coup_amount_paid` FROM `user` user, `invoice` invoice, `payment_details` payment_details, `tender_type_level1` tender_type_level1 WHERE (invoice.SYS_INVOICE_NO = payment_details.SYS_INVOICE_NO) and (invoice.CASHIER_ID = user.USER_ID) and payment_details.sub_tender1_id = tender_type_level1.sub_tender1_id and (invoice.BILL_VOID_FLG IS null and payment_details.PAYMENT_VOID_FLG = 'N' and payment_details.TENDER_ID = 'CP') AND invoice.cashier_id=(case when($P{cashierId})! = '' then($P{cashierId})else invoice.cashier_id end) AND invoice.invoice_date >= date($P{fromDate}) AND invoice.invoice_date <= date($P{toDate}) AND $X{IN,invoice.TILL_NO,tillNo} AND $X{IN ,tender_type_level1.sub_tender1_name,typeOfCoupon} AND $X{IN,invoice.sys_invoice_no,invoiceNo} when i give preview for the same i get this error com.jaspersoft.ireport.designer.errorhandler.ProblemItem@1b4e7ae Cannot cast from Collection to String net.sf.jasperreports.engine.design.JRDesignExpression@162bdd8 com.jaspersoft.ireport.designer.errorhandler.ProblemItem@1d690fb Cannot cast from Collection to String net.sf.jasperreports.engine.design.JRDesignExpression@17ff60e How can that be rectified?