jaspersoft 6.1 adhoc filter of type date not affecting the results

[mc.bechara]

I have an adhoc view with field myDate, when I add it to the filters and say myDate is After DAY-30 or any other time value, nothing is affected.

There results don't get refreshed with this filter. However same report used to work with jaspersoft 6.0

Why isn't it working in version 6.1?

[marianol]

I just tested this with the supermart domain that comes with the samples and is working. I just selected the Supermart Domain, the Expenses Group and filtered by expense date (YEAR-3 or MONTH-40 since the dates of that dataset are for 2012 and 2013) and choose "is after" for the filter action. 

One important thing to note is that you need to press the APPLY button at the bottom of the filter panel so the filter get applied.

Was the domain imported form 6.0? What database are you using?

[mc.bechara]

of course I clicked Apply and nothing happened.

The domain was imported from v.6.0

and I have this in the log:

xception calling JRDataSource.next() for query  select * from ( select "PO_DATE", 

Sum("QUANTITY") as "Sum_QUANTITY", 

Sum("TOTAL_NET_PRICE_USD") as "Sum_TOTAL_NET_PRICE_USD"

from "KARLOS"."VIEW_REPORT_PURCHASES"

where "SCOPE_ID" = 'CB' and 1 = 1 and 1 = 1 and 1 = 1 and EXTRACT(YEAR FROM "PO_DATE") = 2015 and ("PO_DATE" > '2015-08-25 12:29:12')

group by "PO_DATE"

order by "PO_DATE"

 ) where ROWNUM <= 200001 

 

at com.jaspersoft.commons.semantic.dsimpl.JRQueryDataSet$JRDataSetIterator.next(JRQueryDataSet.java:432)

at com.jaspersoft.commons.datarator.CachedData.fetchData(CachedData.java:219)

... 127 more

Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: null.

at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:267)

at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:168)

at com.jaspersoft.commons.util.JSControlledJdbcQueryExecuter.run(JSControlledJdbcQueryExecuter.java:156)

... 1 more

Caused by: java.sql.SQLDataException: ORA-01861: literal does not match format string

 

[mc.bechara]

@marianol  I found out that in v.6.0  my query in jasper used to be and ("PO_DATE" > TIMESTAMP '2015-08-12 00:00:00')

while in v.6.1 it's and ("PO_DATE" > '2015-08-12 00:00:00') that's why the error.

But how to fix it? knowing that mapping of semantics is correct.

[mc.bechara]

oh by the way my database is oracle

[mc.bechara]

anyone has a solution for this please? i'm not finding any in the forums.

 

[mc.bechara]

any ideas of the solution ?

[gsteiner]

We are having the same exact issue w/ an oracle backend.  Have not encountered it with Postgres.

[gsteiner]

Here is a workaround I received from Support.

 

There is a workaround and to make this change, edit WEB-INF/applicationContext-semanticLayer.xml and look for the property "excludeFunctionTemplates" in the bean "defaultSQLGenerator".Comment out the two  elements as shown:                                                    

[mc.bechara]

But if i comment those 2 I'm getting the following error:   java.lang.RuntimeException: groovy evaluation failed on string args[0].value and expression Integer(2015)

and if I comment only the timestamp I get the following error:     java.lang.RuntimeException: error getting schema