JasperServer & dynamic query via parameters

[anandh.nr]

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 Message

Error filling report

Error Trace

com.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?