The following System-defined variables can be used in SQL queries for Count, Chart and Table widgets.
Related Articles
Users Id (Guid)
@UserId This returns the guid of the current logged on user. This is useful for getting data that related to the current logged on user. It returns as a string wrapped in single quotes ready to be used in a SQL WHERE clause eg '382a9d02-f53f-4b71-91c8-df7dbe7c710d'.
Example:
SELECT
WorkItemId,
Title,
AffectedUser
FROM WorkItem
WHERE AssignedUserId = @UserId
This query will return all work items where the current logged on user is the assigned user.
Users Security Scope
@IsScoped This will return the value of whether the current logged on user is scoped for work item access. This is useful for ensuring that only data the user has access to, is returned. It returns a 1 or 0.
This variable is only valid when the @UserId variable is used too. This would normally be the case in order to return the right data.
Example:
IF(@IsScoped = 0)
SELECT WI.WorkItemId, WI.Title, WI.AffectedUser
FROM WorkItem WI
ELSE
SELECT WI.WorkItemId, WI.Title, WI.AffectedUser
FROM WorkItem WI
LEFT JOIN Access_CI$User_WorkItem AUW ON WI.Id = AUW.WorkItemId
AND AUW.UserId = @UserId
This query will return all work items if the end user is unscoped or just the ones they have access to if they are scoped.
Note: This can only return if the user is scoped for work items although, config item scoped value could be returned by querying the ScopedUser table directly.
SELECT SU.IsConfigItemScoped
FROM ScopedUser SU
WHERE SU.UserId = @UserId
Users Name
@Me This returns the DisplayName of the current logged on user. This is useful for getting data that related to the current logged on user. It returns as a string wrapped in single quotes ready to be used in a SQL WHERE clause eg 'Claire Mathis'.
Example:
SELECT WorkItemId, Title, AffectedUser
FROM WorkItem
WHERE AssignedUser = @Me
This query will return all work items where the current logged on user is the assigned user but will match by DisplayName which might not be unique.
Users Support Group(s)
@MySupportGroup This returns the Id of the support group of the current logged on user, or a list of Ids if the user is in more than one group. This is useful for getting data that related to the current logged on user's support group(s). It returns as a comma separated list of Ids as a single string wrapped in single quotes eg 'ae391e6b-e0f2-4eb0-8df5-6cf4bf3631e5,9fd5ede8-61de-495e-9e6d-17e9603b6945'.
Because these are a single string, not a list of strings, they cannot be used directly in a WHERE IN clause, instead we can JOIN to the output of the SplitString function which splits this list of Ids into a table of Ids.
Example:
SELECT WI.WorkItemId, WI.Title, WI.AffectedUser
FROM WorkItem WI
JOIN SplitString(@MySupportGroup,',') MySupportGroups
ON MySupportGroups.Item = WI.TierId
This query will return all work items assigned to the current logged on user's support group(s).
Users Language Setting
@LanguageCode This returns the language code from the preferences settings of the current logged on user. This is useful for getting localized strings in the language preferred by the user. It returns as a string wrapped in single quotes ready to be used in a SQL WHERE clause eg 'ENU'.
Example:
SELECT WorkItemId, Title, AffectedUser, SupportGroupDisplay.DisplayString
FROM WorkItem WI
JOIN DisplayString AS SupportGroupDisplay
ON SupportGroupDisplay.ElementID = WI.TierId
AND SupportGroupDisplay.LocaleID = @LanguageCode
WHERE AssignedUserId = @UserId
This query will return all work items, including the localized Support Group value, where the current logged on user is the assigned user
Current Date (with Offset)
@Today This returns the current date or the current date plus or minus n number of days using the format @Today+n or @Today-n. This is useful for getting data based on the current date when the query is run. It returns as a SQL formatted date string wrapped in single quotes ready to be used in a SQL WHERE clause eg '2020-01-01T00:00:00'.
Example:
SELECT WorkItemId, Title, AffectedUser
FROM WorkItem WI
WHERE Created > @Today-1
This query will return all work items created since yesterday morning.
Note: The API only supports using a single offset (including zero offset) per query. Eg the @Today variable can be used multiple times in a single query but the offset must be the same each time.
WHERE Created > @Today-1
AND LastModified > @Today-1
Is supported
WHERE Created > @Today
AND LastModified > @Today-1
Is not supported
Â