Dynamic list works...but no reports based on it do

lmr · 2015-12-15 12:05:15

Hi all,

I created a dynamic list which works fine using the following sql.

SELECT ncd.Health_Board as 'Health Board', ncd.Clinic_Name AS 'Clinic', mor.MoR as 'Speciality', MIN(DATE_FORMAT(ncd.Date_of_WMHN_assessment,'%d/%m/%Y')) AS 'First Recruit', MAX(DATE_FORMAT(ncd.Date_of_WMHN_assessment,'%d/%m/%Y')) AS 'Last Recruit', COUNT(*) AS 'Total Recruited'
FROM ncmh_contacts_database ncd
LEFT JOIN ncmh.combobox_method_of_recruitment mor
ON mor.MoR_ID=ncd.Method_of_Recruitment
WHERE ncd.Participation_Status = 'Assessment Complete'
AND ncd.Date_of_WMHN_assessment IS NOT NULL
AND ncd.Date_of_WMHN_assessment <> ''
AND ncd.Clinic_Name <> ''
AND ncd.Health_Board <> ''
AND ncd.Health_Board != 'N/A'
GROUP BY ncd.Health_Board, ncd.Clinic_Name, ncd.Method_of_Recruitment

However, whenever I try to make a report based on any of the columns...the following errors occurs:

Query could not be executed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS xx__rs_col_0, Clinic AS xx__rs_col_1, Speciality AS xx__rs_col_2, First Recru' at line 1

MORE:
Error: An error occured during report execution.
Query could not be executed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS xx__rs_col_0, Clinic AS xx__rs_col_1, Speciality AS xx__rs_col_2, First Recru' at line 1
To get help please contact an administrator.
Details
net.datenwerke.rs.core.service.reportmanager.exceptions.ReportExecutorException: Query could not be executed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS xx__rs_col_0, Clinic AS xx__rs_col_1, Speciality AS xx__rs_col_2, First Recru' at line 1 at net.datenwerke.rs.base.service.datasources.table.impl.TableDBDataSource.open(TableDBDataSource.java:321) at net.datenwerke.rs.base.service.reportengines.table.TableReportEngine.createReport(TableReportEngine.java:254) 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.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$$c981671f.CGLIB$doGet$0() at net.datenwerke.rs.core.server.reportexport.ReportExportServlet$$EnhancerByGuice$$c981671f$$FastClassByGuice$$d195d7a6.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$$c981671f.doGet() at javax.servlet.http.HttpServlet.service(HttpServlet.java:624) at javax.servlet.http.HttpServlet.service(HttpServlet.java:731) at net.datenwerke.rs.core.server.reportexport.ReportExportServlet$$EnhancerByGuice$$c981671f.CGLIB$service$13() at net.datenwerke.rs.core.server.reportexport.ReportExportServlet$$EnhancerByGuice$$c981671f$$FastClassByGuice$$d195d7a6.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$$c981671f.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:505) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:956) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:423) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1079) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:625) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:318) 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: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS xx__rs_col_0, Clinic AS xx__rs_col_1, Speciality AS xx__rs_col_2, First Recru' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:526) at com.mysql.jdbc.Util.handleNewInstance(Util.java:408) at com.mysql.jdbc.Util.getInstance(Util.java:383) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1062) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4226) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4158) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2840) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2212) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:116) at net.datenwerke.rs.base.service.datasources.table.impl.TableDBDataSource.open(TableDBDataSource.java:300) ... 58 more

Any idea why reports don't work based on a working dynamic list?

Thanks in advance!

Arno Mittelbach · 2015-12-15 12:49:52

Hi,

I think the problem are the spaces in the aliases which seem to confuse ReportServer when constructing the actual query used for the report. Could you try the changed query:

SELECT ncd.Health_Board as 'HealthBoard', ncd.Clinic_Name AS 'Clinic', mor.MoR as 'Speciality', MIN(DATE_FORMAT(ncd.Date_of_WMHN_assessment,'%d/%m/%Y')) AS 'FirstRecruit', MAX(DATE_FORMAT(ncd.Date_of_WMHN_assessment,'%d/%m/%Y')) AS 'LastRecruit', COUNT(*) AS 'TotalRecruited'
FROM ncmh_contacts_database ncd
LEFT JOIN ncmh.combobox_method_of_recruitment mor
ON mor.MoR_ID=ncd.Method_of_Recruitment
WHERE ncd.Participation_Status = 'Assessment Complete'
AND ncd.Date_of_WMHN_assessment IS NOT NULL
AND ncd.Date_of_WMHN_assessment <> ''
AND ncd.Clinic_Name <> ''
AND ncd.Health_Board <> ''
AND ncd.Health_Board != 'N/A'
GROUP BY ncd.Health_Board, ncd.Clinic_Name, ncd.Method_of_Recruitment

I've created a bug ticket to look into this further. However, in order to specify default aliases for your columns, you can also use the metadata datasource. For example, point it to the same datasource and add a query like the following

SELECT 'HealthBoard', 'Health Board', 'some description' FROM DUAL
UNION
SELECT 'FirstRecruit', 'First Recruit', 'some description' FROM DUAL

ReportServer here expects a table containing three columns, the first specifying the column name, the second providing a default aliases and the third a description.

Hope this helps.

Cheers
-Arno

lmr · 2015-12-15 12:58:16

Thanks Arno, yes, removing the spaces from the aliases did the trick--the reports now work. Much appreciated.

Arno Mittelbach · 2015-12-15 13:02:13

Glad I could help.

ps. || If you like ReportServer .. we are always looking for reviews ;-)
vv

lmr · 2015-12-16 12:52:44

Sure, no problem Arno, great open source product you have here. I've posted reviews on both sites.

Arno Mittelbach · 2015-12-16 13:06:49

That is great to hear. Thanks a lot.

ReportServer Forum

#1 2015-12-15 12:05:15

Dynamic list works...but no reports based on it do

#2 2015-12-15 12:49:52

Re: Dynamic list works...but no reports based on it do

#3 2015-12-15 12:58:16

Re: Dynamic list works...but no reports based on it do

#4 2015-12-15 13:02:13

Re: Dynamic list works...but no reports based on it do

#5 2015-12-16 12:52:44

Re: Dynamic list works...but no reports based on it do

#6 2015-12-16 13:06:49

Re: Dynamic list works...but no reports based on it do

Board footer