Video Screencast Help
Search Video Help Close Back
to help

Report - Number of Created Tickets by Worker

Created: 21 Jan 2013 | 3 comments
jasonhopp's picture
jasonhopp
0 0 Votes
Login to vote

Does anyone have a report that shows number of tickets created by worker for a selected date range?  Looking to get information on how many tickets each technician is opening per day/week/month.  Looked at the "stock" reports and really didn't see anything that would give the number of tickets created by each worker.  Thanks.

Discussion Filed Under:
, , ,

Comments 3 Comments • Jump to latest comment

michael.george's picture
michael.george
Report - Number of Created Tickets by Worker - Comment:23 Jan 2013 : Link

I'm not seeing an awesome way to do this in 7.1, but I am sure you could do it using a profile. Before I go through those steps I thought I'd confirm you are using 7.1. For what it's worth, this is a much simpler process in 7.5.

If a post solves your issue, please mark it as a solution. It makes these forums better for everyone.

0
Login to vote
jasonhopp's picture
jasonhopp
Report - Number of Created Tickets by Worker - Comment:23 Jan 2013 : Link

Yes, I am currently running 7.1.2

0
Login to vote
michael.george's picture
michael.george
Report - Number of Created Tickets by Worker - Comment:24 Jan 2013 : Link

 

Okay, so two ways to do this, really. One, do a SQL query. Not nearly as cool, IMHO. Just create a new report and after selecting the data source, add the SQL query as such:
 
SELECT
  COUNT([User].[PrimaryEmail]) AS Count
  ,[User].[PrimaryEmail]
FROM [ReportProcessContact] CONTACT
  JOIN [User] ON CONTACT.[ReferenceID] = [User].[UserID]
  JOIN [ReportProcess] RP ON CONTACT.[SessionID] = RP.[SessionID]
WHERE CONTACT.[ContactType] LIKE '%submitter%'
  AND RP.[ReportProcessID] LIKE 'IM-%'
  AND
  (
    DATEPART(MONTH,RP.[ProcessStarted]) = DATEPART(MONTH, DATEADD(MONTH, -1, getdate()))
    AND
    DATEPART(YEAR,RP.[ProcessStarted]) = DATEPART(YEAR, DATEADD(YEAR, -1, getdate()))
  )
GROUP BY [User].[PrimaryEmail]
 
Kinda boring, but it works I suppose. Way cooler is to do a data profile for ServiceDesk. Credit where credit's due, check out http://www-secure.symantec.com/connect/articles/us... for the full plan. Here's the short version:
 
1. Open up your SQL Server Management Studio and connect to your DB.
2. Find your DB (ProcessManager or whatever yours is called) and right click on 'Views'.
3. Create a new view and close the wizard that appears
4. Drop your required SQL into the box. In this case, I'm using:
 
SELECT CONTACT.SessionID, CONTACT.ContactType, dbo.[User].[PrimaryEmail]
FROM dbo.ReportProcessContact AS CONTACT INNER JOIN
                      dbo.[User] ON CONTACT.ReferenceID = dbo.[User].UserID
WHERE (CONTACT.ContactType LIKE '%submitter%')
 
5. After running it to make sure it works, save the view and make sure you remember the name you gave it.
6. Log into the Process Manager and go to the admin tab.
7. Go under 'Data' to 'Lists and Profiles' and click on the plus icon, selecting 'Add Profile Definition (existing table)'
8. Set the reference type to workflow process, choose a name for the profile definition (I usually use the view name), and enter the view name from step 5 into the table name box.
9. Click 'go'.
10. From the 'Select ID Field' drop down, select 'SessionID'.
11. Check both boxes and click 'Generate'.
12. Create a new report and select your data source.
13. Add Process and Incident Management to the report.
14. At the bottom of the 'Process Management' section on the left, you'll see a 'Workflow Profile' type to add. Add that and you'll get a window that will let you select the name of the Profile definition you created in steps 7-11.
15. Last things to add from the left are the 'Include Process Actions' and 'Started Date'. 'Started Date' will bring up a dialog where you can select the time frame. I tend to go with 'Last week' and 'Is Editable At Runtime'.
16. Select from the right side 'Report Process ID' from 'Process' and 'PrimaryEmail' from your custom definition.
17. On the left, click on 'Options' and set the group by to 'PrimaryEmail'. You may also want to setup paging and row limiting if you plan on this bringing back a lot of data. Don't want to slow things down too much.
 
And that's it. The big difference between doing a data profile and sql report is the abilty to easily add additional data without monkeying with the SQL too much. You also get the ability to have the hot links to the tickets themselves.
 
It should also be noted that this is for incidents only. Problem, change, and any other custom types won't appear here.

If a post solves your issue, please mark it as a solution. It makes these forums better for everyone.

0
Login to vote