Ticket Survey by Owner
I am trying to get a report that will provide me with the survey results for each technician (owner) for the year (July 1, 2009 - June 30, 2010). I would prefer a graph that shows all techs...I've tried using the report wizard to no avail. SQL is foreign to me. If someone could help I would greatly appreciate it!
0 Comments
[ + ] Show comments
Answers (3)
Please log in to answer
Posted by:
dchristian
14 years ago
hey Ragena,
Try this report.
It will create a pivot table with all the user satisfaction rating results.
All you need to do is dump it to excel and create the graph.
Only between July 1 2009 and June 30 2010:
Without the date restriction:
Try this report.
It will create a pivot table with all the user satisfaction rating results.
All you need to do is dump it to excel and create the graph.
Only between July 1 2009 and June 30 2010:
SELECT Coalesce(FULL_NAME, 'UNASSIGNED') AS NAME,
Max(IF(SATISFACTION_RATING = 1, COUNT, 0)) AS 'POOR',
Max(IF(SATISFACTION_RATING = 2, COUNT, 0)) AS '2',
Max(IF(SATISFACTION_RATING = 3, COUNT, 0)) AS '3',
Max(IF(SATISFACTION_RATING = 4, COUNT, 0)) AS '4',
Max(IF(SATISFACTION_RATING = 5, COUNT, 0)) AS 'EXCELLENT',
Max(IF(SATISFACTION_RATING = 0, COUNT, 0)) AS 'NOT RATED'
FROM (SELECT U.FULL_NAME,
T.SATISFACTION_RATING,
COUNT(*) AS COUNT
FROM HD_TICKET T
LEFT JOIN USER U
ON T.OWNER_ID = U.ID
WHERE ( T.CREATED >= '2009-07-01'
AND T.CREATED <= '2010-06-30' )
GROUP BY U.FULL_NAME,
T.SATISFACTION_RATING) RAW
GROUP BY FULL_NAME
ORDER BY NAME
Without the date restriction:
SELECT Coalesce(FULL_NAME, 'UNASSIGNED') AS NAME,
Max(IF(SATISFACTION_RATING = 1, COUNT, 0)) AS 'POOR',
Max(IF(SATISFACTION_RATING = 2, COUNT, 0)) AS '2',
Max(IF(SATISFACTION_RATING = 3, COUNT, 0)) AS '3',
Max(IF(SATISFACTION_RATING = 4, COUNT, 0)) AS '4',
Max(IF(SATISFACTION_RATING = 5, COUNT, 0)) AS 'EXCELLENT',
Max(IF(SATISFACTION_RATING = 0, COUNT, 0)) AS 'NOT RATED'
FROM (SELECT U.FULL_NAME,
T.SATISFACTION_RATING,
COUNT(*) AS COUNT
FROM HD_TICKET T
LEFT JOIN USER U
ON T.OWNER_ID = U.ID
GROUP BY U.FULL_NAME,
T.SATISFACTION_RATING) RAW
GROUP BY FULL_NAME
ORDER BY NAME
Posted by:
airwolf
14 years ago
If you want a graph, you'll need to use ODBC and something like Crystal Reports or Excel. The XML report generator built into the KBOX is extremely limited and cannot produce graphs.
Posted by:
ragena.blankenship
14 years ago
Thanks..is there a way to get the information I want without the graph? I am having trouble just getting a report that shows how many work orders each tech handled for any given time period.
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.