How do I create a report of New Tickets vs Time of Day?

Discussion in 'Using your helpdesk' started by donander, Jun 7, 2011.

  1. donander

    donander Member

    I want to create a report, preferably in graph form, with Time of Day on the X axis and New Ticket Posts on the Y axis. If a graph is not possible, then a table would be ok and I can make the graph in Excel. I want this report to aggregate data so that it shows the aggregate numbers for the last year, per half hour time slot or bin.

    I have mocked up a report with graph in Excel to illustrate what I'm looking for. Note that the data are TOTAL new tickets for a given time period (I'd like to look at the past year) for the 48 half-hour time bins during a 24 hour period. These data are purely fictitious but one would imagine it would be roughly a bell curve, maybe with a slight dip during the lunch hour.

    My reason for wanting this report is to determine what hours we need the most staff coverage.

    Thanks,
    Don

    Attached Files:

  2. Gary McGrath

    Gary McGrath Kayako Staff Staff Member

    Hi Don,

    My answer to a another ticket I think can do this for you actually:

    http://forums.kayako.com/threads/v4-reporting-module.24274/page-2

    Specifically this SQL here:

    SELECT swtickets.ticketmaskid, swtickets.isescalated as Escalated, swticketstatus.title as Status, swticketpriorities.title as Priority, swdepartments.title as Department, swstaff.fullname as "Ticket Owner", FROM_UNIXTIME(swtickets.dateline) as Created
    FROM swtickets
    Right Join swticketstatus
    On swtickets.ticketstatusid = swticketstatus.ticketstatusid
    Right Join swticketpriorities
    On swticketpriorities.priorityid = swtickets.priorityid
    Right Join swdepartments
    On swdepartments.departmentid = swtickets.departmentid
    Right Join swstaff
    on swstaff.staffid = swtickets.ownerstaffid

    It will pull out most of the data you could want to actually generate stats on anything, in your case, it grabs date created and time in 24h notation. You could use that field to create your graph from ( Although you will probably need excel to split out the date hours and mins into seperate columns. )

    Gary
  3. donander

    donander Member

    Gary,
    As I am not fascile with SQL, and am concerned about messing something up if I try, I'd prefer to use the report wizard. Would the procedure posted on May 30 in the other thread you linked work?
    Thanks,
    Don
  4. Gary McGrath

    Gary McGrath Kayako Staff Staff Member

    Hi Don,

    You can try using the inbuilt matrix, but for what you want, I really think at the moment, direct SQL is going to be the only way you can get it.

    If your MySQL is inside your network, you can do what we did if your worried about breaking something, we added a new user to our SQL DB which had "read only" access to the DB, we then used ODBC to create a connection to the MySQL. Then in Excel, we used the ODBC to bring in the data, the benifit of this is once its done, there is no longer any SQL to worry about, you just open excel, click refresh and bingo, your in stats heaven.

    I can talk you through all the above if you wish to go that route ( it sounds daunting I know, but it can all be done in about 10 mins promise! )

    Gary

Share This Page