jaspersoft 6.1 adhoc filter of type date not affecting the results
Posted on August 20, 2015 at 12:20am
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?
Joined: Jul 7 2014 - 4:39am
Last seen: 7 years 6 months ago
Posted on August 25, 2015 at 7:32am
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?
Joined: Sep 13 2011 - 8:04am
Last seen: 4 years 5 months ago
Posted on August 25, 2015 at 11:47pm
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
Joined: Jul 7 2014 - 4:39am
Last seen: 7 years 6 months ago
Posted on August 26, 2015 at 12:43am
@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.
Joined: Jul 7 2014 - 4:39am
Last seen: 7 years 6 months ago
Posted on August 26, 2015 at 11:57pm
oh by the way my database is oracle
Joined: Jul 7 2014 - 4:39am
Last seen: 7 years 6 months ago
Posted on September 4, 2015 at 5:04am
any ideas of the solution ?
Joined: Jul 7 2014 - 4:39am
Last seen: 7 years 6 months ago
Posted on September 9, 2015 at 12:08pm
We are having the same exact issue w/ an oracle backend. Have not encountered it with Postgres.
Joined: Jun 14 2013 - 10:57am
Last seen: 7 years 6 months ago
Posted on September 9, 2015 at 1:14pm
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 <value> elements as shown:
<bean class="com.jaspersoft.commons.semantic.dsimpl.SQLGenerator" id="defaultSQLGenerator" scope="prototype">
<property name="excludeFunctionTemplates">
<list>
<!-- <value>Integer</value> -->
<!-- <value>Timestamp</value> -->
</list>
</property>
<property name="functionTemplates"><map></map></property></bean></value>
Joined: Jun 14 2013 - 10:57am
Last seen: 7 years 6 months ago
Posted on September 10, 2015 at 1:32am
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
Joined: Jul 7 2014 - 4:39am
Last seen: 7 years 6 months ago
anyone has a solution for this please? i'm not finding any in the forums.