If I click on show query for a custom report and I try to run it in the query builder lots of errors come up; link requester detail to requests, link tasks to requests with specific statuses
Not sure if this is a problem. If I click on show query for a custom report, cope the whole query and past it into the query section then try to run it exactly as is in the query builder lots of errors come up:
This is what I copy:
SELECT wo.WORKORDERID "Request ID",aau.FIRST_NAME "Requester",std.STATUSNAME "Request Status",reqf.UDF_CHAR4 "Cost Centre" FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN Requester_Fields reqf ON wo.REQUESTERID=reqf.USERID WHERE (((((((((std.STATUSNAME = N'Assign a new 631/636 nr' COLLATE SQL_Latin1_General_CP1_CI_AS) OR (std.STATUSNAME = N'Awaiting on feedback from TM' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (std.STATUSNAME = N'Extension to be moved back to Cisco and off from OCS' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (std.STATUSNAME = N'Extension to be moved over to Lync' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (std.STATUSNAME = N'Extension to be moved over to OCS' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (std.STATUSNAME = N'TM to advise on what nr is assigned to user' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (std.STATUSNAME = N'TM to check if ext has been moved over & translated' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (std.STATUSNAME = N'TM to check which number is assigned to user' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (std.STATUSNAME = N'TM to delete & re-create & re-translate ext to OCS' COLLATE SQL_Latin1_General_CP1_CI_AS)) AND wot.THD_WOID=wot.WORKORDERID
This is what I get when I try to run this from the query builder:
java.lang.Exception: Only select queries can be executed - 'SELECT wo.WORKORDERID "Request ID",aau.FIRST_NAME "Requester",std.STATUSNAME "Request Status",reqf.UDF_CHAR4 "Cost Centre" FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN Requester_Fields reqf ON wo.REQUESTERID=reqf.USERID WHERE (((((((((std.STATUSNAME = N'Assign a new 631/636 nr' COLLATE SQL_Latin1_General_CP1_CI_AS) OR (std.STATUSNAME = N'Awaiting on feedback from TM' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (std.STATUSNAME = N'Extension to be moved back to Cisco and off from OCS' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (std.STATUSNAME = N'Extension to be moved over to Lync' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (std.STATUSNAME = N'Extension to be moved over to OCS' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (std.STATUSNAME = N'TM to advise on what nr is assigned to user' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (std.STATUSNAME = N'TM to check if ext has been moved over & translated' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (std.STATUSNAME = N'TM to check which number is assigned to user' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (std.STATUSNAME = N'TM to delete & re-create & re-translate ext to OCS' COLLATE SQL_Latin1_General_CP1_CI_AS)) AND wot.THD_WOID=wot.WORKORDERID'
at com.adventnet.servicedesk.reports.utils.CoreReportDesigner.generateSQLReport(CoreReportDesigner.java:7354)
at com.adventnet.servicedesk.reports.action.ReportRequestHandler.runSQLQueryReport(ReportRequestHandler.java:1572)
at com.adventnet.servicedesk.reports.action.ReportRequestHandler.handleRequest(ReportRequestHandler.java:1890)
at com.adventnet.servicedesk.reports.action.CustomReportHandler.handleRequest(CustomReportHandler.java:169)
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.ThreadLocalFilter.doFilter(ThreadLocalFilter.java:108)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at com.adventnet.servicedesk.filter.EncodingFilter.doFilter(EncodingFilter.java:109)
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:35)
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:138)
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:285)
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)
What I basically want to do is, use this custom report and just add the user's telephone number " AaaContactInfo.LANDLIN" into the report just before the "Cost Centre" column. I have tried it, but the custom report query just doesnt want to run from the query builder.
It needs to list the following:
Request ID
Requester
Request Status (specific statuses as indicated in the custom query above)
Cost Centre (this is an additional field reqf.UDF_CHAR4)
Requester telephone number (AaaContactInfo.LANDLIN)
If the query could run from the query builder, I could add the telephone number myself, but it just doesnt want to run, I can only access it from the custom reports.
The other report I want, is to add the task titles & task scheduled start dates & Task statuses from the tickets generated in the below query:
SELECT wo.WORKORDERID "Request ID",aau.FIRST_NAME "Requester",std.STATUSNAME "Request Status" FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE (((std.STATUSNAME = N'AD to disable account' COLLATE SQL_Latin1_General_CP1_CI_AS) OR (std.STATUSNAME = N'AD to enable account' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (std.STATUSNAME = N'AD to remove nr from EV & UM' COLLATE SQL_Latin1_General_CP1_CI_AS)) AND wot.THD_WOID=wot.WORKORDERID
This query runs when I copy & paste it into the query builder as is, but I dont know how to link the tasks. I have successfully created reports where I have linked the tasks to each ticket as well as requester, but I cannot tailor it into this specific report.
It needs to list the following:
Request ID
Requester
Request Status (specific statuses as indicated in the custom query above)
Task Title
Scheduled Start Time
Task Status
Most items on the forum give queries to generate reports of requests for certain criteria dates etc. &but not for specific statuses only. I have tried several queries from the forum which I have also edited, but I just cant seem to win this one.
This is what I copy:
SELECT wo.WORKORDERID "Request ID",aau.FIRST_NAME "Requester",std.STATUSNAME "Request Status",reqf.UDF_CHAR4 "Cost Centre" FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN Requester_Fields reqf ON wo.REQUESTERID=reqf.USERID WHERE (((((((((std.STATUSNAME = N'Assign a new 631/636 nr' COLLATE SQL_Latin1_General_CP1_CI_AS) OR (std.STATUSNAME = N'Awaiting on feedback from TM' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (std.STATUSNAME = N'Extension to be moved back to Cisco and off from OCS' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (std.STATUSNAME = N'Extension to be moved over to Lync' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (std.STATUSNAME = N'Extension to be moved over to OCS' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (std.STATUSNAME = N'TM to advise on what nr is assigned to user' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (std.STATUSNAME = N'TM to check if ext has been moved over & translated' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (std.STATUSNAME = N'TM to check which number is assigned to user' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (std.STATUSNAME = N'TM to delete & re-create & re-translate ext to OCS' COLLATE SQL_Latin1_General_CP1_CI_AS)) AND wot.THD_WOID=wot.WORKORDERID
This is what I get when I try to run this from the query builder:
java.lang.Exception: Only select queries can be executed - 'SELECT wo.WORKORDERID "Request ID",aau.FIRST_NAME "Requester",std.STATUSNAME "Request Status",reqf.UDF_CHAR4 "Cost Centre" FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN Requester_Fields reqf ON wo.REQUESTERID=reqf.USERID WHERE (((((((((std.STATUSNAME = N'Assign a new 631/636 nr' COLLATE SQL_Latin1_General_CP1_CI_AS) OR (std.STATUSNAME = N'Awaiting on feedback from TM' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (std.STATUSNAME = N'Extension to be moved back to Cisco and off from OCS' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (std.STATUSNAME = N'Extension to be moved over to Lync' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (std.STATUSNAME = N'Extension to be moved over to OCS' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (std.STATUSNAME = N'TM to advise on what nr is assigned to user' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (std.STATUSNAME = N'TM to check if ext has been moved over & translated' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (std.STATUSNAME = N'TM to check which number is assigned to user' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (std.STATUSNAME = N'TM to delete & re-create & re-translate ext to OCS' COLLATE SQL_Latin1_General_CP1_CI_AS)) AND wot.THD_WOID=wot.WORKORDERID'
at com.adventnet.servicedesk.reports.utils.CoreReportDesigner.generateSQLReport(CoreReportDesigner.java:7354)
at com.adventnet.servicedesk.reports.action.ReportRequestHandler.runSQLQueryReport(ReportRequestHandler.java:1572)
at com.adventnet.servicedesk.reports.action.ReportRequestHandler.handleRequest(ReportRequestHandler.java:1890)
at com.adventnet.servicedesk.reports.action.CustomReportHandler.handleRequest(CustomReportHandler.java:169)
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.ThreadLocalFilter.doFilter(ThreadLocalFilter.java:108)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at com.adventnet.servicedesk.filter.EncodingFilter.doFilter(EncodingFilter.java:109)
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:35)
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:138)
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:285)
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)
What I basically want to do is, use this custom report and just add the user's telephone number " AaaContactInfo.LANDLIN" into the report just before the "Cost Centre" column. I have tried it, but the custom report query just doesnt want to run from the query builder.
It needs to list the following:
Request ID
Requester
Request Status (specific statuses as indicated in the custom query above)
Cost Centre (this is an additional field reqf.UDF_CHAR4)
Requester telephone number (AaaContactInfo.LANDLIN)
If the query could run from the query builder, I could add the telephone number myself, but it just doesnt want to run, I can only access it from the custom reports.
The other report I want, is to add the task titles & task scheduled start dates & Task statuses from the tickets generated in the below query:
SELECT wo.WORKORDERID "Request ID",aau.FIRST_NAME "Requester",std.STATUSNAME "Request Status" FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE (((std.STATUSNAME = N'AD to disable account' COLLATE SQL_Latin1_General_CP1_CI_AS) OR (std.STATUSNAME = N'AD to enable account' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (std.STATUSNAME = N'AD to remove nr from EV & UM' COLLATE SQL_Latin1_General_CP1_CI_AS)) AND wot.THD_WOID=wot.WORKORDERID
This query runs when I copy & paste it into the query builder as is, but I dont know how to link the tasks. I have successfully created reports where I have linked the tasks to each ticket as well as requester, but I cannot tailor it into this specific report.
It needs to list the following:
Request ID
Requester
Request Status (specific statuses as indicated in the custom query above)
Task Title
Scheduled Start Time
Task Status
Most items on the forum give queries to generate reports of requests for certain criteria dates etc. &but not for specific statuses only. I have tried several queries from the forum which I have also edited, but I just cant seem to win this one.