Index / Free SQL

HaloPSA Billable vs Non-Billable Hours Report (free SQL)

Billable and non-billable hours per agent for the last 30 days, with a billable percentage. If an engineer is busy but barely billing, this is where it shows up.

SELECT TOP 200
    u.uname AS [Agent],
    CAST(SUM(ISNULL(ac.ActionChargeHours,0))    AS DECIMAL(8,1)) AS [Billable Hrs],
    CAST(SUM(ISNULL(ac.ActionNonChargeHours,0)) AS DECIMAL(8,1)) AS [Non-Billable Hrs],
    CAST(100.0 * SUM(ISNULL(ac.ActionChargeHours,0))
        / NULLIF(SUM(ISNULL(ac.ActionChargeHours,0)) + SUM(ISNULL(ac.ActionNonChargeHours,0)),0)
        AS DECIMAL(5,1)) AS [Billable %]
FROM ACTIONS ac
JOIN UNAME u ON u.Unum = ac.whoagentid
WHERE ac.ActionArrivalDate >= DATEADD(DAY,-30,GETDATE())
  AND ISNULL(u.Uisdisabled,0) = 0
GROUP BY u.uname
ORDER BY [Billable %] 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

• Change -30 to -7 for a weekly team meeting view.
• Halo stores billable and non-billable hours separately on each time entry, so no rate maths is needed.
• Low billable % is often a contract setup problem, not a people problem. Check what's being logged against all-inclusive contracts.