How do I display approver names in reports?

Question

How do I show an approver's name in a report?

Answer

To show an employee's approver in a report, the report has to meet a few initial conditions:

• The report must be a custom report.
• The report must be a custom report that includes the employee as a reporting column.
• Examples include timesheet details, employee expense details, employee summary reports, or any summary report that includes the employee name as a report column.

To add a reporting column that displays the employee's approver:

1. Navigate to Reports.
2. Click on the name of the custom report you want to modify.
3. Click on Edit.
4. Click on the Plus (+) button to create a new report column.
5. Scroll down the new report column values pick list to the very bottom and select "sql expression."
6. Sql expressions are sql statements that can reference system data and produce a calculated value, or can reference system data and produce a value for display in a report column. In this case, we are going to reference a value for display as a text value.
7. When you add the sql expression report column for the first time, a sql expression dialog will pop up for editing. Later, you can edit this sql expression by clicking on the sql expression report column field in the reporting setup page. 
8. In the sql expression dialog, enter the following sql statement: SELECT uu.fullname FROM t_user AS uu WHERE uu.id=t_timeentry.apprv1id
9. Set data type to 'Text'.
10. If your system is using a SQL Server database, you will need to include t_timeentry.apprv1id in the "group by" expression for a summary report by appending:  ,t_timeentry.apprv1id
11. To do this, scroll down to the bottom on the report configuration page when in edit mode.
12. Click on the "Options" link.
13. Edit the SQL Grouped By expression. 
14. Click OK to save
15. Save and run the report. 
16. In this example, the part of the statement with t_user.approv1id will display the level 1 approver name. If you want to show other approver levels, just modify the statement to reference that level number, e.g. t_user.approv2id, t_user.approv3id, etc.
17. Edit the sql expression report column name, modifying it to approval level name, e.g. manager approver, supervisor approver, etc.  
18. Click Okay to save the report.
19. If you want, you can repeat the process and create additional report columns that display other approval level names.