jaspersoft 6.1 adhoc filter of type date not affecting the results

1

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?

mc.bechara's picture
Joined: Jul 7 2014 - 4:39am
Last seen: 4 years 2 months ago

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

 

mc.bechara - 4 years 2 months ago

8 Answers:

0

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?

marianol's picture
4788
Joined: Sep 13 2011 - 8:04am
Last seen: 1 year 1 month ago
0

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's picture
Joined: Jul 7 2014 - 4:39am
Last seen: 4 years 2 months ago
1

@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's picture
Joined: Jul 7 2014 - 4:39am
Last seen: 4 years 2 months ago
0

oh by the way my database is oracle

mc.bechara's picture
Joined: Jul 7 2014 - 4:39am
Last seen: 4 years 2 months ago
0

any ideas of the solution ?

mc.bechara's picture
Joined: Jul 7 2014 - 4:39am
Last seen: 4 years 2 months ago
0

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

gsteiner's picture
Joined: Jun 14 2013 - 10:57am
Last seen: 4 years 2 months ago
1

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>
gsteiner's picture
Joined: Jun 14 2013 - 10:57am
Last seen: 4 years 2 months ago
0

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

mc.bechara's picture
Joined: Jul 7 2014 - 4:39am
Last seen: 4 years 2 months ago
Feedback