Query report problem

Hi, thanks for looking at my issue.

the below report is exactly what im look for however i error when running this im running version 8201 and MSSQL

at the bottom of this post is my error message.

KPI report - group,technician

in ServiceDesk Plus» Query Report-KPI Reporting • 2 years ago

Technician

Group

Total Number of calls

Total Closed Calls

Closed within SLA

Closed exceeding SLA

Percentage within SLA

Percentage exceeding SLA

Date limit : for the current month

Tested in 8011

DB :mysql /mssql

SELECT au.FIRST_NAME 'Technician', qd2.QUEUENAME "Group", (SELECT COUNT(wo4.WORKORDERID) FROM Workorder_Threaded wot4 INNER JOIN workorder wo4 ON wo4.WORKORDERID=wot4.WORKORDERID LEFT JOIN WorkOrderStates wos4 ON wos4.WORKORDERID=wo4.WORKORDERID LEFT JOIN WorkOrder_Queue woq4 ON wo4.WORKORDERID=woq4.WORKORDERID LEFT JOIN QueueDefinition qd4 ON woq4.QUEUEID=qd4.QUEUEID WHERE (wot4.THD_WOID=wot4.WORKORDERID) AND wo4.CREATEDTIME >= <from_thismonth> AND wo4.CREATEDTIME <= <to_thismonth> AND wos4.OWNERID=(wos0.OWNERID) AND woq4.QUEUEID=(woq2.QUEUEID)) 'Total Number of calls',(SELECT COUNT(wo.WORKORDERID) FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID WHERE std.STATUSNAME = 'Closed' AND (wot.THD_WOID=wot.WORKORDERID) AND wo.COMPLETEDTIME >= <from_thismonth> AND wo.COMPLETEDTIME <= <to_thismonth> AND wos.OWNERID=(wos0.OWNERID) AND woq.QUEUEID=(woq2.QUEUEID))'Total Closed Calls',(SELECT COUNT(wo3.WORKORDERID) FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo3 ON wot.WORKORDERID=wo3.WORKORDERID LEFT JOIN WorkOrderStates wos ON wo3.WORKORDERID=wos.WORKORDERID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN WorkOrder_Queue woq3 ON wo3.WORKORDERID=woq3.WORKORDERID LEFT JOIN QueueDefinition qd3 ON woq3.QUEUEID=qd3.QUEUEID WHERE std.STATUSNAME = 'Closed' AND wos.ISOVERDUE = '0' AND wo3.COMPLETEDTIME >= <from_thismonth> AND wo3.COMPLETEDTIME <= <to_thismonth> AND wot.THD_WOID=wot.WORKORDERID AND wos.OWNERID=(wos0.OWNERID) AND woq3.QUEUEID=(woq2.QUEUEID)) 'Closed within SLA', (SELECT COUNT(wo3.WORKORDERID) FROM WorkOrder_Threaded wot3 INNER JOIN WorkOrder wo3 ON wot3.WORKORDERID=wo3.WORKORDERID LEFT JOIN WorkOrderStates wos3 ON wo3.WORKORDERID=wos3.WORKORDERID LEFT JOIN StatusDefinition std3 ON wos3.STATUSID=std3.STATUSID LEFT JOIN WorkOrder_Queue woq3 ON wo3.WORKORDERID=woq3.WORKORDERID LEFT JOIN QueueDefinition qd3 ON woq3.QUEUEID=qd3.QUEUEID WHERE std3.STATUSNAME = 'Closed' AND wos3.ISOVERDUE = '1' AND wot3.THD_WOID=wot3.WORKORDERID AND wo3.COMPLETEDTIME >= <from_thismonth> AND wo3.COMPLETEDTIME <= <to_thismonth> AND wos3.OWNERID=(wos0.OWNERID) AND woq3.QUEUEID=(woq2.QUEUEID)) 'Closed exceeding SLA',CASE WHEN (SELECT COUNT(wo.WORKORDERID) FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE std.STATUSNAME = 'Closed' AND (wot.THD_WOID=wot.WORKORDERID) AND wo.COMPLETEDTIME >= <from_thismonth> AND wo.COMPLETEDTIME <= <to_thismonth> AND wos.OWNERID=(wos0.OWNERID)) >0 THEN (SELECT count(wo.workorderid) FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID WHERE (( std.STATUSNAME = 'Closed') OR ( std.STATUSNAME = 'Resolved')) and wot.THD_WOID=wot.WORKORDERID and wo.CREATEDTIME >= <from_thismonth> AND wo.CREATEDTIME <= <to_thismonth> AND wos.OWNERID=(wos0.OWNERID) AND woq.QUEUEID=(woq2.QUEUEID)) * 100 / (select count(wo4.workorderid) from workorder_threaded wot4 INNER JOIN workorder wo4 on wo4.workorderid=wot4.workorderid left join workorderstates wos4 on wos4.workorderid=wo4.workorderid LEFT JOIN WorkOrder_Queue woq4 ON wo4.WORKORDERID=woq4.WORKORDERID LEFT JOIN QueueDefinition qd4 ON woq4.QUEUEID=qd4.QUEUEID WHERE (wot4.THD_WOID=wot4.WORKORDERID) and wo4.CREATEDTIME >= <from_thismonth> AND wo4.CREATEDTIME <= <to_thismonth> and wos4.OWNERID=(wos0.OWNERID) AND woq4.QUEUEID=(woq2.QUEUEID)) ELSE 0 END 'Percentage within SLA',CASE WHEN (SELECT COUNT(wo.WORKORDERID) FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE std.STATUSNAME = 'Closed' AND (wot.THD_WOID=wot.WORKORDERID) AND wo.COMPLETEDTIME >= <from_thismonth> AND wo.COMPLETEDTIME <= <to_thismonth> AND wos.OWNERID=(wos0.OWNERID)) >0 THEN (SELECT count(wo3.WORKORDERID) FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo3 ON wot.WORKORDERID=wo3.WORKORDERID LEFT JOIN WorkOrderStates wos ON wo3.WORKORDERID=wos.WORKORDERID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN WorkOrder_Queue woq3 ON wo3.WORKORDERID=woq3.WORKORDERID LEFT JOIN QueueDefinition qd3 ON woq3.QUEUEID=qd3.QUEUEID WHERE (( std.STATUSNAME = 'Closed') OR ( std.STATUSNAME = 'Resolved')) AND ((wos.ISOVERDUE = '1') OR (wos.ISOVERDUE IS NULL)) and wot.THD_WOID=wot.WORKORDERID and wo3.CREATEDTIME >= <from_thismonth> AND wo3.CREATEDTIME <= <to_thismonth> and wos.OWNERID=(wos0.OWNERID) AND woq3.QUEUEID=(woq2.QUEUEID)) * 100 / (select count(wo4.workorderid) from workorder_threaded wot4 INNER JOIN workorder wo4 on wo4.workorderid=wot4.workorderid left join workorderstates wos4 on wos4.workorderid=wo4.workorderid LEFT JOIN WorkOrder_Queue woq4 ON wo4.WORKORDERID=woq4.WORKORDERID LEFT JOIN QueueDefinition qd4 ON woq4.QUEUEID=qd4.QUEUEID WHERE (wot4.THD_WOID=wot4.WORKORDERID) and wo4.CREATEDTIME >= <from_thismonth> AND wo4.CREATEDTIME <= <to_thismonth> and wos4.OWNERID=(wos0.OWNERID) AND woq4.QUEUEID=(woq2.QUEUEID)) ELSE 0 END 'Percentage exceeding SLA' FROM WorkOrder_Threaded wot2 INNER JOIN WorkOrder wo2 ON wot2.WORKORDERID=wo2.WORKORDERID LEFT JOIN WorkOrderStates wos0 ON wos0.workorderid=wo2.workorderid LEFT JOIN AaaUser au ON au.user_id=wos0.ownerid LEFT JOIN WorkOrder_Queue woq2 ON wo2.WORKORDERID=woq2.WORKORDERID LEFT JOIN QueueDefinition qd2 ON woq2.QUEUEID=qd2.QUEUEID WHERE (wot2.THD_WOID=wot2.WORKORDERID) AND wos0.OWNERID IS NOT NULL and wo2.CREATEDTIME >= <from_thismonth> AND wo2.CREATEDTIME <= <to_thismonth> GROUP BY qd2.QUEUENAME ,au.first_name,wos0.OWNERID, woq2.QUEUEID

