MSSQL query - help needed

MSSQL query - help needed

I have the MSSQL query: closed requests with group "Analityk HD", mode "WebForm" or "e-mail", with more than one change of priority. It works fine in SQL Server management Studio, but it doesn't work in ServiceDesk 8.0.19.

query:
  1. SELECT CAST( SUM(A.changeonly) as FLOAT ) / CAST ( SUM(A.total) as FLOAT ) * 100 '% zgłoszeń z wielokrotną zmianą', A.FINALDATE FROM ( 
  2. SELECT count(DISTINCT WO.WORKORDERID) changeonly, 0 total, CONVERT(date, dateadd( s, CONVERT( INT, FLOOR(wo.COMPLETEDTIME/1000) ),'1970-01-01 00:00:00'), 112) as FINALDATE 
  3. FROM WorkOrder WO 
  4. LEFT JOIN WorkOrderHistory WOH ON WOH.WORKORDERID = WO.WORKORDERID
  5. LEFT JOIN WorkOrderHistoryDiff WOHD ON WOHD.HISTORYDIFFID = WOH.HISTORYID
  6. JOIN ModeDefinition MD ON WO.MODEID = MD.MODEID 
  7. LEFT JOIN WorkOrder_Queue WOQ ON WO.WORKORDERID = WOQ.WORKORDERID 
  8. LEFT JOIN QueueDefinition QD ON WOQ.QUEUEID = QD.QUEUEID 
  9. WHERE (((( MD.MODENAME = N'E-Mail' COLLATE Polish_CI_AS ) OR 
  10. ( MD.MODENAME = N'Web Form' COLLATE Polish_CI_AS ))) AND 
  11. ( QD.QUEUENAME = N'Analityk HD' COLLATE Polish_CI_AS )) AND WOHD.COLUMNNAME = 'PRIORITYID'
  12. AND ( wo.ISPARENT = 1 )
  13. GROUP BY CONVERT(date, dateadd( s, CONVERT( INT, FLOOR(wo.COMPLETEDTIME/1000) ),'1970-01-01 00:00:00'), 112)
  14. HAVING COUNT( WO.WORKORDERID ) > 1
  15. UNION
  16. SELECT 0 changeonly, count( DISTINCT WO.WORKORDERID) total, CONVERT(date, dateadd( s, CONVERT( INT, FLOOR(wo.COMPLETEDTIME/1000) ),'1970-01-01 00:00:00'), 112) as FINALDATE 
  17. FROM WorkOrder WO 
  18. JOIN ModeDefinition MD ON WO.MODEID = MD.MODEID 
  19. LEFT JOIN WorkOrder_Queue WOQ ON WO.WORKORDERID = WOQ.WORKORDERID 
  20. LEFT JOIN QueueDefinition QD ON WOQ.QUEUEID = QD.QUEUEID 
  21. WHERE (((( MD.MODENAME = N'E-Mail' COLLATE Polish_CI_AS ) OR 
  22. ( MD.MODENAME = N'Web Form' COLLATE Polish_CI_AS ))) AND 
  23. ( QD.QUEUENAME = N'Analityk HD' COLLATE Polish_CI_AS ))
  24. AND ( wo.ISPARENT = 1 )
  25. GROUP BY CONVERT(date, dateadd( s, CONVERT( INT, FLOOR(wo.COMPLETEDTIME/1000) ),'1970-01-01 00:00:00'), 112) 
  26. ) A GROUP BY A.FINALDATE ORDER BY A.FINALDATE DESC

errors:

net.sf.jasperreports.engine.design.JRValidationException: Report design not valid : 
1. Field not found : null
2. Field not found : null
3. Class not set for group expression : Bynull
4. Class not set for text field expression.
at net.sf.jasperreports.engine.design.JRAbstractCompiler.verifyDesign(JRAbstractCompiler.java:260)
at net.sf.jasperreports.engine.design.JRAbstractCompiler.compileReport(JRAbstractCompiler.java:144)
at net.sf.jasperreports.engine.JasperCompileManager.compileReport(JasperCompileManager.java:219)
at net.sf.jasperreports.engine.JasperCompileManager.compileReportToFile(JasperCompileManager.java:135)
at com.adventnet.servicedesk.reports.utils.CoreReportDesigner.generateSQLReport(CoreReportDesigner.java:7694)
at com.adventnet.servicedesk.reports.action.ReportRequestHandler.runSQLQueryReport(ReportRequestHandler.java:1595)
at com.adventnet.servicedesk.reports.action.ReportRequestHandler.handleRequest(ReportRequestHandler.java:1890)
at com.adventnet.servicedesk.reports.action.CustomReportHandler.handleRequest(CustomReportHandler.java:176)
at com.adventnet.servicedesk.reports.action.ReportHandler.execute(ReportHandler.java:59)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at com.adventnet.servicedesk.filter.EncodingFilter.doFilter(EncodingFilter.java:116)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.tuckey.web.filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java:350)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at com.adventnet.servicedesk.authentication.NTLMV2CredentialAssociationFilter.doFilter(NTLMV2CredentialAssociationFilter.java:34)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at com.adventnet.servicedesk.authentication.NTLMV2Filter.doFilter(NTLMV2Filter.java:125)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:75)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.jboss.web.tomcat.security.CustomPrincipalValve.invoke(CustomPrincipalValve.java:66)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:158)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at com.adventnet.authentication.CredentialAssociation.invoke(CredentialAssociation.java:75)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at com.adventnet.client.util.LoginParameters.invoke(LoginParameters.java:53)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at com.adventnet.servicedesk.tools.AuthenticateNtlm.invoke(AuthenticateNtlm.java:203)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.authenticator.SingleSignOn.invoke(SingleSignOn.java:444)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Unknown Source)



Can you help me, please?


        New to M365 Manager Plus?
        New to M365 Manager Plus?
          New to RecoveryManager Plus?
          New to RecoveryManager Plus?
            New to Exchange Reporter Plus?
            New to Exchange Reporter Plus?
              New to SharePoint Manager Plus?
              New to SharePoint Manager Plus?
                See all integrations
                New to ADManager Plus?
                See all integrations
                New to ADManager Plus?

                  New to ADSelfService Plus?

                  Start your free trial
                  Start your free trial

                        Related Products