How to view users by Department
I have Custom 3 set to display my users Department. I have a scheduled report that runs monthly showing how many tickets were closed for the last 31 days and which department they were opened by. Here's that report:
SELECT U.CUSTOM_3 AS DEPARTMENT,
COUNT(*) AS CLOSED_TICKETS
FROM HD_TICKET H,
HD_STATUS S,
USER U
WHERE H.HD_STATUS_ID = S.ID
AND H.SUBMITTER_ID = U.ID
AND S.STATE = 'CLOSED'
AND ( H.TIME_CLOSED > (CurDate()-31) )
GROUP BY U.CUSTOM_3
My report does a pretty good job except it has 2 locations listed. Ops Center and Retail Ops. I need to see which users have either of these 2 settings under Custom 3. Is there a report that will show me my users and what is listed under Custom 3?
SELECT U.CUSTOM_3 AS DEPARTMENT,
COUNT(*) AS CLOSED_TICKETS
FROM HD_TICKET H,
HD_STATUS S,
USER U
WHERE H.HD_STATUS_ID = S.ID
AND H.SUBMITTER_ID = U.ID
AND S.STATE = 'CLOSED'
AND ( H.TIME_CLOSED > (CurDate()-31) )
GROUP BY U.CUSTOM_3
My report does a pretty good job except it has 2 locations listed. Ops Center and Retail Ops. I need to see which users have either of these 2 settings under Custom 3. Is there a report that will show me my users and what is listed under Custom 3?
0 Comments
[ + ] Show comments
Answers (4)
Please log in to answer
Posted by:
jmcelvoy
13 years ago
Along with this, or in place of, is there a way when I go under HelpDesk>Users to have a column for their Department...which again is specified by Custom 3?
Posted by:
GillySpy
13 years ago
Do you really want a ticket report or a user report?
If this doesn't match then please include a screenshot mocked up in excel of what you might want the result to look like. I find a picture says a thousand words when it comes to reporting.
SELECT U.USER_NAME, CUSTOM_3
FROM USER U
WHERE CUSTOM_3 <>' /* custom_3 not empty*/
ORDER BY CUSTOM_3
If this doesn't match then please include a screenshot mocked up in excel of what you might want the result to look like. I find a picture says a thousand words when it comes to reporting.
Posted by:
jmcelvoy
13 years ago
I just need to be able to see what users have listed under Custom 3. I have several people that apparently have their location listed under Custom 3 instead of their Department and I need to change that. I'll try this query and see what I get. Thanks.
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.