Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

Service Desk Owner Report

Created: 09 Nov 2012

OK I may be over complicating this, but I can't seem to figure it out. Is there a way to use the report writer in ServiceDesk (7.1 SP2) to give me a list of tasks with owner? The reason i can't figure this out is i want one report with an owner column if the task has an owner give me the owners name if not leave the owner cell blank. I wrote a SQL Query that gives me what i want, but the report engine in ServiceDesk will not allow me to use Insert or Exec. Maybe there is another way to do it, but the SQL is below:

Declare @TempIDs Table
([ID] nvarchar (100),
 [Name] nvarchar(500),
 [Priority] nvarchar(100),
 [Age] datetime,
 [Contact] nvarchar(100),
 [Contact Type] nvarchar(100)
);
 
Insert into @TempIDs
select distinct
    T.WFTaskNumberPrefix as [ID],
    T.Name as [Name],
    P.PriorityName as [Priority],
    T.AssignedDate as [Age],
    CAST(User6.PrimaryEmail AS nvarchar(max)) as [Contact],
    ReportProcessContact6.ContactType as [Contact Type] 
From ProcessManager.dbo.ReportProcess as P with (nolock) inner join
ProcessManager.dbo.Task as T with (nolock) on
t.SessionID = p.SessionID inner join
ProcessManager.dbo.TaskAssignment as TA with (nolock) on
TA.TaskID = T.TaskID inner join
ProcessManager.dbo.UserReferenceIDLookup as [Lookup] with (nolock) on
[Lookup].ReferenceID = ta.ReferenceID inner join
ProcessManager.dbo.[User] as U with (nolock) on
[Lookup].UserID = u.UserID inner join
ProcessManager.dbo.[Group] as G with (nolock) on
G.GroupID = TA.ReferenceID left join
ProcessManager.dbo.TaskResponse as TR with (NOLOCK) on
        ((TR.TaskID = T.TaskID and
(select count(*) c from ProcessManager.dbo.TaskResponse where TaskResponse.TaskID = T.TaskID) < 2)) inner join
    ProcessManager.dbo.ReportProcessContact as ReportProcessContact6 with (NOLOCK) on
        ((ReportProcessContact6.SessionID = P.SessionID)) left outer join
    ProcessManager.dbo.[User] as User6 with (NOLOCK) on
        ((ReportProcessContact6.ReferenceID = User6.UserID)) left outer join
    ProcessManager.dbo.[Group] as Group6 with (NOLOCK) on
        ((ReportProcessContact6.ReferenceID = Group6.GroupID and
    ReportProcessContact6.ReferenceType = 2))
where
    (T.DoNotShowInTaskList = 'False' and
    (([Lookup].UserID = 'c4d028e7-93d3-11e1-abff-005056a81a4c' and
    (TA.ReferenceType = 2))) and
    T.IsCompleted = 'False')
 
select distinct
    T.WFTaskNumberPrefix as [ID],
    T.Name as [Name],
    P.PriorityName as [Priority],
    T.AssignedDate as [Age],
    CAST(User6.PrimaryEmail AS nvarchar(max)) as [Owner]
From ProcessManager.dbo.ReportProcess as P with (nolock) inner join
ProcessManager.dbo.Task as T with (nolock) on
t.SessionID = p.SessionID inner join
ProcessManager.dbo.TaskAssignment as TA with (nolock) on
TA.TaskID = T.TaskID inner join
ProcessManager.dbo.UserReferenceIDLookup as [Lookup] with (nolock) on
[Lookup].ReferenceID = ta.ReferenceID inner join
ProcessManager.dbo.[User] as U with (nolock) on
[Lookup].UserID = u.UserID inner join
ProcessManager.dbo.[Group] as G with (nolock) on
G.GroupID = TA.ReferenceID left join
ProcessManager.dbo.TaskResponse as TR with (NOLOCK) on
        ((TR.TaskID = T.TaskID and
(select count(*) c from ProcessManager.dbo.TaskResponse where TaskResponse.TaskID = T.TaskID) < 2)) inner join
    ProcessManager.dbo.ReportProcessContact as ReportProcessContact6 with (NOLOCK) on
        ((ReportProcessContact6.SessionID = P.SessionID and
    ReportProcessContact6.ContactType = 'Owner')) left outer join
    ProcessManager.dbo.[User] as User6 with (NOLOCK) on
        ((ReportProcessContact6.ReferenceID = User6.UserID)) left outer join
    ProcessManager.dbo.[Group] as Group6 with (NOLOCK) on
        ((ReportProcessContact6.ReferenceID = Group6.GroupID and
    ReportProcessContact6.ReferenceType = 2))
where
    (T.DoNotShowInTaskList = 'False' and
    (([Lookup].UserID = 'c4d028e7-93d3-11e1-abff-005056a81a4c' and
    (TA.ReferenceType = 2))) and
    T.IsCompleted = 'False')
Union
Select Distinct ID,
Name,
Priority,
Age,
[Owner] = ''
From @TempIDs
Where ID not in (Select ID
From @TempIDs
Where [Contact Type] = 'Owner')
order by [ID] asc
 
 
Any help would be appreciated.