ServiceDesk

Hi I have created my own incident report using SQL.  This report shows the specific fields  and filters based on my special needs.  However when I put this report back into the serivice desk, it has removed the hyperlink to the incident.  Does anyone know how to re-add this hyperlink?

I have put the copy of my SQL in to the body here/

SELECT DISTINCT
                      TOP (100) Process.ReportLogProcessID AS [Process.ReportLogProcessID], Process.ReportProcessID AS [Process.ReportProcessID],
                      Process.ProcessStarted AS [Process.ProcessStarted], Process.ProcessTitle AS [Process.ProcessTitle], Process.SessionID AS [Process.SessionID],
                      Process.ProcessViewerPageID AS [Process.ProcessViewerPageID], Task0.TaskID AS [Task.TaskID], Task0.DueDate AS [Task.DueDate],
                      dbo.GetTaskAssignments(Task0.TaskID) AS [Task.TaskAssignments], ReportProcessContact1.ContactType AS [Process_Contacts.ContactType],
                      ServiceDeskIncidentManagement5.process_id AS [Incident.ID], ServiceDeskIncidentManagement5.priority AS [Incident.Priority],
                      ServiceDeskIncidentManagement5.classification_category_01 AS [Incident.Category1],
                      ServiceDeskIncidentManagement5.overall_slastate AS [Incident.SLAStatus], User1.FirstName, User1.LastName, User1.PrimaryEmail
FROM         ReportProcess AS Process INNER JOIN
                      Task AS Task0 ON Task0.SessionID = Process.SessionID INNER JOIN
                      ReportProcessContact AS ReportProcessContact1 ON ReportProcessContact1.SessionID = Process.SessionID LEFT OUTER JOIN
                      [User] AS User1 ON ReportProcessContact1.ReferenceID = User1.UserID LEFT OUTER JOIN
                      [Group] AS Group1 ON ReportProcessContact1.ReferenceID = Group1.GroupID AND ReportProcessContact1.ReferenceType = 2 LEFT OUTER JOIN
                      UserAddress AS UserAddress4 ON User1.UserID = UserAddress4.UserID INNER JOIN
                      ServiceDeskIncidentManagement AS ServiceDeskIncidentManagement5 WITH (NOLOCK) ON
                      ServiceDeskIncidentManagement5.process_id = Process.ReportProcessID
WHERE     (Task0.DoNotShowInTaskList = 'False') AND ('3DCA0263-D5EF-48ae-BF96-A161989DFB8E' = '3DCA0263-D5EF-48ae-BF96-A161989DFB8E') AND
                      (Task0.IsCompleted = 'False') AND (ReportProcessContact1.ContactType = 'Affected User')
ORDER BY [Process.ReportProcessID] DESC

Creating SQL reports in the report designer, instead of using the GUI builder, does drop the 'Include Process Actions' feature that provides the hyperlink.

I haven't found a clean way around this as the reports don't provide drill-down interaction.  If you absolutely need to use this funciton try as best as you can to avoid writing the SQL yourself.

I've read the SQL.  I might be wrong but there doesn't appear to be anything in there that you couldn't do in the GUI and therefore retain the ability to click on the process ID.

Usually I find that I have to write the SQL where I'm trying to do things like arithmetic or putting in obscure where clauses that the GUI doesn't give you the option for but that doesn't appear to be the case here.

Can you describe what you are trying to do?

Thanks for the info Ryan,

However there is one small thing I could not do with the report builder, and that was filter out using the following clause "(ReportProcessContact1.ContactType = 'Affected User')".... 
 

In the report designer add Process Contacts to your report, click on Contact and you will be given the option of searching for a user.  Instead of searching for a specific user, select "Affected User" from the Contact Type drop-down and then click OK (not search) it will then filter the report for Contact Type.

This is great if you are just wanting to filter by one particular contact type as the designer can't cope with OR. 

Instead you can make this Contact box editable at runtime to give the option of changing which Contact Type is being searched for.

It worked for me.

Add this to your select statement and see if it works.  

Could you give more details on how it works? I added href statement to SQL select and got just a string, non-clckable, instead of hyperlink

Thanks