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. |
Below is the code of the pre-installed Requirement with Child Requirements report. With this report, a selected requirement is shown with its requirement ID. Full details regarding the requirement’s child requirements are displayed. Although not a custom report, this report is a helpful example because it makes use of the $PROJECTID function. It also includes two parameters, reqID (requirement ID) and reqProp_Obsolete_0 (show obsolete requirements).
SELECT r.ReqID, r.ReqCreated, r.ReqName, r.TreeOrder FROM RTM_V_Requirements r INNER JOIN TM_ReqTreePaths rtp ON (rtp.ReqNodeID_pk_fk = r.ReqID) WHERE rtp.ParentNodeID_pk_fk=${reqID|22322|Requirement ID} AND r.ProjectID = ${$PROJECTID} AND r.MarkedAsObsolete=${reqProp_Obsolete_0|0|Show obsolete Requirements} ORDER BY r.TreeOrder ASC