ReportServer Forum

tristan

Member

Registered: 2013-11-14

java.lang.NoSuchMethodError when using expressions in Dynamic List SQL

I am trying out the Dynamic Lists feature of report server and cannot get any of the parameterised date expressions to work correctly.

I have created a Dynamic List using a query that looks something like this:

SELECT t1.field1, t1.field2, t1.field3
FROM myTable AS t1
INNER JOIN otherTable as t2 ON t1.joinField1 = t2.joinField
WHERE DATE(t2.itemDate) BETWEEN ${startDate} AND ${endDate}

I created the parameters and this works perfectly well.

Now I want to be able to schedule this report to run every night and email yesterdays data to a client. So I modified the SQL to look like this:

SELECT t1.field1, t1.field2, t1.field3
FROM myTable AS t1
INNER JOIN otherTable as t2 ON t1.joinField1 = t2.joinField
WHERE DATE(t2.reportDate) BETWEEN ${today.clearTime.addDays(-1)} AND ${today.clearTime.addSeconds(-1)}

This fails and I get a NoSuchMethodError in the tomcat logs. I get the same error if I try ${today.format("...")}. After experimenting a bit, it seems that the error occurs with any expression that has a bracket in it. So, for example, ${today.clearTime} is fine, but ${today.clearTime()} fails.

I tried copy and pasting the examples in p46 of the (users) manual and get the same result.

Any idea what is going wrong here?

EDIT:
I also tried modifying my WHERE clause to
WHERE DATE(t2.ITEMDATE) = DATE_FORMAT(${today},"%Y-%m-%d") and I get no result.

Is there any way to access debug logs to view
(a) the value of ${today} and
(b) the actual SQL that is sent to the database server

Last edited by tristan (2013-11-26 16:23:55)

Marcel Berger

InfoFabrik

Registered: 2012-03-14

Website

Re: java.lang.NoSuchMethodError when using expressions in Dynamic List SQL

Hi Tristan,

it works a little bit different. You need a date parameter to enter a formula expression.

Use your example with the two date parameters

SELECT t1.field1, t1.field2, t1.field3
FROM myTable AS t1
INNER JOIN otherTable as t2 ON t1.joinField1 = t2.joinField
WHERE DATE(t2.itemDate) BETWEEN ${startDate} AND ${endDate}

Execute the dynamic list and switch both parameters to formula mode (right mouse click on parameter). Now you can enter the date formula expression.

Kind regards
Marcel

---

https://www.infofabrik.de
https://www.reportserver.net

tristan

Member

Registered: 2013-11-14

Re: java.lang.NoSuchMethodError when using expressions in Dynamic List SQL

Thanks Marcel,

That certainly makes things a lot easier But I am still getting a server error 500 + java.lang.NoSuchMethodError in my logs.

I have the parameter "startDate".
If I set its value to ${today} it works perfectly
If I set its value to ${today.addDays(-1)} I get the exception.

An excerpt from the stacktrace is:
Caused by: java.lang.NoSuchMethodError: javax.el.ELResolver.invoke(Ljavax/el/ELContext;Ljava/lang/Object;Ljava/lang/Object;[Ljava/lang/Class;[Ljava/lang/Object;)Ljava/lang/Object;
    at de.odysseus.el.tree.impl.ast.AstMethod.eval(AstMethod.java:91)
    at de.odysseus.el.tree.impl.ast.AstMethod.eval(AstMethod.java:100)
    at de.odysseus.el.tree.impl.ast.AstEval.eval(AstEval.java:51)
    at de.odysseus.el.tree.impl.ast.AstNode.getValue(AstNode.java:30)
    at de.odysseus.el.TreeValueExpression.getValue(TreeValueExpression.java:122)
    at net.datenwerke.rs.utils.juel.JuelServiceImpl.evaluate(JuelServiceImpl.java:176)
    at net.datenwerke.rs.utils.juel.JuelServiceImpl.evaluate(JuelServiceImpl.java:162)
    at net.datenwerke.rs.utils.juel.SimpleJuel.parseAsObject(SimpleJuel.java:37)
    at net.datenwerke.rs.base.service.parameters.datetime.DateTimeParameterInstance.parseFormula(DateTimeParameterInstance.java:87)
    at net.datenwerke.rs.base.service.parameters.datetime.DateTimeParameterInstance.getSelectedValue(DateTimeParameterInstance.java:77)
    at net.datenwerke.rs.core.service.parameters.entities.ParameterInstance.configureParameterMap(ParameterInstance.java:188)
    at net.datenwerke.rs.core.service.reportmanager.parameters.ParameterSet.getParameterMap(ParameterSet.java:111)
    at net.datenwerke.rs.base.service.dbhelper.querybuilder.ManagedQuery.prepareStatement(ManagedQuery.java:89)
    at net.datenwerke.rs.base.service.datasources.table.impl.TableDBDataSource.open(TableDBDataSource.java:261)
    at net.datenwerke.rs.base.service.reportengines.table.TableReportEngine.createReport(TableReportEngine.java:257)
    at net.datenwerke.rs.base.service.reportengines.table.TableReportEngine.doExecuteNormal(TableReportEngine.java:187)
    at net.datenwerke.rs.base.service.reportengines.table.TableReportEngine.doExecuteNormal(TableReportEngine.java:148)
    at net.datenwerke.rs.base.service.reportengines.table.TableReportEngine.doExecute(TableReportEngine.java:90)
    at net.datenwerke.rs.core.service.reportmanager.engine.ReportEngine.execute(ReportEngine.java:127)
    at net.datenwerke.rs.core.service.reportmanager.ReportExecutorServiceImpl.execute(ReportExecutorServiceImpl.java:186)
    at net.datenwerke.rs.core.service.reportmanager.ReportExecutorServiceImpl.execute(ReportExecutorServiceImpl.java:119)
    at net.datenwerke.rs.core.service.reportmanager.ReportExecutorServiceImpl.execute(ReportExecutorServiceImpl.java:106)
    at net.datenwerke.rs.core.service.reportmanager.ReportExecutorServiceImpl.execute(ReportExecutorServiceImpl.java:89)
    at net.datenwerke.rs.core.server.reportexecutor.ReportExecutorRpcServiceImpl.execute(ReportExecutorRpcServiceImpl.java:381)
    at net.datenwerke.rs.core.server.reportexecutor.ReportExecutorRpcServiceImpl.executeAs(ReportExecutorRpcServiceImpl.java:208)
    at com.google.inject.persist.jpa.JpaLocalTxnInterceptor.invoke(JpaLocalTxnInterceptor.java:66)
    at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:110)
    at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:561)
    ... 33 more

Thorsten J. Krause

Guest

Re: java.lang.NoSuchMethodError when using expressions in Dynamic List SQL

Hi Tristan,

I tried to reproduce this, but wasn't able to. Could you perhaps export the report definition and send it to me?
Use the "Quick Export" button in the Administration view and choose Yes when asked to include variants.

Kind regards,
Thorsten
thorsten.krause@datenwerke.net

tristan

Member

Registered: 2013-11-14

Re: java.lang.NoSuchMethodError when using expressions in Dynamic List SQL

Hi Thorsten

I have mailed you the export file.

Regards,

Tristan