[Report] Helpdesk: All tickets whose current state is not Closed

[hatrantator]

Hello there!

We use the report 'Helpdesk: All tickets whose current state is not Closed' with a slightly changed format to print out as an overview for internal meetings.

The problem i have:
When i put my name in the filter for the agent, it gives me every ticket where i am the Agent.
How do I change it so it also shows me the tickets where i am the secondary agent, without using another filter/column?

My version of the report:

Select Top 1000000 htblticket.ticketid,
'#' + Cast(htblticket.ticketid As nvarchar) As Ticket,
htblticket.subject As Betreff,
htblusers.name As Benutzer,
htblticketstates.statename As Status,
htblusers1.name As Agent,
htblticket.date As Erstellt,
htblticket.updated As Bearbeitet,
'../helpdesk/icons/' + htbltickettypes.icon As icon
From htblticket
Inner Join htblpriorities On htblpriorities.priority = htblticket.priority
Inner Join htblticketstates On htblticketstates.ticketstateid =
htblticket.ticketstateid
Inner Join htblusers On htblusers.userid = htblticket.fromuserid
Left Join htblagents On htblagents.agentid = htblticket.agentid
Left Join htblusers htblusers1 On htblusers1.userid = htblagents.userid
Inner Join htbltickettypes On htblticket.tickettypeid =
htbltickettypes.tickettypeid
Inner Join htblsource On htblticket.sourceid = htblsource.sourceid
Inner Join htblusers htblusers2 On htblticket.userid_lastnote =
htblusers2.userid
Where htblticketstates.statename <> 'Closed' And htblticket.spam <> 'True'
Order By htblticket.ticketid

[hatrantator]

I am actually just looking for a way to print out an overview of all my tickets without too much of a hassle.

[hatrantator]

Thank you for your reply.

What I was really looking for is a report that combines 'Helpdesk: All tickets whose current state is not Closed' and the one you gave me right now.

Example:
'Helpdesk: All tickets whose current state is not Closed' gives me 20 tickets where I am the agent.
Your report gives me another 15 tickets where I am subscribed as secondary agent.
I want to see the all 35 tickets where i am either agent or subscriber in one report.

Your report helped me to figure it out a little bit more :

Select Top 1000000 htblticket.ticketid,
'#' + Cast(htblticket.ticketid As nvarchar) As Ticket,
htblticket.date As CreationDate,
htblticket.updated As LastUpdated,
htbltickettypes.typename As Type,
htblticketstates.statename As State,
htblpriorities.name As Priority,
htblsource.name As Source,
htblusers.name As [User],
htblusers1.name As AssignedAgent,
htblusers2.name As UserLastNote,
htblusers3.name As SubscribedAgent,
'../helpdesk/icons/' + htbltickettypes.icon As icon,
htblticket.subject As Subject
From htblticket
Inner Join htblpriorities On htblpriorities.priority = htblticket.priority
Inner Join htblticketstates On htblticketstates.ticketstateid =
htblticket.ticketstateid
Inner Join htblusers On htblusers.userid = htblticket.fromuserid
Left Join htblagents On htblagents.agentid = htblticket.agentid
Left Join htblusers htblusers1 On htblusers1.userid = htblagents.userid
Inner Join htbltickettypes On htblticket.tickettypeid =
htbltickettypes.tickettypeid
Inner Join htblsource On htblticket.sourceid = htblsource.sourceid
Inner Join htblusers htblusers2 On htblticket.userid_lastnote =
htblusers2.userid
Inner Join htblticketsubscribers On htblticket.ticketid =
htblticketsubscribers.ticketid
Inner Join htblagents htblagents1 On htblagents1.agentid =
htblticketsubscribers.agentid
Inner Join htblusers htblusers3 On htblusers3.userid = htblagents1.userid
Where (htblticketstates.statename <> 'Closed' And htblusers1.name =
'Agent' And htblticket.spam <> 'True') Or
(htblticketstates.statename <> 'Closed' And htblusers3.name = 'Agent'
And htblticket.spam <> 'True')
Order By htblticket.ticketid

Now I have another problem. There should be 35 tickets, but the report only shows me 28.
Is it possible that the report doesn't show me the ticket if there is more than one subscribed agent or no subscribed agent at all?

EDIT: All the tickets without a subscriber are missing. Do I have to put a second 'or' in there?

Thank you for your help!

[Esben_D]

To show the tickets only a specific agent is subscribed to you would need to add the following tables:

• htblticketsubscribers
  
• htblagents
  
• htblsusers

Then you link them in the following order htblticket > htblticketsubscribers > htblagents > htblsusers
Then you can add the criteria of the agent name you want to filter for.

You can use the report below and replace the highlighted name below with name of the agent you want to filter for.

Select Top 1000000 htblticket.ticketid,
'#' + Cast(htblticket.ticketid As nvarchar) As Ticket,
htblticket.date As CreationDate,
htblticket.updated As LastUpdated,
htbltickettypes.typename As Type,
htblticketstates.statename As State,
htblpriorities.name As Priority,
htblsource.name As Source,
htblusers.name As [User],
htblusers1.name As AssignedAgent,
htblusers2.name As UserLastNote,
htblusers3.name As SubscribedAgent,
'../helpdesk/icons/' + htbltickettypes.icon As icon,
htblticket.subject As Subject
From htblticket
Inner Join htblpriorities On htblpriorities.priority = htblticket.priority
Inner Join htblticketstates On htblticketstates.ticketstateid =
htblticket.ticketstateid
Inner Join htblusers On htblusers.userid = htblticket.fromuserid
Left Join htblagents On htblagents.agentid = htblticket.agentid
Left Join htblusers htblusers1 On htblusers1.userid = htblagents.userid
Inner Join htbltickettypes On htblticket.tickettypeid =
htbltickettypes.tickettypeid
Inner Join htblsource On htblticket.sourceid = htblsource.sourceid
Inner Join htblusers htblusers2 On htblticket.userid_lastnote =
htblusers2.userid
Inner Join htblticketsubscribers On htblticket.ticketid =
htblticketsubscribers.ticketid
Inner Join htblagents htblagents1 On htblagents1.agentid =
htblticketsubscribers.agentid
Inner Join htblusers htblusers3 On htblusers3.userid = htblagents1.userid
Where htblticketstates.statename <> 'Closed' And htblusers3.name = 'Agent' And
htblticket.spam <> 'True'
Order By htblticket.ticketid