Attempting to create a report

Discussion in 'Kayako Reports' started by esowers, Jun 21, 2012.

  1. esowers

    esowers Established Member

    For tickets that we include knowledgebase articles in we put a Tag of "KB" on the ticket.

    We would like to generate a report that shows a percent (or number) of total tickets for that staff that are closed that have the KB tag for each staff member.

    Here is an image of the layout we're looking for.
    percent_of_faq.png

    Two questions with this.
    1. Does anyone know if generating percents is possible?​
    2. Could anyone provide insight as to how to put this report together?​
  2. Drew Keller

    Drew Keller Just one person in a world of millions.

    Percentages are possible. Have a look at the standard ticket report "Percentage of tickets that are reopened grouped by department for this month" as an example.

    I am just not sure if Tag is available to the KQL and I can't check at the moment
  3. esowers

    esowers Established Member

    Thanks Drew, I didn't see the percentage reports.

    I think I'm getting close, but for some reason this report isn't pulling any information (when I know there are requests closed with the tag).

    Code:
    SELECT ((SUM(IF('Tag Links.Tag' = 'kb', 1, 0))/COUNT(*))*100) AS 'KB Tags' FROM 'Tickets', 'Tag Links' WHERE 'Tag Links.Tag' = 'kb' AND 'Tickets.Department' = 'Technology Services' GROUP BY Tickets.Owner
    I'm new to KQL and MySQL query's so please don't judge me :)
  4. masterctrl1

    masterctrl1 Kayako Guru

    Do you mean the operator searched for a KB article and inserted the contents? It would be nice if the KB tag was automatic. :)
    What I really don't understand is why there isn't an option to insert a KB article URL into the ticket reply instead.
    Many of our articles are technical in nature and include numbered steps, links, etc. -- all of which doesn't format well in a ticket reply.
    I am dumbfounded why there is no option to post the KB article URL into a ticket or chat reply in one click. Very frustrating! :mad:
  5. esowers

    esowers Established Member

    Unfortunately we navigate out to the user side and copy/paste the KB URL into the ticket, then add the tag manually (quite a long process, but gives the desired outcome).

    I agree, adding the URL would be an AMAZING feature!! :) Our Knowledgebase items typically include pictures, which don't work with inserting the KB article into a reply.
  6. Drew Keller

    Drew Keller Just one person in a world of millions.

    Sorry was late here last night so had gone to bed.

    I think your issue is simple, just swap tickets an tag links in your from statement, one of the issues I have with KQL is that table ordering is specific but not explained or errored.

    Let me know if that works, I can test in a couple of hours otherwise.
  7. esowers

    esowers Established Member

    Drew, thanks for the recommendation, but unfortunately switching the tickets and tag links didn't do the trick.
  8. Drew Keller

    Drew Keller Just one person in a world of millions.

    The only thing I can see being the difference is the field you using is not a field that only accepts 1 or 0 like is resolved, so maybe the sum if statement cannot handle this.

    I think this is a question for Kayako support.

    Sorry I can't help further.
    TechnoPhobia likes this.

Share This Page