Reporting Enhancements

[will.good]

Following on from the response on this post - can I please request the following enhancements to reporting:

• Ability to format date/time
• Able to add in columns such as week number (to group counts by week for example)
• Replace values in cells
• Functions such as sums / averages / concatenate / rounding / average and add columns based on this
• Split values by delimiter

Some examples below of how we are using Datebase Direct which are not currently possible (to my knowledge) in in-app reporting:

SQL to return request source count (by week)

select
weekofyear(h_datelogged) as "Week Number",
DATE_FORMAT(h_datelogged, '%d/%m/%Y') AS "Week Commencing",
SUM(case
when h_source_type = "Analyst" THEN 1
END) AS "Analyst", 
SUM(case
when h_source_type = "Email" THEN 1
END) AS "Email",SUM(case
when h_source_type = "Self Service" THEN 1
END) AS "Self Service",  
SUM(case
when h_source_type = "Chat" THEN 1
END) AS "Chat", 
SUM(case
when h_source_type = "Autoresponder" THEN 1
END) AS "Autoresponder",
SUM(case
when h_source_type = "Request" THEN 1
END) AS "Request",
COUNT(h_source_type) AS "Total"
FROM h_itsm_requests
WHERE (h_datelogged >= '2023-01-02' and
h_datelogged <= '2023-12-31')
group by weekofyear(h_datelogged) desc

SQL to return count of closure code by month

SELECT h_closure_category AS "Closure Category",
SUM(CASE WHEN monthname(h_datelogged) = 'January' THEN 1 END) AS "January Count",
SUM(CASE WHEN monthname(h_datelogged) = 'February' THEN 1 END) AS "February Count",
SUM(CASE WHEN monthname(h_datelogged) = 'March' THEN 1 END) AS "March Count",
SUM(CASE WHEN monthname(h_datelogged) = 'April' THEN 1 END) AS "April Count",
SUM(CASE WHEN monthname(h_datelogged) = 'May' THEN 1 END) AS "May Count",
SUM(CASE WHEN monthname(h_datelogged) = 'June' THEN 1 END) AS "June Count",
SUM(CASE WHEN monthname(h_datelogged) = 'July' THEN 1 END) AS "July Count",
SUM(CASE WHEN monthname(h_datelogged) = 'August' THEN 1 END) AS "August Count",
SUM(CASE WHEN monthname(h_datelogged) = 'September' THEN 1 END) AS "September Count",
SUM(CASE WHEN monthname(h_datelogged) = 'October' THEN 1 END) AS "October Count",
SUM(CASE WHEN monthname(h_datelogged) = 'November' THEN 1 END) AS "November Count",
SUM(CASE WHEN monthname(h_datelogged) = 'December' THEN 1 END) AS "December Count"
FROM   h_itsm_requests
where h_closure_category is not null and (h_datelogged >= '2023-01-01' AND h_datelogged <= '2023-12-31')
GROUP  BY h_closure_category 

SQL to return breakdown of chat sessions by week

SELECT
weekofyear(h_created_on) as "Week Number",
DATE_FORMAT(h_created_on, '%d/%m/%Y') AS "Week Commencing",
SUM(case when h_status = "3" THEN 1 END) AS "Resolved",
SUM(case when h_status = "99" THEN 1 END) AS "Cancelled",
SUM(case when h_agent_id is not null THEN 1 END) AS "Accepted",
SUM(case when h_agent_id is null THEN 1 END) AS "Missed",
SUM(case when h_session_id is not null THEN 1 END) AS "Total",
concat(round(sum(case when h_agent_id is null THEN 1 END)/SUM(case when h_session_id is not null THEN 1 END)*100,0),"%") as "Missed %",
SEC_TO_TIME(round(avg(h_response_time),0)) as "Average Response Time",
SEC_TO_TIME(round(avg(h_close_time),0)) as "Average Close Time"
FROM h_lc_sessions
WHERE (h_created_on >= '2023-01-01 00:00:00' and h_created_on <= '2023-12-31 23:59:59')
group by weekofyear(h_created_on)
order by weekofyear(h_created_on) desc