anandh.nr Posted September 1, 2014 Share Posted September 1, 2014 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 : DAYandDate Range Option: LAST FULL YEARthen 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 reportError 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 MessageError Tracenet.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 moreError Messagecom.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? 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