jxls report & script data source

karolina · May 8, 2015, 1:13pm

Hi,

Quick question - Is it possible to create a jxls report with a script data source?
If so, how to get table names?

Cheers,
Karolina

Arno_Mittelbach · May 8, 2015, 7:43pm

Hi Karolina,

I haven’t tried this, but if I have it correctly, then you should have the replacement ‘tableName’ which you can use as any other parameter. If so, then

${rm.exec('SELECT * FROM ' + parameters.tableName)}

should do the trick.

Cheers
-Arno

karolina · May 8, 2015, 8:14pm

Hi Arno,

I’ll definitely try it and let you know the results.
What I don’t know is how the table names in script datasources
are created.

Cheers,
Karolina

Arno_Mittelbach · May 8, 2015, 8:35pm

Currently they are generated as a type of UID. In particular the table name may over time as the table is only held in an in-memory database. So the only way to go with is to use the replacement “tableName” within the query.

Cheers
-Arno

karolina · May 8, 2015, 10:46pm

Hi Arno,

The template is like that:

<jx:forEach items="${rm.exec('SELECT * FROM ' + parameters.tableName)}" var="column">
${column.ds_name}
</jx:forEach>

And the result:

Błąd: Wystąpił błąd podczas wykonywania raportu.
Nie można wykonać raportu: net.datenwerke.rs.core.service.reportmanager.exceptions.ReportExecutorException: Nie można wykonać raportu: Can't parse an expression rm.exec('SELECT * FROM ' + parameters.tableName)
Aby uzyskać pomoc prosimy o kontakt z administratorem.
Szczegóły
net.datenwerke.rs.core.service.reportmanager.exceptions.ReportExecutorException: Nie można wykonać raportu: net.datenwerke.rs.core.service.reportmanager.exceptions.ReportExecutorException: Nie można wykonać raportu: Can't parse an expression rm.exec('SELECT * FROM ' + parameters.tableName) at net.datenwerke.rs.core.service.reportmanager.ReportExecutorServiceImpl.execute(ReportExecutorServiceImpl.java:218) at net.datenwerke.rs.core.service.reportmanager.ReportExecutorServiceImpl.execute(ReportExecutorServiceImpl.java:119) at net.datenwerke.rs.core.server.reportexport.ReportExportServlet.exeucteReport(ReportExportServlet.java:527) at net.datenwerke.rs.core.server.reportexport.ReportExportServlet.doExportReport(ReportExportServlet.java:402) at net.datenwerke.rs.core.server.reportexport.ReportExportServlet.exportReport(ReportExportServlet.java:333) at net.datenwerke.rs.core.server.reportexport.ReportExportServlet.exportReportViaSession(ReportExportServlet.java:326) at net.datenwerke.rs.core.server.reportexport.ReportExportServlet.doGet(ReportExportServlet.java:164) at net.datenwerke.rs.core.server.reportexport.ReportExportServlet$$EnhancerByGuice$$420df46c.CGLIB$doGet$0() at net.datenwerke.rs.core.server.reportexport.ReportExportServlet$$EnhancerByGuice$$420df46c$$FastClassByGuice$$4230acce.invoke() at com.google.inject.internal.cglib.proxy.$MethodProxy.invokeSuper(MethodProxy.java:228) at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72) at com.google.inject.persist.jpa.JpaLocalTxnInterceptor.invoke(JpaLocalTxnInterceptor.java:66) at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72) at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:110) at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72) at com.google.inject.internal.InterceptorStackCallback.intercept(InterceptorStackCallback.java:52) at net.datenwerke.rs.core.server.reportexport.ReportExportServlet$$EnhancerByGuice$$420df46c.doGet() at javax.servlet.http.HttpServlet.service(HttpServlet.java:620) at javax.servlet.http.HttpServlet.service(HttpServlet.java:727) at net.datenwerke.rs.core.server.reportexport.ReportExportServlet$$EnhancerByGuice$$420df46c.CGLIB$service$13() at net.datenwerke.rs.core.server.reportexport.ReportExportServlet$$EnhancerByGuice$$420df46c$$FastClassByGuice$$4230acce.invoke() at com.google.inject.internal.cglib.proxy.$MethodProxy.invokeSuper(MethodProxy.java:228) at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72) at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:110) at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72) at com.google.inject.internal.InterceptorStackCallback.intercept(InterceptorStackCallback.java:52) at net.datenwerke.rs.core.server.reportexport.ReportExportServlet$$EnhancerByGuice$$420df46c.service() at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:263) at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:178) at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91) at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:62) at com.google.inject.persist.PersistFilter.doFilter(PersistFilter.java:89) at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:163) at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58) at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118) at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1070) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:745) Caused by: net.datenwerke.rs.core.service.reportmanager.exceptions.ReportExecutorRuntimeException: net.datenwerke.rs.core.service.reportmanager.exceptions.ReportExecutorException: Nie można wykonać raportu: Can't parse an expression rm.exec('SELECT * FROM ' + parameters.tableName) at net.datenwerke.rs.jxlsreport.service.jxlsreport.reportengine.JxlsReportEngine.executeReport(JxlsReportEngine.java:73) at net.datenwerke.rs.jxlsreport.service.jxlsreport.reportengine.JxlsReportEngine.doExecute(JxlsReportEngine.java:58) 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) ... 52 more Caused by: net.datenwerke.rs.core.service.reportmanager.exceptions.ReportExecutorException: Nie można wykonać raportu: Can't parse an expression rm.exec('SELECT * FROM ' + parameters.tableName) at net.datenwerke.rs.jxlsreport.service.jxlsreport.reportengine.output.generator.JxlsOutputGeneratorImpl.processWorkbook(JxlsOutputGeneratorImpl.java:103) at net.datenwerke.rs.jxlsreport.service.jxlsreport.reportengine.output.generator.JxlsHTMLOutputGenerator.exportReport(JxlsHTMLOutputGenerator.java:45) at net.datenwerke.rs.jxlsreport.service.jxlsreport.reportengine.JxlsReportEngine.executeReport(JxlsReportEngine.java:71) ... 55 more Caused by: java.lang.RuntimeException: Can't parse an expression rm.exec('SELECT * FROM ' + parameters.tableName) at net.sf.jxls.tag.ForEachTag.parseItemsProperty(ForEachTag.java:141) at net.sf.jxls.tag.ForEachTag.init(ForEachTag.java:101) at net.sf.jxls.parser.CellParser.parseTag(CellParser.java:300) at net.sf.jxls.parser.CellParser.parseCellValue(CellParser.java:202) at net.sf.jxls.parser.CellParser.parseCell(CellParser.java:56) at net.sf.jxls.transformer.SheetTransformer.parseCells(SheetTransformer.java:204) at net.sf.jxls.transformer.SheetTransformer.parseRow(SheetTransformer.java:275) at net.sf.jxls.transformer.SheetTransformer.transformSheet(SheetTransformer.java:80) at net.sf.jxls.transformer.XLSTransformer.transformWorkbook(XLSTransformer.java:248) at net.sf.jxls.transformer.XLSTransformer.transformXLS(XLSTransformer.java:221) at net.datenwerke.rs.jxlsreport.service.jxlsreport.reportengine.output.generator.JxlsOutputGeneratorImpl.processWorkbook(JxlsOutputGeneratorImpl.java:101) ... 57 more Caused by: java.lang.NullPointerException at net.sf.jxls.tag.ForEachTag.parseItemsProperty(ForEachTag.java:135) ... 67 more

