How do I display an employee policy name in reports?

Question

How do I display an employee policy name in reports?

Answer

Background

When creating custom timesheet summary or details reports, you might need to display an employee's policy name. To access the employee profile policy assignment and display it in reports requires a SQL expression.   

Example Report

In this example, create an Employee Summary Report:

1. Navigate to the Reports tab
2. Scroll down the Timesheet Reports templates box.
3. To create a custom Employee Hours Summary report, click on the Plus (+) button on report template row called: "Employee Hours - Employee hours summary"
4. This action will create a new custom version of this report and open up the report edit page and leave you in edit mode.
5. To add a report column that displays the employee policy name, you will need to create a SQL expression field.
6. Next to the Report Columns box, click plus to add a new field.
7. A picklist box will display
8. Scroll down to the bottom of the list to <<Add SQL Expression>> and click on that list item
9. A SQL Expression dialog will open
10. Copy and paste the following sql into this dialog:
    

    SELECT pp.name FROM t_policy AS pp WHERE pp.id=t_user.policyid

11. Make sure the Data Type is set to "Text"
12. Click OK to save
13. One important last step: when in report edit mode, scroll down to the bottom of the report edit page and click on Options
14. In the Option dialog, in the edit box "SQL "Group By" Expression:" add a comma (,) to the right of the text of existing grouped by expressions.
15. In the case of this report, the SQL "Group By" Expression: text should look like this:
    t_user.fullname,t_user.policyid
16. click OK to save and close the SQL "Group By" Expression: dialog
17. Then click OK to save the report.
18. Click on the report name to run the report, and you should see the policy name value display with the employee hours summary data:
    
    Here is a sample of a simple report page for one employee:
    
    These These SQL expressions and Group By expressions can be applied to any hours or expense summary or details reports.