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

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

2
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.
Answered 08/30/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
2
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.
Answered 08/30/2010 by: ragena.blankenship
Yellow Belt

Please log in to comment
3
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:
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
Answered 11/05/2010 by: dchristian
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity