Index / Free SQL

HaloPSA QBR Report: Ticket Volume by Client and Type (free SQL)

The table every QBR starts with: how many tickets each client raised, what kind, month by month. Drop it into a Halo dashboard as a chart or export it to your QBR deck.

SELECT TOP 5000
    a.aareadesc                       AS [Client],
    r.rtdesc                          AS [Type],
    FORMAT(f.dateoccured, 'yyyy-MM')  AS [Month],
    COUNT(*)                          AS [Tickets]
FROM FAULTS f
JOIN AREA a        ON a.Aarea = f.Areaint
JOIN REQUESTTYPE r ON r.RTid  = f.Requesttype
WHERE f.dateoccured >= DATEADD(MONTH,-6,GETDATE())
  AND ISNULL(f.FDeleted,0) = 0
  AND ISNULL(r.RTIsOpportunity,0) = 0 AND ISNULL(r.RTIsProject,0) = 0
GROUP BY a.aareadesc, r.rtdesc, FORMAT(f.dateoccured, 'yyyy-MM')
ORDER BY [Client], [Month], [Tickets] DESC

How to add it

1. In Halo go to Configuration → Reporting → Reports (or the Reports module) and click New.
2. Set Data Source to Custom SQL Query and paste the SQL.
3. Click Load Report to preview, then save it. You can feed it into a dashboard widget.

Tweaks

• Filter to one client by adding AND a.aareadesc = 'Client Name' before GROUP BY.
• Change -6 to -3 for a quarter.
• Projects and opportunities are excluded, so the numbers are support tickets only.