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.