[Getting value of one parameter value to another parameter in jasper server]

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 value

which is entered/selected by an user at runtime while evaluating  PAYMENT_METHOD_CONDITION parameter .

 

Can anybody help on this?

 

Thanks,

Anandh.

[JasperServer & dynamic query via parameters]

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?