StableN/ASyncedN/A

Database Reporting Functions

Updated Jun 9, 2026468 viewsView on usehalo.com
  • reporting
  • dbo
  • database functions

In this guide we will cover:

  • What is a Database Function?
  • List of Database Functions
  • Example Report

What is a Database Function?

A database function is a section of text that runs a built-in SQL query when used in another query. This can be used to format a report without making your own query unnecessarily long or complicated.

To use these, add "dbo." in front of the function name within the query.

List of Database Functions

Halo has the following stored procedures. For the below, the parameters are listed as their datatype and what the values should relate to.

  • VariableWorkTime (datetime startdate, datetime enddate).
  • Get_ServiceUser_By_ServiceID_Userid (int serviceid, int userid).
  • GetSLATimeLeft (datetime enddate, int slaid).
  • Fn_GetWorkingHours_startday (datetime startdate, int slaid, int timezoneOffset).
  • Fn_GetWorkingHours_endday (datetime enddate, int slaid, int timezoneOffset).
  • Fn_GetWorkingHours_workdaysbetween (datetime startdate, datetime enddate, int slaid, bit hoursNotDays, int timezoneOffest).
  • Fn_GetWorkingHours (datetime slaFixByDate, int slaid, varchar(255) timezone_override).
  • Fn_FormatTime (float time).
  • Fn_FormatTimeChar (float time).
  • GetMonthlyValue (float amount, int period).
  • Fn_GetWorkingHours_datetimes (datetime startdate, datetime slaFixByDate, int slaid, int faultid, varchar(255) timezone_override).
  • getInvoiceLineQuantity (float quantity, int lineid, int clientid, int recurringInvoiceQuantityType). 
  • GetSLATimeLeft (datetime Date, int SLAID) 
  • GetTimeBetweenDatesWithSLAID (datetime Start Date, datetime End Date, int SLA ID) 
  • GetTimeBetweenDatesWithWorkdayID (datetime Start Date, datetime End Date, int Workday ID)

Example Report

Let's look at an example of how this works. Here we have a report that shows time taken.

select top 10 round(elapsedhrs,2) as [Time Taken] from faults
where fdeleted=0

Fig 1. Report result without dbo.

Inserting the "fn_formattime" function with "dbo." in front of it will format the report numbers as hours:minutes instead of as a decimal of the hour.

select top 10 dbo.fn_formattime(round(elapsedhrs,2)) as [Time Taken] from faults
where fdeleted=0

Fig 2. Report result with dbo.

Some functions, like dbo.GetSLATimeLeft require a certain format. If you are getting wrong results, try this:

select top 10 dbo.GetSLATimeLeft(CONVERT(VARCHAR(16), CONVERT(DATETIME, fixbydate, 103), 120), slaid) as [SLA Time Left] from faults
where fdeleted=0