Did I mess sth up (sorry, I can’t make the stacktrace wrap)?

Cheers,
karolina

Arno_Mittelbach · May 10, 2015, 9:23am

Hey,

I have to eat my words. There was an implementation of a hook missing which currently prevents JXLS reports from using Script datasources. I’ve fixed this and it should be available in one of the next builds. (The name of the replacement will then be _RS_TABLENAME to be more consistent with other automatically generated parameters.)

Cheers
-Arno

karolina · May 10, 2015, 10:04am

Hi Arno,

I hope they are tasty

So, if I get it correctly, in my case it will be:

<jx:forEach items="${rm.exec('SELECT * FROM ' + parameters._RS_TABLENAME)}" var="column">
${column.ds_name}
</jx:forEach>

?

Will this version

<jx:forEach items="${rm.exec('SELECT * FROM ?', parameters._RS_TABLENAME)}" var="column">
${column.ds_name}
</jx:forEach>

work too?

Thanks for the fix and I’m looking forward to getting the build.

Cheers,
Karolina

Arno_Mittelbach · May 10, 2015, 10:24am

Hi Karolina,

the second version won’t work in this case. Behind the scenes, the second version uses Java’s mechanism on setting variables for sql queries and regrettably Java does not support the
table name to be replaced by a variable. This means that it has to be constructed using string concatenation (i.e., first version).

Cheers
-Arno

Glandike · December 11, 2015, 8:40am

Hi!
Continuing the question, if there any way to add in jxls dynamic parameter with multiple value, such as $X{IN, column_name, parameter} ? I tried a lot options and tested any combinations, but they doesn’t work =(