ReportServer Forum

karolina

Member

Registered: 2014-08-09

jxls report & script data source

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

datenwerke

Registered: 2012-02-14

Re: jxls report & script data source

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

Member

Registered: 2014-08-09

Re: jxls report & script data source

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

datenwerke

Registered: 2012-02-14

Re: jxls report & script data source

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

Member

Registered: 2014-08-09

Re: jxls report & script data source

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

datenwerke

Registered: 2012-02-14

Re: jxls report & script data source

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

Member

Registered: 2014-08-09

Re: jxls report & script data source

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

datenwerke

Registered: 2012-02-14

Re: jxls report & script data source

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

Member

Registered: 2015-11-10

Re: jxls report & script data source

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