Video Screencast Help
Give us your opinion and win with Symantec! Please help us by taking this survey to tell us about your experience with Symantec Connect, so that we can continue to grow and improve.  Take the survey.

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.