/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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   [ - ] Hide 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.
Answer this question or Comment on this question for clarity

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