1. Kayako Download customers: we will continue to develop and support Kayako Download beyond July 2017, alongside the new Kayako for existing customers.

    Find out more.

  2. The forum you are viewing relates to Kayako Classic. If you signed up or upgraded to the new Kayako (after the 4th July 2016), the information in this thread may not apply to you. You can visit the forums for the new Kayako here.

Reports that are based on 2 week parameters.

Discussion in 'Kayako reports (Kayako Classic)' started by John Jo, Aug 5, 2016.

  1. John Jo

    John Jo New Member

    Hello,

    I need help trying to generate a report that shows new tickets that

    • Opened in the last 2 weeks
    • Grouped by Organization
    • Containing Ticket Creation Date
    • First Response date
    • Ticket Priority
    • Ticket Status
    I have something like this :

    SELECT 'Tickets.Creation Date', ADDTIME('Tickets.Creation Date', 'Tickets.First Response Time') AS 'Date of First Response', 'Tickets. Subject', 'Tickets.Priority', 'Users.User Organization
    WHERE ‘Tickets.Creation Date’ =Last14Days() , 'Tickets.Status' = "In Progress"

    Im sure that where I’m wrong is the “Tickets.Creation Date” =Last14Days()

    Is there another method in getting a 2 week parameters?


    Thanks!
     
  2. Phil R

    Phil R Established Member

    This would require edits to the KQL library scripts and one of the thirdparty components.

    In the installation, under __swift/apps/base/library/KQL

    Edit: class.SWIFT_KQL.php
    Requires appending last14days to the '$_allowedExtendedFunctionList' variable
    Requires 'Last14Days()' => 'Last14Days()' adding to '$_extendedFunctionList'

    Edit: class.SWIFT_KQLParser.php
    Locate 1st instance of "case 'last7days':"
    Insert before this:
    Code:
                    case 'last14days':
                        return array($_returnOperator, "'" . SWIFT_Date::FloorDate(strtotime('-14 days')) . "' AND '" . SWIFT_Date::CeilDate(strtotime('today')) . "'");
    
    
    Locate 2nd instance of "case 'last7days':"
    Insert before this:
    Code:
                case 'last7days':
                    return "'" . SWIFT_Date::FloorDate(strtotime('-14 days')) . "'";
    
    In the installation, under __swift/thirdparty/SQLParser

    Edit Parser.php
    Locate 1st instance of "$_extendedFunctions"
    Add 'last14days' to the array.
     

Share This Page