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 timestamp of the beginning of the day in UTC. | The column returns: 2019-08-30 08:37:33 in CEST, which is rendered as 2019-08-30 02:00 |
| $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