Adding Employee Names
For certain custom reports, you may wish to include employee names in the report. The following report query, which shows assigned staff and their role for clients over 50, demonstrates how to use the tables tblEmployee and rxyUser to add employee names to your report. We’ll build and preview the report, and then we’ll take a closer look at the query.
1. Go to the central list of custom reports (Menu at Reports, Create/Edit Custom Reports) and select the Create icon located in the upper right corner of the list to create a new query.
2. Enter Staff Role for Clients Over 50 in the Query Name field.
3. Enter the following query:
SELECT tblClient.colClientID AS [Client ID], tblClient.colFirstName AS [First Name], tblClient.colLastName AS [Last Name], CAST (DATEDIFF(day, colDateOfBirth, GETDATE())/365.24 AS INT) AS [Age], rxyUser.colFirstName AS [Staff First Name], rxyUser.colLastName AS [Staff Last Name], colStaffRoleType AS [Staff Role]
FROM
tblClient
INNER JOIN tblStaffRole ON tblClient.colClientID = tblStaffRole.colClientID
INNER JOIN tblEmployee ON tblStaffRole.colEmployeeID = tblEmployee.colEmployeeID
INNER JOIN rxyUser ON tblEmployee.colUserID = rxyUser.colUserID
INNER JOIN tblStaffRoleType ON tblStaffRole.colStaffRoleTypeID = tblStaffRoleType.colStaffRoleTypeID
WHERE CAST(DATEDIFF(day, colDateOfBirth, GETDATE())/365.24 AS int) >= 50
AND colCurrentRole = ‘True’
4. Select the Preview button to preview the results:
5. Close the report preview to return to the query-building page. In the SELECT statement, you can see that rxyUser.colFirstName and rxyUser.colLastName are used to extract employees’ first and last names. In the FROM statement, you can see that rxyUser is not joined directly onto tblStaffRole. Instead, it is joined onto tblEmployee via colUserID.
Filtering Report Output by Employee
To add employee selection as a report parameter (so users can select a specific employee when they run the report), revise the ‘WHERE’ statement in your query as follows:
WHERE CAST(DATEDIFF(day, colDateOfBirth, GETDATE())/365.24 AS int) >= 50
AND colCurrentRole = ‘True’ AND (rxyUser.colUserID = @UserID OR @UserID = -1)
Important: once you’ve added the employee selection parameter, you need to select “Employee Reports” under “Publishing Options” when building your report in order to preview the results. You also need to make a selection other than ‘All Users’ in the ‘User’ field when previewing the results, or else you’ll get an error message.