anandh.nr

Hi, I have 2 parameters 1. <parameter name="PAYMENT_METHOD" class="java.lang.String"> <defaultValueExpression><![CDATA[new ArrayList<String>(Arrays.asList("ALL ORDERS","CREDITCARDS ONLY","PURCHASE ORDERS ONLY"))]]></defaultValueExpression></parameter> 2. <parameter name="PAYMENT_METHOD_CONDITION" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA[$P{PAYMENT_METHOD}.equals("PURCHASE ORDERS ONLY")? " payment_method=PURCHASE_ORDER' ": $P{PAYMENT_METHOD}.equals("CREDITCARDS ONLY")? " payment_method='CREDITCARD' ": "]]></defaultValueExpression></parameter> here I have used PAYMENT_METHOD parameter value in PAYMENT_METHOD_CONDITION parameter for making some condition to use in the query. Then I will add PAYMENT_METHOD_CONDITION parameter value directly in the query as below select * from revenue_by_currency where $P!{PAYMENT_METHOD_CONDITION } it WORKS FINE in the iReport. But not working in jasper server. PAYMENT_METHOD_CONDITION parameter always returns ""(empty string) because PAYMENT_METHOD parameter always returns defult value, not returning the valuewhich is entered/selected by an user at runtime while evaluating PAYMENT_METHOD_CONDITION parameter . Can anybody help on this? Thanks,Anandh.

Hi All, I need to generate the sales report . with the following input prameters 1. Payment method: CREDIT CARD, PURCHASE ORDER, ALL 2. Date grouping : DAY, WEEK, MONTH, YEAR 3. Date Range Options: TODAY, YESTERDAY, LAST FULL YEAR, LAST FULL MONTH So I defined all the above three parameters in iReport and created some more parameters(which will NOT PROMPT) for building dynamic query. Lets say my query is, select created_date, sum(amount) from sales. If Payment method: CREDIT CARD, Date grouping : DAY and Date Range Option: LAST FULL YEAR then my query dynamically generates based on the above inputs as follows, select created_date, sum(amount) from sales where payment_method='CREDI CARD' and created_date between '1day of last year' and 'last day of last year' group by created_date. if Date Range Option: LAST FULL MONTH then query will be select created_date, sum(amount) from sales where payment_method='CREDI CARD' and created_date between '1day of last monthr' and 'last day of last month' group by created_date. Here, as said earlier I have some more parameters(which will NOT PROMPT) for building dynamic query. 1. PAYMENT_METHOD_CONDITION which takes care of payment_method option in the query $P{PAYMENT_METHOD}.equals("ALL ORDERS")? "": $P{PAYMENT_METHOD}.equals("CREDITCARDS ONLY")? " payment_method='CREDITCARD' ": " payment_method='PURCHASE_ORDER' "2. DATE_RANGE_OPTIONS_CONDITION which takes care of 'created_date between '1day of last monthr' and 'last day of last month' part in the query ' and has the default value as follows $P{DATE_RANGE_OPTIONS}.equals("TODAY")? " created_date between DATE_FORMAT(now(),'%Y-%m-%d 00:00:00') and now() ": $P{DATE_RANGE_OPTIONS}.equals("THIS WEEK TO DATE")? " week(created_date) = week(now()) and year(created_date) = year(now()) ": $P{DATE_RANGE_OPTIONS}.equals("THIS MONTH TO DATE")? " created_date between date_format(now() ,'%Y-%m-01 00:00:00') and now() ": $P{DATE_RANGE_OPTIONS}.equals("THIS YEAR TO DATE")? " created_date between date_format(now() ,'%Y-01-01 00:00:00') and now() ": $P{DATE_RANGE_OPTIONS}.equals("YESTERDAY")? " created_date between date_format(subdate(now(), 1),'%Y-%m-%d 00:00:00') and date_format(subdate(now(), 1),'%Y-%m-%d 23:59:59') ": $P{DATE_RANGE_OPTIONS}.equals("LAST FULL WEEK")? " week(created_date) = week(now()) - 1 and year(created_date) = year(now()) ": $P{DATE_RANGE_OPTIONS}.equals("LAST FULL MONTH")? " created_date between date_format(curdate() - interval 1 month,'%Y-%m-01 00:00:00') and date_format(last_day(curdate()-interval 1 month),'%Y-%m-%d 23:59:59') ": $P{DATE_RANGE_OPTIONS}.equals("LAST FULL YEAR")? " created_date between date_format(curdate() - interval 1 year,'%Y-01-01 00:00:00') and date_format(curdate() - interval 1 year,'%Y-12-31 23:59:59') ": $P{DATE_RANGE_OPTIONS}.equals("CUSTOM RANGE")? " created_date>=""+($P{FROM_DATE}.year+1900)+"-"+($P{FROM_DATE}.month+1)+"-"+$P{FROM_DATE}.date+" 00:00" and created_date<=""+($P{TO_DATE}.year+1900)+"-"+($P{TO_DATE}.month+1)+"-"+$P{TO_DATE}.date+" 23:59"": ""3. GROUP BY parmeter used to take care group-by options and has the following default value $P{DATE_GROUPING}.equals("DAY WISE")? " DATE_FORMAT(CREATED_DATE,'%m-%d-%Y') ": $P{DATE_GROUPING}.equals("WEEK WISE")? " CONCAT(DATE_FORMAT(CREATED_DATE,'%Y'),' WEEK ',(WEEK(CREATED_DATE)+1)) ": $P{DATE_GROUPING}.equals("MONTH WISE")? " DATE_FORMAT(CREATED_DATE,'%Y-%m') ": " DATE_FORMAT(CREATED_DATE,'%Y') " 4. PAYMENT_METHOD_LOGICAL_AND used for logical and $P{PAYMENT_METHOD}.equals("ALL ORDERS")? " ": " and " SO my report query in iReport will be select created_date, sum(USD) as 'USD' from sales where $P!{PAYMENT_METHOD_CONDITION} $P!{PAYMENT_METHOD_LOGICAL_AND} $P!{DATE_RANGE_OPTIONS_CONDITION} GROUP BY $P!{GROUP_BY} So it WORKS FINE in the iReport. But when I deploy same in JASPER SERVER it is throwing following error Error MessageError filling report Error Tracecom.jaspersoft.jasperserver.api.JSException: Error filling report at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$FillResultListener.reportFillError(EngineServiceImpl.java:1262) at net.sf.jasperreports.engine.fill.BaseFillHandle.notifyError(BaseFillHandle.java:211) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFiller.run(BaseFillHandle.java:135) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:886) at Error Message Error Trace net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:240) at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:168) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1114) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:691) at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1314) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:931) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFiller.run(BaseFillHandle.java:120) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:886) at net.sf.jasperreports.engine.fill.BaseFillHandle.startFill(BaseFillHandle.java:165) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$AsynchronousReportFiller.fillReport(EngineServiceImpl.java:842) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1731) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runWithDataSource(EngineServiceImpl.java:1086) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:1015) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:908) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:724) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'GROUP BY DATE_FORMAT(CREATED_DATE,'%Y') ORDER BY DATE_FORMAT(CREATED_DATE,' at line 11 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:526) at com.mysql.jdbc.Util.handleNewInstance(Util.java:403) at com.mysql.jdbc.Util.getInstance(Util.java:378) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1031) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3361) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3295) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1852) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1975) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2476) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1583) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1725) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:233) ... 16 more Error Message com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'GROUP BY DATE_FORMAT(CREATED_DATE,'%Y') ORDER BY DATE_FORMAT(CREATED_DATE,' at line 11 But I have configured all the prompted parameters in jasper server properly. But still Jasper server is not generating query based on the input paramenters and throwing above error. Can anyone please guide me on this. 1. Do I need define all the parameters(NOT PROMPTING) which are used for generating query dynamically should also be defined in jasper server or what do I need to do for those parameter which actually not updating value in the query?