Overview
Kayako Classic reports are capable of producing various types of report layout. The way a report is displayed depends on how you structure your report query using a KQL statement. This article discusses the different report types available in Kayako Classic.
Information
Tabular Report
The tabular report is the simplest type of report, with a single heading row followed by information. The following KQL statement was used to produce a tabular report.
Tabular report example
SELECT 'Tickets.Ticket Mask ID','Tickets.Subject','Tickets.Priority',
'Tickets.Average Response Time' FROM 'Tickets' WHERE 'Tickets.Status'='Open'
AND 'Tickets.Creation Date'= Today()
Summary Table Report
The summary table report is intended for use when performing calculations - such as a count of tickets, an average of customer satisfaction ratings and the oldest tickets (maximum values).
The following KQL statement counts all of the tickets in the specified departments, and then groups the counts by the department to produce a summary table. A summary table includes at least one 'GROUP BY' (this is what differentiates it from a simple tabular report).
Simple summary table report
SELECT COUNT(*)ASTicketsFROM'Tickets' WHERE 'Tickets.Department' IN
('Support','Sales','Billing') GROUP BY'Tickets.Department'
We can go further than this and add nested groups to the summary table. We can take the same KQL statement as above, and add some more groupings to the end of the statement to break the ticket counts down by department, the priority and then by status.
Summary table report
SELECT COUNT(*)ASTicketsFROM'Tickets'WHERE'Tickets.Department' IN
('Support','Sales','Billing') GROUP BY'Tickets.Department','Tickets.Priority',
'Tickets.Status'
There is no limit to the depth of nests you can include in your report.
Grouped Tabular Report (Multigroup)
The grouped report (using the MULTIGROUP operator) can be used to produce a list of tables within one report and using one statement. For example, you may like to see a list of tickets assigned to Rajiv Sharma, broken down by the day the tickets were created.
Grouped report example
SELECT 'Tickets.Ticket Mask ID','Tickets.Department','Tickets.Owner' FROM 'Tickets' WHERE 'Tickets.Owner'='Rajiv Sharma' AND 'Tickets.Creation Date'= LastMonth()
MULTIGROUP BY 'Tickets.Creation Date':DayName
Matrix Reports
-
Simple matrix report
A matrix report is a grid report. At its simplest, it contains one row of labels, one column of labels and information in the middle. For example, you could create a matrix report displaying the number of tickets created within each of your department on each day of the last seven days.
Simple matrix report exampleSELECT COUNT(*) FROM 'Tickets' WHERE 'Tickets.Creation Date'= Last7Days() GROUP BY X
('Tickets.Department'), Y('Tickets.Creation Date':DayName)
The KQL statement of a matrix report must contain an X() and Y() grouping. The X() indicates what will be your columns (horizontal) and the Y() indicates what will be your labels (vertical).
-
Nested matrix report
Just like the simple matrix report, a nested matrix report is a grid report. However, you can add additional X() and Y() operators to introduce nesting (or grouping) to the columns and labels.
For example, you could create a nested matrix report similar to the example above. However, as well as just breaking the tickets count down by department day of the week, we can also group the ticket counts by priority.
Nested matrix report exampleSELECT COUNT(*) FROM 'Tickets' WHERE 'Tickets.Creation Date'= Last7Days() GROUP BY X
('Tickets.Department'), X('Tickets.Priority'), Y('Tickets.Creation Date':DayName)
As another example, rather than just counting tickets created within the last seven days, we can count tickets created in the last month, grouped by week and then by day of the week.
Another nested matrix report example
SELECT COUNT(*) FROM 'Tickets' WHERE 'Tickets.Creation Date'= LastMonth() GROUP BY X
('Tickets.Department'), X('Tickets.Priority'), Y('Tickets.Creation Date':Week),
Y('Tickets.Creation Date':DayName)
There is no limit to the depth of nests you can include in your report.