ServiceDesk

Hi all.

I have a SD 7.1 SP1 environment where we have discovered tickets without assignments, the assignment webpart is empty. I would like to create a report that shows tickets that are missing assignments and I have been playing around with the report builder without any luck.

Can someone help me out with this.

Thanks in advance.

/Patrik

I use this code to find out if I have orphaned tickets:

SELECT [ServiceDeskIncidentManagement_id],
[Process_id],
[incident_name],
[incident_description],
[submitted_by],
[urgency],
[impact],
[date_created],
[close_code]
FROM [ProcessManager].dbo.ServiceDeskIncidentManagement
LEFT JOIN [ProcessManager].dbo.Task ON [ProcessManager].dbo.ServiceDeskIncidentManagement.task_id = [ProcessManager].dbo.task.Taskid
WHERE
[ProcessManager].dbo.task.taskID IS NULL and close_code IS NULL
and process_id not like '%OR%'

All credits to this forum ;-)

Kind regards

Koen

Ignore the last line, I created the "OR-999999" numbering to mark these kind of tickets as orphaned. I like to keep those marked tickets out of my report, hence the last line in the code...

Cheers

Thanks for the effort Koen.

I was more looking for a way to build this in the report builder so I can leave the customer with a report that will show them this.

Any idea on how to do this in the report builder in ServiceDesk?

Cheers.

Click the "add report" icon (white document with green +-sign), choose "Add Standard Report". Select "Select Data Source" and choose default, now you can choose "SQL Query", paste the code above in the sql-field and this should result in a usable report. Don't forget to add permissions to the folder and the report.

Good luck.

Koen

I recieved this query from Toomas at Symantec Support and it worked well. One drawback is that the results are not clickable and apperently this is not possible or really hard to accomplish.

Here's the working query (Thanks Toomas):

SELECT

rp.ReportProcessID AS [Ticket ID],
rp.ProcessName AS [Ticket Title],
rp.Result AS [Status],
rp.PercentComplete AS [Percent Complete],
rp.ProjectName AS [Project Name],
rp.ProcessStarted AS [Started],
t.WFTaskNumber AS [Task Number],
t.AssignedDate AS [Task Assigned Date]
FROM ReportProcess rp
LEFT JOIN Task t ON rp.SessionID=t.SessionID
LEFT JOIN TaskAssignment ta ON t.TaskID=ta.TaskID
WHERE ((rp.Result!='Closed') AND (PercentComplete!='100'))
AND (t.CanBeCompleted!=0 OR t.CanBeCompleted IS NULL)
AND ta.ReferenceID IS NULL
ORDER BY rp.ReportProcessID DESC

I would use something like this:

SELECT
rp.ReportProcessID AS [Ticket ID],
rp.ProcessName AS [Ticket Title],
rp.Result AS [Status],
rp.PercentComplete AS [Percent Complete],
rp.ProjectName AS [Project Name],
rp.ProcessStarted AS [Started],
t.WFTaskNumber AS [Task Number],
t.AssignedDate AS [Task Assigned Date]
FROM ReportProcess rp
LEFT JOIN Task t ON rp.SessionID=t.SessionID
LEFT JOIN TaskAssignment ta ON t.TaskID=ta.TaskID
WHERE ((rp.Result!='Closed') AND (PercentComplete!='100'))
AND (t.CanBeCompleted!=0 OR t.CanBeCompleted IS NULL)
AND ta.ReferenceID IS NULL
ORDER BY rp.ProcessStarted ASC

The SQL query is relatively simple - The result lists Ticket ID, Title, Status, Project, Started Date and Task Number and Assignment Date if applicable. Processes are filtered by Status (Closed and 100% are not shown), further filtered by existence of valid tasks on these processes (tasks marked as completed are excluded) and existence of Task Assignment on these tasks.

Edit:

Following that can also function as a workaround to get Include Process Actions setting working and links to the processes into the report:
https://www-secure.symantec.com/connect/articles/using-custom-sql-servicedesk-report-builder-and-still-use-parameters