ServiceDesk

 View Only

  • 1.  All open tickets assigned to workers and queues

    Posted Aug 06, 2013 04:21 PM

    Hi All,

    I'm trying to write a report for all open tickets, assigned to workers and queues. I can get all tickets assigned to queues and then one with all open tickets assigned to a user but not both in the same report. Can anyone help me out?

    Thanks

    Jen



  • 2.  RE: All open tickets assigned to workers and queues

    Posted Aug 06, 2013 04:58 PM

    You'll have to do two reports and display them on the page. One to show all the tickets assigned to workers and another to show assigned to queues. We have three reports for on our page, tickets assigned to you, tickets assigned to your queues and tickets assigned to members of your queues.



  • 3.  RE: All open tickets assigned to workers and queues

    Posted Aug 08, 2013 07:29 PM

    That is the way we have it on the page the manangement wants it in a excel spreadsheet. I was hoping to find a way to merge the reports. I might just have to run each and use excel to merge the info.

     

    Thanks Aryanos.



  • 4.  RE: All open tickets assigned to workers and queues

    Posted Aug 13, 2013 10:43 AM

    My solution to this in the past was to write a SQL query that used a case statement for the task assignment. Something like if reference type 1, get from the user table, if 2, get from the group table (if those are the right numbers!). I then added it as a profile so that I could use that assignment info in any report.



  • 5.  RE: All open tickets assigned to workers and queues

    Posted Aug 16, 2013 11:07 AM

    Michael,

    I was trying to write it in SQL but I am no SQL query master and don't know how to do it.  I'm not even sure what the other table would be.

    thanks

    Jen



  • 6.  RE: All open tickets assigned to workers and queues

    Posted Aug 16, 2013 01:06 PM

    Two ways to do this: using a raw SQL report (available inside the report builder) or using a data profile.

    If using a raw SQL report, use this:

     

    SELECT CASE WHEN ISNULL(SDIM.[CurrentlyAssignedQueueName], '') <> '' THEN SDIM.[CurrentlyAssignedQueueName]
    WHEN ISNULL(SDIM.[Owner], '') <> '' THEN SDIM.[Owner]
    END AS AssignedTo
    **Insert other columns here**
    FROM [ImIncidentTicket] SDIM
     
    And put any other data from the Incident table where the **Insert other columns here** note is. That'll get you a very basic report, but you won't have access to parameters like you normally get in the report builder. Things like "Not Completed" or date options and what not.
     
    The better option is to create a data profile as outlined in https://www-secure.symantec.com/connect/articles/using-custom-sql-servicedesk-report-builder-and-still-use-parameters (hat tip: jvenuto).
     
    Doing that, you can still use the above SQL (leave out the other columns bit), but you can still use your normal parameters like any other report.