regards,

Rachana

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

i get:

java.sql.SQLException: Divide by zero error encountered.

at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)

at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)

at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)

at net.sourceforge.jtds.jdbc.TdsCore.getNextRow(TdsCore.java:761)

at net.sourceforge.jtds.jdbc.MSCursorResultSet.processOutput(MSCursorResultSet.java:946)

at net.sourceforge.jtds.jdbc.MSCursorResultSet.cursorCreate(MSCursorResultSet.java:541)

at net.sourceforge.jtds.jdbc.MSCursorResultSet.<init>(MSCursorResultSet.java:154)

at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:365)

at net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1258)

at org.jboss.resource.adapter.jdbc.WrappedStatement.executeQuery(WrappedStatement.java:250)

at com.adventnet.servicedesk.reports.utils.CoreReportDesigner.generateSQLReport(CoreReportDesigner.java:7623)

at com.adventnet.servicedesk.reports.action.ReportRequestHandler.runSQLQueryReport(ReportRequestHandler.java:1639)

at com.adventnet.servicedesk.reports.action.ReportRequestHandler.handleRequest(ReportRequestHandler.java:1957)

at com.adventnet.servicedesk.reports.action.CustomReportHandler.handleRequest(CustomReportHandler.java:175)

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:162)

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:30)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)

at SSO.http.HttpSecurityService.doFilter(HttpSecurityService.java:982)

at com.adventnet.servicedesk.authentication.NTLMV2Filter.doFilter(NTLMV2Filter.java:190)

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)