SQL Functions for Custom Reports

To assist in writing advanced queries, placeholders are available for each function. Function placeholders are replaced with SQL code upon execution. Functions are used like parameters, but their names have a $ (dollar symbol) as a prefix. Unlike parameters, placeholders are defined report elements that cannot be customized per execution.

The following table lists all available function placeholders:

Function What it does Example
$TODAY Returns the current systemdate on the database server. You can also write $TODAY-1 for yesterday or $TODAY-7 for a week ago. CreatedAt > ${$TODAY}
$DATE(column) Returns the date but not the time.
$DATE('string') Converts the given string to a database date. CreatedAt > ${$DATE('01/10/2005')}
$DAYS[p1;p2] Calculates the difference in days between the two given parameters. The two parameters can be a column within the table/view or $TODAY. The following example returns the rows created within the last week: ${$DAYS[CreatedAt;$TODAY]} > 7
$WEEK(param) Returns the week-number of the given parameter, which can be $TODAY or a column.
$MONTH(param) Returns the month of the year as a number of the given parameter, which can be $TODAY or a column.
$YEAR(param) Returns the year as a number of the given parameter, which can be $TODAY or a column.
$USERID The ID of the currently logged in user.
$USERNAME The name of the currently logged in user.
$PROJECTID The ID of the currently selected project.
$PROJECTNAME The name of the currently selected project.
$REPORTNAME The name of the currently selected report.
$REPORTID The ID of the currently selected report.