V4 Reporting Module

Discussion in 'New ideas, bigger features' started by Mle, Oct 20, 2010.

Thread Status:
Not open for further replies.
Page 2 of 6

  1. Krafty Established Member

    This was posted by another user and i've found it quite useful.

    Create a new report, call it "weekly report" or something like that
    Report Type "Matrix report"
    Base Table Name "Tickets"
    Click next
    Field for columns : "Staff Full Name"
    Fields for Rows: "Ticket Status Title"
    1st Function Field: "Ticket ID"
    Fuctions: Count
    Click Next
    Insert Criteria
    Ticket Last Activity<range> Equal to "Last 7 days"
    Click on finish and run the report

    See if that gets you roughly what your looking for
    Krafty, May 30, 2011
    #21

  2. touficjohn Member

    Jamie I know Kayako doesn't like giving out ETA's, timeframes or roadmaps.
    When v4 was first released I used to submit tickets and post on the forums quite a lot. Now, I can't be bothered because it all takes too long.
    "When something doesn't work it'll eventually get fixed." seems to be the attitude around here.

    Unfortunately, every time I check the forums reporting seems to be delayed, I'm thanked for my patience, Kayako is sorry for the holdup and I should watch this space.

    However, reporting is something I, the management, the people who sign my paychecks, the CIO and the CFO now want.
    Every two days I've got nearly all of these people asking me why I can't give them a decent report and the CFO is looking at me funny asking me why he authorised Kayako's purchase if it doesn't work.

    So!
    Maybe its about time you stopped being so secretive and actually told us when we can expect the software we paid for to work?
    Or is that too much to ask for?
    touficjohn, Jun 1, 2011
    #22
    • Kayako Staff

    Gary McGrath Kayako Staff

    Hi Touficjohn,

    On a side note to your post, what reports are you looking for? The reason I ask is we created several reports by running queries against our sql server, so might be able to at least help you get some reports going upstairs.

    Gary
    Gary McGrath, Jun 1, 2011
    #23

  4. scravid Established Member

    Hi Gary,

    I'd be interested in any SQL you have to get reports out of the system.

    I'm sure the output won't be pretty, but anything you have would be appreciated!

    Cheers,

    Scravid.
    scravid, Jun 1, 2011
    #24

  5. Southerncentralrain Established Member

    We are on the hosted version but have the same sentiments as touficjohn. We need reports now and it is causing major issues for us.
    Southerncentralrain, Jun 1, 2011
    #25
    • Kayako Staff

    Gary McGrath Kayako Staff

    Hi Scravid,

    What kind of reports are you looking for, as our sql ones are quite complicated involving pivot tables and views, if you let me know what your looking for, I can give you some SQL to get it.

    Gary
    Gary McGrath, Jun 1, 2011
    #26

  7. malocite Member

    I cannot scream loudly enough about this. My CEO is demanding reports and I can't even tell her how many calls I've done today unless I keep a paper spreadsheet.

    Today is June 1st. I was told there would be something out in the beginning of April (which was already 5 months after the product was released) We the people (the paying customers) are DESPERATELY asking for an update. Give us an update, a beta, an alpha, some screen shots, SOMETHING to indicate that this is actually being worked on.

    Please....
    malocite, Jun 1, 2011
    #27
    • Kayako Staff

    Jamie Edwards Chief Limey

    The current status is as I posted a few days ago...
    I understand that this is a frustrating for some of you. Live we've said before we are working on reengineering the reporting engine so you can get the data you need out of it. We're doing it as fast as possible without compromising on quality.

    There aren't yet any screenshots or previews publish yet. The engine (the majority of the engineering work) is complete and we are wrapping up the UI. Of the core 4.01 helpdesk it is the only major new feature being worked on right now.

    When there is an update we'll be the first to post it.
    Jamie Edwards, Jun 1, 2011
    #28

  9. touficjohn Member

    With all due respect Jamie/Kayako, your last post is not good enough.
    We know your working on it as quickly as you can and I'm sure it can't be easy with all the angry customers, but:

    I would very much like a ballpark date or a month - not a quarter - once and for all, to know how to handle the people upstairs.

    If not, kindly publish a blog post explaining what happened to the reporting module and where it's at in lay mans terms and update the content on http://www.kayako.com/tour/measure/ to reflect the current state of your reporting engine.

    I don't need kayako to tell me how well my team is doing compared to the month before or how quickly we're getting back to our customers, because I know, it's my bloody department. Try explaining that to your employer.

    90% of CxO's dont even know what the hell Kayako is.
    It's the IT guys on the ground pushing and selling your software to their bosses, promising all kinds of great things (like reports) in exchange for a signed and approved purchase order.
    Only to look like a fool when it doesn't work.
    touficjohn, Jun 2, 2011
    #29

  10. azyiu Member

    hi Jamie, here are fields we would like to see in our reports, and we also would like the ability to do sorting as well:

    - date (by month)
    - user type (teacher/ school it admin/ student/ paerent/ home product user/ internal)
    - enquiry type
    - request type
    - school name (if user fill in)
    - product (website/ cd-rom/ xxx/ xxx/ others)
    - book series
    - special remarks
    azyiu, Jun 2, 2011
    #30

  11. azyiu Member

    I posted this in another thread, but here are fields we would like to see in our reports, and we also would like the ability to do sorting as well:

    - date (by month)
    - user type (teacher/ school it admin/ student/ paerent/ home product user/ internal)
    - enquiry type
    - request type
    - school name (if user fill in)
    - product (website/ cd-rom/ xxx/ xxx/ others)
    - book series
    - special remarks
    azyiu, Jun 2, 2011
    #31

  12. scravid Established Member

    Thanks for the offer Gary,

    I'm not after anything too fancy, I'd just like to get some reports to tell me things like:

    Total Tickets Logged, Outstanding, Closed, overdue, by Department and Priority
    How our customers rated us too

    Do you have anything which might help?

    Anything appreciated!

    Cheers,
    scravid, Jun 2, 2011
    #32
    • Kayako Staff

    Jamie Edwards Chief Limey

    Ok - as things stand we are aiming to have a beta out by the end of June, but this might change. Hopefully we'll have a preview out before then, too.
    Jamie Edwards, Jun 2, 2011
    #33
    • Kayako Staff

    Gary McGrath Kayako Staff

    Hi there Scravid,

    If you run this SQL query here against your fusion database, it will return the data you asked for, then you just need to use excel or something to create a pivot table, or you can just sort the columns for quick displays of things.

    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

    Gary
    Gary McGrath, Jun 2, 2011
    #34

  15. scravid Established Member

    Thanks a lot Gary, that looks brilliant!

    Don't suppose you've got anything on ratings do you?

    Not to worry if you don't, I'm very grateful for the SQL you've already provided.

    Cheers.
    scravid, Jun 2, 2011
    #35

  16. malocite Member

    End of June.... I'll be happy if it happens. That last fellow is echoing my sentiments exactly. I pushed ahead with the Kayako product because I believed in the company and the product. It was a mistake on my part not ensuring this functionality was there, and I suppose there is a lesson there to be learned. Don't assume basic functionality ever exists, always ask.

    My CEO asks me every week how many calls have been opened and closed and all I can do is shrug and say 'lots'.

    She stopped laughing at that answer back in April.

    A stop gap measure may be to provide a person to do what garygbm just did by providing some custom sql statements that we can use to build our own report with a third party product. Its not ideal, but I don't know what else we can do. At least with that we could show our bosses something. I'm going to try using his sql stuff and see if I can get any data out. And I'll sit with baited breath as I wait for the end of the month.

    Work hard guys....
    malocite, Jun 2, 2011
    #36
    diggo likes this.
    • Kayako Staff

    Gary McGrath Kayako Staff

    Hi there,

    I can give you the SQL to link in your ratings to your tickets, but you will need to do a little checking on your fusion database. The reason for this is that its more easy to use select statements to stop duplicate rows.

    This code below will grab the defauly "over all satisfaction" rating for you.

    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,
    (select benchmarkresult from swbenchmarkresults where swbenchmarkresults.typeid = swtickets.ticketid and benchmarkid=1 ) as "Overall Satisfaction"
    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

    If if you have more than 1 rating ( we have 4 ), then you need to go and browse your SWBENCHMARKS table and write down what benchmarkID relates to each of your custom ratings.

    Then you edit the above code with this: ( replacing <BenchMarkID> with the correct ID and <My Custom Rating Name> with the name of the rating:

    ,(select benchmarkresult from swbenchmarkresults where swbenchmarkresults.typeid = swtickets.ticketid and benchmarkid=<BenchMarkID> ) as "<My Custom Rating Name>"

    Ours looks like this to give you a final example:

    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,
    (select benchmarkresult from swbenchmarkresults where swbenchmarkresults.typeid = swtickets.ticketid and benchmarkid=1 ) as "Overall Satisfaction",
    (select benchmarkresult from swbenchmarkresults where swbenchmarkresults.typeid = swtickets.ticketid and benchmarkid=2 ) as "Communication",
    (select benchmarkresult from swbenchmarkresults where swbenchmarkresults.typeid = swtickets.ticketid and benchmarkid=3 ) as "Response Time",
    (select benchmarkresult from swbenchmarkresults where swbenchmarkresults.typeid = swtickets.ticketid and benchmarkid=4 ) as "friendliness"
    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

    Hope that is clear enough for you? let me know if you have any questions.

    Gary
    Gary McGrath, Jun 2, 2011
    #37

  18. diggo Member

    Exactly the same here, with acute embarrassment for me trying to explain why the migration has stalled to management. The only reason we considered upgrading was for the promised reporting.

    Sean
    diggo, Jun 3, 2011
    #38

  19. scravid Established Member

    Thanks a lot for your time and effort Gary, the SQL is absolutely crystal clear!

    I haven't done any DB coding for a while myself, but with your comments etc I am sure I can dig out the rest of my ratings!

    Thanks again, really appreciated.

    Scravid.
    scravid, Jun 3, 2011
    #39
    • Kayako Staff

    Gary McGrath Kayako Staff

    Hi Scravid,

    If your not too confident browsing/creating sql. you can run this here against your fusion DB

    SELECT benchmarkid, benchmarktitle from swbenchmarks

    It will come back with a list of ID's and names you can use. If you struggle to get it to work, if you post the output here, I can integrate it into the longer sql code for you. :)

    Gary
    Gary McGrath, Jun 3, 2011
    #40
Page 2 of 6
Thread Status:
Not open for further replies.

Share This Page